Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Mar 6, 2001

Normalizing an Existing Database - Page 3

By Bruce Szabo

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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date