| Author |
Topic |
|
renutek
Starting Member
5 Posts |
 Posted - 2004-07-29 : 16:58:55
|
| Hi,Here is my scenario..I have a transaction that captures a lot of addresses say shipping,billing,from address,etc. so in the database design we have a table for each address type with a foreign key of transaction id.Transaction tbl --> Shipping_add Trans_id desc trans_id add1 city1 blah 1 aa cc --> billing_add trans_id add1 city 1 aa cc --> from_add trans_id add1 city 1 aa cc and so on..instead of having different tables for different addressess,is it a good idea to have one address table with address type as a column..what are the pros and cons for this approach..Could somebody please explain in detail about this database design concepts..Transaction tbl --> address_tbl Trans_id desc trans_id address_type add1 city1 blah 1 shipping aa cc 1 billing aa1 cc1 1 from aa2 cc2Thanks in advance for your help.. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-29 : 17:42:04
|
| Typically, your basic database design concepts are to put like items in the same place. I would argue it is much better to put all addresses in one table with a type code column. It means fewer tables to worry about, you can retrieve all addresses with a single JOIN statement, and perhaps most important, if you come up with a new "type" of address, you simply add it to the type code list and include it. This is a data change instead of a schema structure change which would occur if every type were in its own table.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
 |
|
|
renutek
Starting Member
5 Posts |
Posted - 2004-07-29 : 18:02:58
|
| Thanks AjarnMark..It's like you read my mind..the main reason I wanted to have this way is because of future addition to address type..But I have a question about retrieving with single join..If I join with single join my result set will have 'n' records for 'n' address type for that transaction..Trans_id add_type add1 city1 shipping a c1 billing a1 c1 Instead if I want just one record for the transaction along with all the address type then either I need to create a view for each address type and join those views to transaction table to merge all those data in to one record or join the same address table 2 (or n) times with aliases to merge those...Is there any easy way to retrieve all those addresses along with the transaction data in a single row...Thanks again. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-30 : 15:42:29
|
| To put multiple addresses into a single row with this schema then you are correct you either have to do multiple joins or you can do a series of CASE statements. Both of these are hindered by the need to modify them if there is a new type added.Why is it important to have them all in one row? How often will you need EVERY address listed for multiple transactions? It seems to me that you more likely will want either:1) A "primary" address listed for each of multiple transactions, OR2) All addresses for a single transactionand both of these are easily accomplished with the schema we're discussing.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
owen912
Starting Member
31 Posts |
Posted - 2004-07-31 : 09:17:54
|
| I have a thought on this topic with a slightly different angle.In the past, I have always written one Address table with a column designating different address types. The Address table is then related to a Person table (Person --> Address --> AddressTypes).This scenario has worked for databases for retail functions (I hate the word e-commerce). I am now working on a project that will have me tracking addresses not necessarily associated with a single person. In other words, tracking addresses for a school campus.Rather than create separate tables for tracking campus addresses and person addresses, and setting relationships back to a parent table, I am thinking about a column that stores addressIDs in a comma delimited list. A person with multiple address associations (Billing, Shipping, Etc) will read something like “123,456,789.”This way I could then relate addresses to other entities such as buildings and organizations.Has anyone tried this approach, and what are the pros and cons? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-03 : 09:50:34
|
| CON....having to deal with "building and subsequentially parsing such lists" |
|
|
|
renutek
Starting Member
5 Posts |
Posted - 2004-08-03 : 11:19:18
|
quote:
Originally posted by AjarnMark
To put multiple addresses into a single row with this schema then you are correct you either have to do multiple joins or you can do a series of CASE statements. Both of these are hindered by the need to modify them if there is a new type added.Why is it important to have them all in one row? How often will you need EVERY address listed for multiple transactions? It seems to me that you more likely will want either:1) A "primary" address listed for each of multiple transactions, OR2) All addresses for a single transactionand both of these are easily accomplished with the schema we're discussing.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering
Hi AjarnMark,Thanks again..I am in favour of this approach..our database design should not be based around the data retrieval...but the problem is,I am joining this new project in the middle and the group is already using this design with lots of redundant tables like this..with just common sense one can tell that we should reduce the number of tables and number of table joins etc.. Could you please provide me some good links to database design topics so that I could provide some justification with facts for mistakes in that database..Thanks. |
|
|
|
JasonGoff
Posting Yak Master
158 Posts |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-08-03 : 13:42:23
|
| To play devils advocate, I believe the following should be weighed when considering the path you chose:1.) Your joins will be faster if you have separate tables for each type of address. This becomes an issue when your "common" address table contains large amounts of data (millions of rows) and your database needs to use large keys (GUID). If you use indexed views to address this, you will be storing *a lot* more information.2.) In my experience, each new address type represents a unique function that is associated with it, so when you invariably wish to use it in a query you either need a view (representing schema changes for each additional type) or an enum or lookup table with the Address Type combined with hardcoding the key to that into your queries.3.) In your design, it looks like you had a description for one type of address but not some others. This would mean that combining the them all into one table will result in wasted space and larger row sizes. Furthermore, just having an address type represents additional storage overhead.Cons:1.) Your design will have more tables.2.) Listing all addresses as rows is a more expensive query.3.) Common address table is closer to a normalized design.Ultimately, it depends on your environment and what you will be using them for. If you commonly need to display them in one row, you have a small number of "address types" that are unlikely to change, you may have a very large number of addresses and/or each address type requires different business logic, I may recommend the separate table approach. If you need to frequently access all addresses for a given entity as rows and/or you expect the "address types" to expand without the need for separate logic, the "common address" table approach may fit better.Just my 0.02. |
|
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-08-04 : 04:55:22
|
| 1) Why would you use GUIDs when you get to a million rows ? Maximum value for an INT in SQL is 2,147,483,648. I've got tables with 40million+ rows with INTs as Surrogate keys to support joins (not my design I hasten to add, but it works!) |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-08-04 : 11:52:14
|
| I did not say that was my database design, I was simply illustrating a point. I also indicated millions... with an s. Regardless, as with anything in a database, there are always tradeoffs. A GUID is extremely useful under a number of circumstances, but that has to be balanced with its large size. My point is that you cannot assume that an INT (, GUID or Natural Key) will work under all circumstances.There are some wonderful articles on this site and others discussing these specifics of this topic, so I implore you to seek them out if you have further questions. |
|
|
|
|
Database design question