Another Point of View
One of the Swynk author's (David Faour) volunteered to comment on the process I had undertaken.
I sent David a copy of the
first two pages of this article and the database layouts. Below are some of his comments.
One can read some of Davids'
other articles here. I have taken Davids' comments
from an email he sent to me. With only a few grammatical corrections here are his comments.
David's Comments
It looks pretty good in general. However, I do have some comments on
tblAddresses, tblContact, and tblEmail.
In tblAddresses, I am not sure what you are trying to capture in Address1,
Address2, Address3. Are you doing it to split into three input boxes on a
form? Also street address can be further decomposed into street number,
street name,
street type(ave,blvd,st,rd), and Apt/Suite number. This depends on what your
lowest atomic level is.
City, state, zip, and country should technically be lookups(foreign key) to
a tblCity, tblState, tblZip, and tblCountry.
Suppose you have free text typed into the DB, and somebody accidentally
uses FK for the state Florida instead of FL, just a simply typo because k &
l are together on the keyboard. when you do a delete from where state=FL,
the FK row is still there. In addition, a select from where state=FL will miss the
row; the same thing applies to cities, countries, and zip
Also, i'm not sure that firmID and lookup ID are appropriate in this
table... it seems that address should be a table with its own addressID and
there might be a cross reference table of firmAddresses (firmID,addressID)
because a company might have many locations...
In the tblContact, the title should also not be a free text, and should be a
lookup to tblTitle(id, description)
this is because someone who's title is "Dr." may have their title mistakenly
input as "Fr." and you would have the same situation as above with states.
Lastly, with tblEmail if you wish, you could decompose email address into 3
parts name, server name, servertype(.com/.net/.org/.gov/etc).
Server name would be AOL, MSN, etc and name of course would be yourname@.
Now, if you go for this setup, one might say that the servertype should also
be a lookup to the valid servertypes and that a servertype table should be
created.
Keep in mind that these comments are directed towards a very strict, formal
interpretation of 3NF, and of course you will make decisions to
denormalize based upon business requirements
» 
IE 7
Firefox 2.0
Comment and Contribute
