| Author |
Topic |
|
koto
Starting Member
6 Posts |
 Posted - 2011-03-06 : 16:52:24
|
| Hello,I've following tables setup:Address (ID, Street, Town...)Person (ID, AddressID (FK to Address table) FirstName, LastName...)Customer (ID, PersonID (FK to Person table), Vip ...Customer's address data would be then obtained by view joining these tables.Is this a good approach from the 'performance/convinience of use' point of view ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
koto
Starting Member
6 Posts |
Posted - 2011-03-07 : 04:10:58
|
| Hello TaraThank you for the answer. The system is quite large and there are other tables using address:Company (ID, AddressID, Name...)Driver (ID, AddressID, LicenceType...)So having address data in the separate table has some advantages.Would you add address fields also to these tables?I have a strong background in OO programming and there you always try to avoid fields repetition. I know that Db is a different thing, so I'm wondering how db experts would approach this. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-07 : 04:45:18
|
quote:
I know that Db is a different thing, so I'm wondering how db experts would approach this.
Well...Tara is an expert and she has stated her opinion. But I'd say it depends on what kind of logic you are thinking of here. If for example you register a company with an address and you also register all its employees with the same address then it makes sense to store it in a separate table to avoid duplication. But if you want to register two neighbors that have just a different house number it will be far better to store the address twice as columns in the person-table.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-03-07 : 05:31:37
|
| u need to have seperate table for address because one person may have multiple address. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-08 : 15:15:15
|
quote:
u need to have seperate table for address because one person may have multiple address.
One person might have multiple address, but model assumes that it will store just one. Separate address table makes sense in case there are multiple persons with the same address.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
koto
Starting Member
6 Posts |
Posted - 2011-03-08 : 16:57:18
|
| HiTo sum it up, the best solution from performance point of viewwould be to resign from address table and have its fields repeated in Person and Company tables? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-08 : 17:33:53
|
| That depends on how much space the address data would take for each row in each table, and how many queries you'd perform that don't require that address data. If you always have to retrieve address data, then it's probably better to keep it in the same table. But if you only need it 20-30% of the time, and the address takes up more than 30% of the space in a row, you'd likely get better performance by splitting them into separate tables. Reducing the amount of data in-row increases the number of rows you can store in a page, reducing the number of pages needed in memory, and in turn reduces the amount of disk I/O.If you really want to find the best design you'll need to test different scenarios. If you have to settle on a specific object model, use views or stored procedures to return data to your object layer in a consistent format. You can then test different table structures and interface with their appropriate view/stored procedure.There are somewhat larger concerns that you should also consider:1. Can a Person, Customer, etc. ever change their address? Does their address history need to be preserved? (if true, none of the suggested designs will accomplish this)2. If a Person, Customer, etc. (multiple object types) have the same address, is there a solid business case to keep the address separate for each? Example: if 5000 Persons work at a Customer, and they all have the same address as the Customer, is it required to save the same address data 5001 times? Or just 5000 times? (This is a tremendous waste of space, and will definitely hurt performance)3. As mentioned earlier, can Persons, Customers, etc. have multiple addresses? Not just address history, but different addresses like billing, shipping, home vs. business, and so on?If your current requirements haven't considered these scenarios, you may have significant problems in the future if you have to add to or redesign your database to accommodate them. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-09 : 03:04:23
|
| Nice explanation Rob! It would be nice to have like option for every post. :)MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-09 : 08:23:12
|
Thanks! Unfortunately I have a full time job and they don't pay me to answer questions on SQLTeam.  Maybe I could get fired again... |
|
|
|
|
Is this set of tables a good approach?