kramaswamy (2/2/2013)
Stores may not have been the best example to use 😛 How bout, let's say, information about a client - the client's home address, name, etc...The only reason I'm considering splitting the table into smaller pieces is the question of whether having a table with a large amount of columns would cause any degree of performance degradation.
Contact information is even worse than store information. A client can have more than one home address (summer home, winter home, work week home, weekend home, vacation home, multiple shipping addresses, etc), can certainly have more than one phone number, can actually have more than one name in the form of "aliases" (AKA), and much more (how many email addresses can one person have, for example). Most contacts I have have at least 3 contact telephone numbers (home, work, cell) and at least 2 email addresses (semi-private, work).
I wouldn't count on some of the things you've identified as being only 1:1. I also try to avoid NULLs especially for fixed length datatypes.
So, like everything else, "It Depends". If you've only ended up with 1:1 relationships in your normalization attempts, one of two things may have happened... either the data you used never did go beyond 1:1 or you didn't normalize as correctly as you may have thought.
I can give you an example guaranteed to produce a 1:Many relationship... InvoiceHeader and InvoiceDetail. Another would be AccountInfo and AccountTransaction. For contacts, Contact and ContactTelephone.
The other thing you have to look for is 1:0 information. If a column or set of columns is frequently NULL, that's a pretty good indiction that it should be in a separate table.
--Jeff Moden
Change is inevitable... Change for the better is not.