Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-11 : 07:17:53
|
| Stan writes "I have been reading the forums about creating denormalizing, primary keys, surrogate keys etc and it has got me really confused. I have just started work for a company converting a Visual FoxPro database to SQL Server, but i am completely new to database design, although i know the principles.With the existing table structure, I have tried to normalize, and split out fields where we don't have the data, to avoid having too many nulls, and having one large table with many columns, e.gTable Contact_NameContact_Key int identity allTitleForenameOthernamesSurnameTable: Contact_Address (a person may have more than one address)Contact_Address_ID identity (just for uniqueness)Contact_Key - links to Contact_NameAddress1Address2Address3Address4Address5Table:Contact_PersonalContact_Key (links to Contact_Name)DOBGenderMarital_StatusUk_Tax_Payertable:Contact_Phone (a person may have more than one phone number or none)Contact_Phone_ID identity (just for uniqueness)Contact_KeyPhone_NumTypeThere are a lot more tables than this. This will result in multiple joins on Contact_Key when trying to retrieve the data. Is this a bad thing? Or should i denormalize the data, and just live with the fact that many columns will have null values? How many columns is too many to have in a table. Plus should I not be using an identity key as a primary key?I am sorry for asking an obvious question, and not phrasing it very well." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-11 : 08:02:32
|
| One thing I would is:Create a table of Address_Types, with values like:BusinessHomeBilling..etc..And then for your address table, instead of an identity as your primary key, I would have a composite key of Contact_Key and Address_Type. I would do the same with phone numbers as well; i.e., a table of PhoneNumberTypes:HomeWorkCellFaxTry to use as many natural keys as possible. I personally feel that for people or companies, coming up with a numeric key of some sort (be it an identity or someone just typing one in while doing data entry) is a good idea, but some people prefer to use natural keys for that as well, such as FirstNameLastName or CompanyName.But you appear to be on the right track.Good luck!Side note: with the table of address types, you can put other fields in there to make those types more meaningful, such as "InvoiceWeight" where each type has a weight, and the highest weighted address for a contact has is the one you send the invoices to, or fields of that nature.- Jeff |
 |
|
|
|
|
|
|
|
Question on Denormalizing and Multiple Table Joins