[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1388
  • Last Modified:

Database Normalization question for address/contact information

Hi, I am trying to do an address contact database and I have some questions.  I am semi-familiar with normalization, have done some reading on it, took a class a couple years on it, however, I don't eat breathe and sleep it so have never gotten as familiar with it as I would like.  I would like to know if there are any links out there that describe normalizing contact/address info in *detail*.  I have searched and searched and it seems that I can't find any standard way of doing things, or an author will ask a question like "what if a contact has 3 phone numbers and 7 e-mail addresses" but then never proceeds to answer that question and all of a sudden brings up a whole new set of data that deals with classrooms.  I assume that it is something like you have a

contacts table
-------------
contact_id PK
fname
lname

phone table
------------
phone_id PK
phone_number
type (home or work or cell)
contact_id FK

but I can't find this anywhere

Also, I want to know how people deal with City State Zip Address - I think it's obvious that you would want a State Table, but how do you handle teh difference between Mobile Alabama and Mobile Kentucky

How do you handle things like years, like if you have a field under contacts like year_graduated and year_enrolled that take a 4-digit year, do people create a year table and include those as foreign keys?  Is that going overboard?  There seem to be varying theories on denormalizing tables and what is practical in a real world environment.

I realize that there is not one set way of doing things, but I figured with something as basic as a contact database that there would be loads of information that I could draw on to make some educated decisions.  Maybe you experts can point me in the right direction.
0
PurpleSlade
Asked:
PurpleSlade
5 Solutions
 
NickUpsonCommented:
your contact and phone tables are fine but don't go to far with foreign keys, the year table is not necessary
0
 
Atlanta_MikeCommented:
Assuming there won't be multiple address per contact... store the address in the contacts table. Separate City and state will suffice for your schema.

Its more about the relationship between the tables...

I think you're on the right track.

What other tables will you need?
0
 
PurpleSladeAuthor Commented:
OK, here is what i have so far:  

Contacts
--------------
contact_id  PK
first_name
last_name
middle_name
preferred_name (nickname)
suffix
jersey_name
year_enrolled
year_graduated
birthday

Country
------------
country_id  PK
country_name

States
-------------
state_abbr  PK
state_name

Zip
--------------
zip_code PK
city_name
state_abbr FK

Address
--------------
address_id  PK
address
contact_id FK
zip_code FK
country_id FK
address_type (home or work)       <--- can someone tell me how this is best done?  Should I make a seperate table?

Phone
---------------
phone_id  PK
phone_number
phone_type (home or work or cell)
contact_id  FK

email
----------------
email_id  PK
email_address
email_type (home or work)
contact_id FK

The last thing that I want to do is be able to have a mentor table which basically references the contact table, so say in contact table there is:

1 George Bush  
2 Bill Clinton
3 Al Gore
4 John McCain

Say Bill Clinton mentored Al Gore and John McCain I want something like

Mentors
-----------------
contact_id FK
contact_id FK

so 2,3
    2,4

So one contact can essentially mentor 0 or more contacts - how do I get the

How do I accomplish that?  And any comments on the overall structure welcome.  Overkill?
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
PurpleSladeAuthor Commented:
This is an example of an author mentioning something that I am trying to deal with and then when he moves on to teh next page, uses a completely different example.  I have found many instances of this

http://www.devshed.com/c/a/Administration/Database-Normalization/1/
0
 
Atlanta_MikeCommented:
Not bad...

Address Type and Phone Type fields can be done one of two ways. One have a lookup table with a unique key to be stored in the Address and Phone tables. Or you can create a separate table for PhoneType and AddressType.

I much prefer using a Lookup table (with a FK constraint in the parent table).



0
 
PurpleSladeAuthor Commented:
Sorry, but can you write it here what you mean?  Also, is my Mentor table on track?  Also, do you know of any links to databases that have been done such as this?  I seem to find 2 different trains of thought -- one says that the way I have done it is overcomplicated - that I should put most of what I have in the main contacts table.  The other is all for normalizing to the fullest extent of the law.
0
 
Atlanta_MikeCommented:
Yes, you're mentor table looks fine.

What I mean is create a table similar to:

ID      Type       Name
--       -----       ----------------
1        Null        Phone Type
2        Null        Address Type
3        1           Home
4        1           Mobile
5        1           Work
6        2           Home
7        2           Work


ID is the primary key and an identity field, Type = NULL defines the different types and is a foreign key to ID.

Your Phone table will have a foreign key relationship to this tables ID field.

0
 
normenclatureCommented:
It looks like you already have a decent grasp of data design.  There really is a fine line between good data design and denormalized slightly ON PURPOSE in favor of SIMPLIFIED REPORTING.  Reporting can be a NIGHTMARE if a database is "TOO NORMALIZED".  It may also depend on whether you have non-DB people creating reports or not.  If not, go ahead and normalize the heck out of it, but adding a few extra fields can save YOU time creating reports as well.

For my clients I tend to create a happy-medium:  I'll offer 3 phone fields (home, cell, work) and 3 email addresses, and 2 website fields all in the one contact table.  However, just in case, I have a phone table left joined to it (like you have) but also a Web table (tblWeb) which has two fields:  address, type.  The address is either an email address or a web address and the type is either email or web to distinguish the two.  I have code that sorts it for them, so when adding it, the user gets one prompt, and the software will put it in the table and show them under two different "tabs" the websites and emails (both essentially filters off the same table)  Is this making any sense?  I hope so.  This approach usually makes everyone happy.  The "quick and dirty users" that just want a program that WORKS, and the people that want to hold every website and email under the sun for a specific contact.

Anyways-- my 2c.

normenclature

0
 
PurpleSladeAuthor Commented:
OK, well I guess I have enough to go on.  I guess the lesson here is that I will just have to find out the hard way the consequences of normalizing or not and whether or not my database design will lead to pain in the ass sql coding later on.  I would have liked to have seen some links to a finished database similar to mine, but maybe there are no magical links that say, hey, do it this way PurpleSlade!  At any rate, thanks for your help everyone.  I am still not sure AtlantaMike that I get the purpose of your table.
0
 
Atlanta_MikeCommented:
That is a way of avoiding having a PhoneTypes table,an AddressTypes table, a Salutations table, a ShoeSize table.

To normalize the table to first normal form you'd have to have a separate table for each of your types, etc.

If your list of options is small then you can create one table to store all of these options in.

There are of course pluses and minuses to doing this. Read Joe Celko's "SQL Programming Style" book. He's opposed to using a Lookup table. He makes some good points, but in reality if your database has a limited number of tables and limited number of options for a specific column (as desscribed above) then it's an easier way to handle it.

If you want true normal form you'll have a separate table for each of the types, options, etc.
0
 
PurpleSladeAuthor Commented:
Atlanta_Mike, I'm sorry I'm still not getting it --

So I have a phone table:

ID    PHONENUMBER  FK_TO_TYPES table?
1     303-333-3333            1
2     303-555-5555            3

And then in your types table
1      NULL       Phone Type  ?
3         1         Home

and 1 or types is a foreign key to what?  What would the values be in that?  

I feel stupid, but can you elaborate a little?
0
 
Atlanta_MikeCommented:
yep... you do get it.

ID   CUSTID   PHONENUMBER  FK_TO_TYPES table?
1          123   303-333-3333            4
2          123    303-555-5555           3

ID    TypeID     Desc
1      NULL       Phone Type
3         1         Home
4         1         Work

The TypeID denotes what the value is a type for (i.e. Phone Type in this case).

0
 
PurpleSladeAuthor Commented:
OK, now I get it, thanks.  Interesting way of doing it.
0
 
Atlanta_MikeCommented:
It's good for eliminating a lot of extra tables with two and three rows in them. It has it's pluses and minuses. But mostly its a little more managable.

You wouldn't want to use a lookup table for ZipCodes or something that's going to have a lot of records. Not the purpose.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now