September 23, 2011 at 12:38 pm
I am in the process of redesigning a DB2 database with a person table, a 'distinct' addresses table and a PersonAdressRelation table that identifies the addresses associated with a person and the type of address - Home, Shipping, Billing, etc.
So, to redesign this i proposed to merge the PersonAddressRelation and Address table to form just a PersonAddress table. 1-many from Person to PersonAdress.
I proposed this solution to make it simpler 'not' to keep an address unique, which is the problem even when the DB2 has a hard time keeping them unique.
Does anyone have any opinions on which way to go? Any ideas of implementations out there dealing with large data sets.
Thanks for your input in advance.
September 23, 2011 at 1:01 pm
Yes. Yes. Yes.
This is a case where normalization is inappropriate. An address should belong to a particular individual, so you can change that person's atomic data appropriately without affecting other entries. The only way this normalization would make sense is if you had a table with *every* address anywhere, and selected from the dropdown list from heck. Otherwise, each address should belong to one and only one particular user, and not be a lookup.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 23, 2011 at 1:02 pm
I personally like the approach a Person table and an Address table. The Address table would contain the foreign key to Person and an AddressType field to let you know which type of address it is.
I am not really sure what you mean that the address has to be unique though. There is no reason to keep them unique. Suppose a given person wants to ship something to an address that is also a shipping address for somebody else. That shipping address would then be in the table twice. One for each "owner" of that shipping address.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 1:17 pm
Sean Lange (9/23/2011)
I personally like the approach a Person table and an Address table. The Address table would contain the foreign key to Person and an AddressType field to let you know which type of address it is.I am not really sure what you mean that the address has to be unique though. There is no reason to keep them unique. Suppose a given person wants to ship something to an address that is also a shipping address for somebody else. That shipping address would then be in the table twice. One for each "owner" of that shipping address.
The address being unique 'was' a requirement placed by the business. This was due to the address standardization playing a role (i.e. the usps address standardization). So in order to maintain that 'standardized'\holy address a mapping table was created.
Thats the history behind it. 🙂
I too like the idea of not normalizing it to such an extent that we see a mapping table between an address and a person. But we have 10+ types of addresses and a person. The end users are not able to digest the fact that they will see redundant data in the address table but will have an address type identification. ....
September 23, 2011 at 1:21 pm
vishal.gamji (9/23/2011)
I am in the process of redesigning a DB2 database with a person table, a 'distinct' addresses table and a PersonAdressRelation table that identifies the addresses associated with a person and the type of address - Home, Shipping, Billing, etc.So, to redesign this i proposed to merge the PersonAddressRelation and Address table to form just a PersonAddress table. 1-many from Person to PersonAdress.
I proposed this solution to make it simpler 'not' to keep an address unique, which is the problem even when the DB2 has a hard time keeping them unique.
Does anyone have any opinions on which way to go? Any ideas of implementations out there dealing with large data sets.
Thanks for your input in advance.
No, it's not a case where normalisation is inappropriate. It's a case where someone has produced a data model that doesn't match the real world, but that's not because normalisation has been done. Separating addresses out into an address table would only be normalisation if there were business rules than made having a separate address table a better data model than having a person-addresstype-address table. I have never seen any such case, although I've had the misfortune to see many designs that did have a separate address table. Such designs are usually the result of someone thinking about an entity-relationship model which has entities which utterly fails to capture the non-transient properties of the data (as do most attempts at entity-relationship modelling when used instead of relational modelling).
Tom
September 23, 2011 at 1:23 pm
Well then if they are (foolishly imho) dead set on keeping it over normalized then you have to keep the PersonAddressTypeHowManyJoinsToKeepOutOneOrTwoRedundantPiecesOfDataDoesItTake type of structure. Do they do the same thing for phone numbers? Normalization can be taken so far it is crippling and this sounds to me to be overnormalized. You may be stuck living with the current structure due to users inability to wrap their head around something pretty simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 1:25 pm
In light of Tom's response I think he is correct this is not necessarily over normalized but I think we all agree this is not a good data model.
--edit typos because my brain was faster than my fingers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 1:25 pm
So, with my proposal of just a Person (PersonId) - Address (PK: AddressId, FK: PersonId), does anyone see any cons with this approach ? Other than the visual redundancy.
September 23, 2011 at 1:27 pm
Sean Lange (9/23/2011)
Well then if they are (foolishly imho) dead set on keeping it over normalized then you have to keep the PersonAddressTypeHowManyJoinsToKeepOutOneOrTwoRedundantPiecesOfDataDoesItTake type of structure. Do they do the same thing for phone numbers? Normalization can be taken so far it is crippling and this sounds to me to be overnormalized. You may be stuck living with the current structure due to users inability to wrap their head around something pretty simple.
Agree. I gave the same example of phone numbers to counter. Thanks.
September 23, 2011 at 1:27 pm
vishal.gamji (9/23/2011)
So, with my proposal of just a Person (PersonId) - Address (PK: AddressId, FK: PersonId), does anyone see any cons with this approach ? Other than the visual redundancy.
Nope. That is exactly how I would model it. It does of course break the business rule you mentioned that the address must be unique (which is actually a good thing imho).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 2:04 pm
Thanks everyone for the help.
September 23, 2011 at 4:54 pm
vishal.gamji (9/23/2011)
So, with my proposal of just a Person (PersonId) - Address (PK: AddressId, FK: PersonId), does anyone see any cons with this approach ? Other than the visual redundancy.
No cons that I can see.
The argument that claims that normalisation is relevant is that if "this address" changes (perhaps the post office introduces a new zip code) then you want to represent "this address". The argument against it is that if Joe moves house I want to represent "Joe's address". The argument against wins every time because Joe (or someone else) moves house more often that the post office invents new zip codes, which means that the business rules have to taken account of addresses attached to people rather than addresses attached to real estate (actually, in a realtor's office some addresses are addresses of chunks of real estate, and those addresses need their separate table, but most people aren't in that business). Since the sole function of normalisation is to ensure that the business rules can be catered for with minimum difficulty the system which recoirds people's addresses rather than real estate addresses is the normalised one. I know that there are people who claim the function of normalisation is to ensure that there are no information preserving decompositions of tables, but that really is is the tail wagging the dog.
Tom
September 23, 2011 at 6:44 pm
I’m gonna go out on a limb here and disagree with all y’all. I work with address data extensively. I ceased to believe years ago that “we’ll only need one address or phone number, ever” could ever be a true statement. Things change, businesses grow. People have multiple addresses and addresses can belong to multiple people, or entities. Sorry Evil K – addresses can belong to many users/persons. I personally have lived with up to 10 people at a single address. (not fun btw)
If Joe moves, you’re not going to want to just update his address. You’ll want to add a new address and indicate the date when the old address became invalid and when the new one becomes valid. If you mailed something to Joe two years ago, but only had his current address, you’d never be able to tell him where his stuff went two years ago.
A reasonable amount of normalization is a wonderful thing in this regard, besides people are not the only things that have addresses, companies have addresses, real property as mentioned in the prior post, etc.
I work in the real estate/default services industry, maybe that will explain my strong feeling in this regard. Addresses are the basis of everything. EveREEthing. After years of working with and reporting on and [mostly] working around address data, following is a rough example of the structure I most prefer.
It’s goin home time on a Friday, so forgive the shorthand.
Address (pk AddressID)
Person (pk PersonID)
Phone (pk PhoneID) (and yeah – I’d normalize it to several columns for each component of the phone number, ie: area code in the US, for maintainability)
Email (pk EmailID)
PersonAddress (composite key PersonID, AddressID n:n, fk AddressType) (the address type is here because a mailing address for one person might be a home address for another, n’est-ce-pas?)
PersonPhone (composite key PersonID, PhoneID n:n, fk PhoneType) (see above)
PersonEmail (comp key PersonID, EmailID n:n)
I threw in Phone and Email just to make someone sigh heavily. I’d save the active and deactive dates for each item as well.
Repeat the same for Company, or other entities. If real property is involved:
tblProperty ( pk PropertyID, fk AddressID) (the Property table has further attributes that define real property)
If you need addresses to be unique, just run it through the same usps standardization first, then validate against existing data. If the address exists, use the existing AddressID instead of inserting a new row into Address.
Now I freely admit that this is probably the most annoying structure for the UI developers, but hey, the data is nice and consistent.
September 24, 2011 at 6:06 am
Burninator (9/23/2011)
Sorry Evil K – addresses can belong to many users/persons.
What gave you the impression that Craig thought addresses belonged to only one person? I couldn't see anything in his post that suggested that.
Of course if you want to hold validity periods it's best to make addresses separate entities which are not affected by changes to their relationships with people and model those relationships together with the validity data in a piece of your schema with is normalised all the way to 6NF. Do most applications need to do that? Certainly credit reference applications need to, but which others?
In the real estate industry you maybe have two kinds of address: addresses of real estate, and addresses of people. It sounds as if you treat them both the same. Is there a good reason for doing that? They have very different characteristics, and probably different business rules apply to the two sorts of address data so that different normalisations would apply (after all, normalisation is about representation of business rules, nothing else) and it might be a good idea to treat them differently.
Tom
September 26, 2011 at 8:38 am
L' Eomot Inversé (9/24/2011)
Burninator (9/23/2011)
Sorry Evil K – addresses can belong to many users/persons.What gave you the impression that Craig thought addresses belonged to only one person? I couldn't see anything in his post that suggested that.
Tom, I read it that way too
Evil Kraig F (9/23/2011)
Yes. Yes. Yes.Otherwise, each address should belong to one and only one particular user, and not be a lookup.
I'm sure Craig will jump in and clarify
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply