| Author |
Topic |
|
spazman
Starting Member
3 Posts |
 Posted - 2005-04-22 : 03:49:55
|
| I'm currently designing a SQL Database with about 25 tables.There are several tables which have normalized sub tables that hold Address information. The parent tables do not have enough in common to be combined into one table but the Address tables do. I have been considering combining all the address tables into one master address table and adding a column which would specify the parent table index. The parent table index values would be pulled from a static table. I've gone round and round with doing this and I am undecided. Any advice on the matter would be helpful. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-22 : 05:16:38
|
| Why do you want to combine them? Normalisation is your friend! Mark |
 |
|
|
spazman
Starting Member
3 Posts |
Posted - 2005-04-22 : 05:58:46
|
| I'm still undecided, combining may not make much sense but neither does having 5 nearly identical tables. It would be easier to search all addresses from one table without having to do a bunch of costly joins as well.On the down side, there is the potential for record locking, longing querying times because of table size and an additional column to index and maintain integrity.I see several good and bad side effects of doing this.What would be a better practice to follow? This database is for a Windows application used for data entry and reporting.One day you will confuse yourself and your head will pop. My only wish is to be there to witness your cranial explosion. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2005-04-22 : 06:00:04
|
Without knowing the table design it is hard to give reliable advice but from first impression, your idea for combining the address tables seems like a good idea. I think it is very wise to keep all your addresses in a generic address table and that combined with your parent table index should allow you to distinguish between the different types of addresses and which other tables they should link to.I would say go ahead with your plan. Sounds reasonable to me. My 2 cents.  ------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destruction |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-22 : 06:36:56
|
| I think I misunderstood your issue. I was assuming you had a normalised design for storing addresses. (i.e. separate tables for different address elements such as country, state, etc.)If your tables represent different classifications of the same entity, then I entirely agree with Amethystium that they belong in one table. I've done a fair bit of data modelling over the years and, thus far, my cranium has remained intact, but thanks for the thought!Mark |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-22 : 06:50:48
|
| Additionally...It sounds like you might benefit from taking a slightly object-oriented approach. You could potentially extract the commonality from your other tables into a single table (including a reference to your new single address table). Any specificity would then be moved to new tables with a one-to-one relationship to the master table. Each 'subtyped' entity contains only those columns that distinguish it from the others whilst it 'inherits' common attributes from its 'supertype'.I've used this approach extensively and have found it extremely useful.See [url]http://www.univdata.com/[/url] for more info.Mark |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2005-04-22 : 07:14:33
|
quote:
Originally posted by mwjdavidson
Additionally...It sounds like you might benefit from taking a slightly object-oriented approach. You could potentially extract the commonality from your other tables into a single table (including a reference to your new single address table). Any specificity would then be moved to new tables with a one-to-one relationship to the master table. Each 'subtyped' entity contains only those columns that distinguish it from the others whilst it 'inherits' common attributes from its 'supertype'.I've used this approach extensively and have found it extremely useful.See [url]http://www.univdata.com/[/url] for more info.Mark
Are you suggesting the creation of a table which is reserved for text or string type of data? This would be ideal for holding things like locations, job types etc but addresses should be a seprate entity I reckon.------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destruction |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-22 : 07:56:33
|
| Nope. I'm suggesting extracting commonality from a bunch of similar entities that are involved in similar relationships into a single supertype. A very simple example of this is abstracting out the concept of a 'party' from the more specific entities of 'people' and 'organisations'. There are many relationships that both people and organisations can participate in that are identitical. They can own property, be contacted by means of phone numbers, addresses, etc., be bound by a legal contract, etc. etc.Obviously, there are attributes that are specific to each subtype - an organisation does not have a middle name or a gender (well, maybe in France!). This is where the one-to-one mapping between subtype and supertype comes in. This allows you to maintain specificity whilst inheriting commonality (basic object-oriented programming concepts). Have a look at the link - it's interesting stuff!Mark |
|
|
|
spazman
Starting Member
3 Posts |
Posted - 2005-04-23 : 01:56:02
|
| Thanks for the input.--------------------One day you will confuse yourself and your head will pop. My only wish is to be there to witness your cranial explosion. |
|
|
|
|
DB Design. A question about normalization.