Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Design question regarding Normalized Tables Expand / Collapse
Author
Message
Posted Saturday, February 2, 2013 11:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Hey all,

Got a theoretical question for you guys. Let's say I have a table that contains information about a store. This information includes things like the telephone number of the store, the address of the store, the name of the store, etc ...

To me, the design of the table would be such that all of these fields would be contained in a single table, as these are all 1:1 relationships - IE, it wouldn't make sense to me to create one table called StoreTelephone, another table called StoreAddress, etc ...

Every time I've normalized table structures, I've only set up multiple tables in the case where I have 1:many or many:many relationships.

However, I'm working with a table right now which is approaching 30 columns - way more than I normally work with. All of these columns, though, are 1:1 relationships with the main identity. So to me, it wouldn't serve any benefit to branch some of these off into other tables.

Guess what I'm looking for is just some confirmation that what I'm designing isn't completely wrong for some reason or another. Any input is appreciated!
Post #1414957
Posted Saturday, February 2, 2013 12:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 42,839, Visits: 35,967
kramaswamy (2/2/2013)
IE, it wouldn't make sense to me to create one table called StoreTelephone, another table called StoreAddress, etc ...


Stores only ever have a single address and a single telephone number?

However, I'm working with a table right now which is approaching 30 columns - way more than I normally work with. All of these columns, though, are 1:1 relationships with the main identity. So to me, it wouldn't serve any benefit to branch some of these off into other tables.


If there's no benefit to moving some columns to another table, why are you considering moving them?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414959
Posted Saturday, February 2, 2013 12:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Stores may not have been the best example to use :P 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.
Post #1414960
Posted Sunday, February 3, 2013 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
kramaswamy (2/2/2013)
Stores may not have been the best example to use :P 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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415026
Posted Sunday, February 3, 2013 8:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Considering something like Contact Info I'd do something like below. I'm not including every possible attribute, just trying to give some ideas. I can't tell you how many wasted and expensive hours I've spent on parsing names and addresses just to mention two categories of contact info. Generally speaking I'm inclined toward more granularity when designing a database structure. Those who just dabble with writing simple queries seem to prefer the spreadsheet model you've described because they don't have to worry about doing complex joins, where clauses, grouping, etc. I've been told before to "just put it all into one table" and of course before the project goes live the same person who insisted no one--no one!--cares about fax numbers anymore makes a last-minute request to add fax numbers.

I'll say again: this is a very simplified model. What actually needs to go in the various tables is always based on the needs of the application.

Table:Contact
ContactID --(could by a natural key like CustomerNumber or SSN)
ContactType
Prefix -- the following 5 categories are a minimum when entering names
FirstName
MiddleName
LastName
Suffix
MaidenName --or any other UNIQUE traits like:
Sex
Birthdate

--What needs to be in an address table varies country to country.
--In the USA it's best to get the USPS address guide and enter
--addresses in the proper format to start with.

Table:Address -- allows any number of addresses per contact
AddressID
AddressType
AddressDesc --optional descriptive name such as "My Office" or "Spouse's Mom"
AddressSortOrder
AddressNumber
AddressPrefix --S, East, NW, etc
StreetName
AddressSuffix
City
State
PostalCode
Country

--Just like addresses
Table:Phone
PhoneID
PhoneType
PhoneDesc
PhoneIntlCode
PhonePrefix
PhoneAreaCode
PhoneNumber
PhoneExtension


--Best to present the end-user a list to choose from or you wind up with a million variations.
--All of these tables allow for adding a new 'type' at any time or changing the description of a 'type'

Table:AddressType
AddressTypeID
AddressType -- shipping, mailing, home, office, vacation house, etc
AddressTypeDesc --optional for allowing longer descriptions

--Could be Customer, Agent, Relative, etc
Table:ContactType
ContactTypeID
ContactType
ContactTypeDesc

Table:PhoneType
PhoneTypeID
PhoneType --home, cell, office, fax, etc
PhoneTypeDesc


--I like to use a cross-reference table because you can sometimes have more than 1 contact at the same address
--as well as multiple addresses per contact
Table:ContactAddress
ContactID
AddressID

--For the same reason as addresses
Table:ContactPhone
ContactID
PhoneID

--It's possible to have other attributes for a contact that are one-to-many;
--sometimes an EAV model is used in such a case but that opens up Pandora's Box
Table:ContactChildren
ContactID
ChildFirstName (Middle. Last, etc)
ChildRelationship (etc.)

Table:ContactAssociations
ContactID
AssociationName (etc)

Table:ContactHistory
ContactID
ContactDate
ContactDescription
ContactPurpose --which would relate to a ContactPurpose table
EmployeeID --which might relate to another set of tables for sales people, support, canvassers, etc
ContactTranscript (etc)

--These last 3 tables or tables like them could be done using an EAV model;
--this would allow limitless contact profile categories but can be a beast
--when trying to write efficient queries. Very flexible though if the category
--types and hierarchy cannot be easily predicted.

Table:ProfilePropertyDefinition
DefinitionID
DefinitionParentID --so a hierarchy could be built
DefinitionLevel --again for hierarchical use
DefinitionType --Children, Associations, History, etc.
DefinitionDesc (etc)

Table:ContactProfile
ContactID
DefinitionID
ProfileValue








Post #1415088
Posted Monday, February 4, 2013 12:18 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:03 AM
Points: 486, Visits: 1,221
Most of the databases I work with are for vendor supplied systems, but it is not uncommon to see some 1:1 relationships between tables, particularly if a single table might contain a large number of columns.

Seems to me that I have read an article from Kimberly Tripp at SQLSkills.com that discusses the performance impact of records > 8k that would cause excessive page splits and might be candidates for separation into 1:1 tables. I'm sorry, other than the general idea of the piece, I can't give you more details. Most of the inhouse DB's I've designed have been for relatively small systems and I've never had to break out columns into 1:1 tables.



Post #1415452
Posted Tuesday, February 5, 2013 1:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 178, Visits: 573
First step is to be certain about whther the relationship is 1:1 or not.

If you are certain the relationship is 1:1 and you are wondering about the benefits of splitting them into another table, the name for this is 'vertical partitioning'. If you do some research on vertical partitioning you may find some ideas to help you.

If you are not certain the relationship is 1:1.... then do some anlysis and business process modelling to work this out for certain.
Post #1415644
Posted Tuesday, February 5, 2013 5:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:41 PM
Points: 2,693, Visits: 1,203
Steven Willis (2/3/2013)
Considering something like Contact Info I'd do something like below...


Where's the +1 button. Ton of good info and examples Steve.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #1415758
Posted Tuesday, February 5, 2013 8:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 1,635, Visits: 5,590
kramaswamy (2/2/2013)
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.


Depending what you're putting into those columns you might possibly run into issues with the maximum 8000-odd byte length a SQL row can be (ignoring out-of-row data like VARCHAR(MAX) etc). There's also the simple issue of readability--when you come back to this table in six months and have forgotten what columns are in it, how easy is it going to be to scan through those 30+ columns to find the information you're looking for?

(I know what I speak of, on both counts--I'm often forced to delve around in a database that has a table containing in excess of 200 columns and with a maximum theoretical row length of more than 20k, which causes all sorts of fun!).
Post #1415861
Posted Friday, February 15, 2013 3:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
So in your model, if I got a new cell phone and number , you would need to create a new entry for me. Do you know for a fact that this possibility doesn't exist? I don't see the disadvantage of normalising the database in contrast to the risk of defeating the purpose of a rdms.
Post #1420791
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse