Database Normalization question for address/contact information
Posted on 2006-05-31
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
type (home or work or cell)
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.