FoxPop
 

www.foxpop.co.uk

 


POWERBASE TUTORIAL 11d

by Laurie Jane Kern

Normalizing our Contact Database - Third Normal Form


We left off last time, looking at the second rule of Normalization. This lesson will discuss the Third Normal Form, and with luck it should be as short as the last one.

Third Normal Form (also known as Rule Number Three):
The third rule states that any non-key field in a table must relate to the Primary Key of the table and not to another field, that is not a Primary Key.

OK, I will translate this for you. Remember that the primary key is the field or fields that identifies the record. The key can be a single field (like ID) or a composite key (FirstName & LastName). Thus by stating that the other fields in the record must relate to the primary key and no other, this forces all data in the table in question, to relate exclusively to the entity that the table describes. If a table was to contain information about an line item being ordered, call this an OrderDetail Table. Most likely the primary key would be a composite composed of the line item number and a "foreign key" that relates this line item back to the order header - Order Number.
Now the OrderDetail table could have the following fields: Product ID ( a foreign key back to the products table), Quantity, Order Date - these all relate to the line item. Yes, date does too. What if you call up after you place the order and want to add something to the order? But would you have the following fields: Customer Telephone Number or Product Price ? No! The customer telephone number is related to the customer ID, and that is a foreign key on the Order Header table NOT the Order detail table. The same goes for the price field, price is an attribute of the product ID and is held in the product table. The field you are missing is the Lineitem total which would be calculated from the Quantity and Price (which is found by looking up the price in the product table using the product id key!).

So was that so hard? OK, now that you understand that, lets look at the three cases. Check carefully, but remember you learned in previous tutorials that each of your cases does not have a primary key. Despite that, assume you do and in each case it is an ID field (an auto number field will generate a unique ID each time you add a record). Using that assumption now look at how the data in each table relates to the purpose of the table.

Case 1: In this case you have Personal and Work information. Now if the primary key is an ID field and only one person can have that ID, ALL the Company Information relates to the person, not the Primary Key. You know that the company data could be used for more than one contact so they violate the third rule of normalization.

Case 2: If you use this table for either a Contact or a Company there are fields in the table that are not used because they are specific to the other type of data. If you fill in company name the fields: Title, First Name, Last Name are attributes about a person and then these fields are not relevant to the primary key and so in this situation the third rule is violated. If you enter information about a person, you have not violated this rule. So using a table for two different types of information is breaking the third rule.

Case 3: Each table has a primary key. Each field relates to the key in each table. Cool! You have passed the third normal form test.

That's it for the basic rules of normalization. Yes, there are more but they are more complex and obscure. Besides, I'll bet you're now itching to make some tables? Don't worry, you'll do that in the next lesson!

 

Case 1:
Personal and Business fields ALL in one table
Case 2:
A combined Contact/Business Table
Case 3:
Separate People and Business Tables.
Title
First Name
Last Name
Home Address Line 1
Home Address Line 2
Home City
Home Region
Home Postal Code
Home Country
Home Telephone
Mobile
Home Fax
Home Email1
Home Email2
Home URL
Contact Type
Company Name
Company-Address Line 1
Company-Address Line 2
Company-City
Company-Region
Company-Postal Code
Company-Country
Company-Telephone - Main
Company-Telephone - direct
Mobile
Company-Fax
Company-Email1
Company-URL
Company Type
Notes
 
Title
First Name
Last Name
Company Name
Address Line 1
Address Line 2
City
Region
Postal Code
Country
Telephone
Mobile
Fax
Email
URL
Contact Type
Notes
 
Business Table   People Table
     
Company Name   Title
Address Line 1   First Name
Address Line 2   Last Name
City   Address Line 1
Region   Address Line 2
Postal Code   City
Country   Region
Telephone -Main   Postal Code
Telephone - Direct   Country
FAX   Telephone - home
Email   Mobile
URL   Fax
Business Type   Email1
Notes   Email2
    URL
    Contact Type
    Notes
     
     

 

© LJKern and FoxPop 1999

[Previous] [Index]  [Next]