Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Contact  Privacy  Links
Table Design Examples
Third Normal Form
Access Table Design Guidelines
Microsoft Access Table Design

Access Program Tutorials> Table Design Access > Third Normal Form (3nf)


 

Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
Third Normal Form
Table Design Summary
Table Design Tips

Repair Corrupted Table


Third Normal Form Table Design

Normalization Definition Examples and Recommendations

Third Normal Form Definition (simplified)

A table whose non-primary key fields are dependent only on the primary key and therefore have no dependence (relationship to) any other non-primary key field in the table.  And, the table is in 2nd normal form.


Example 1

Here is the classic employee table example:

Emp_SSN
Emp_Name
Street
City
State
Zip

Here we have a transitive dependency between Zip field and City and State fields - what ever that means.  I guess it means they are a little dependent but no big deal.  Since a zip will tell you what state and what city an address is in.  Therefore, to make this table third normal form we need to split out city state and zip fields into a table of their own and then just have the zip in the employee table:

Emp_SSN
Emp_Name
Street
Zip

The zip tables is as follows:

Zip
City
State

The two tables are now in third normal form.  Some people may argue that there is some transitive relationship between City and State - but we've already gone further in normalizing the Employee table than is customary in an Access database.

This problem might be more obvious if we add mailman to the list:

Emp_SSN
Emp_Name
Street
Zip
Mail_Man

Here you can see that we need a table of Zips defining each Mail_Man.  Here is the resulting tables assuming a mail man only works in one zip

Zip
Mail_Man

Emp_SSN
Emp_Name
Street
Zip

Example 2

Here's a more straightforward example:

Third Normal Form

Above we have a Students table with SSN, Student's Advisor and the Advisor's Phone number.
Clearly, the advisor's phone is dependent upon the advisor and the either candidate primary key for this table (Student_Name and Student_SSN).  To change this table to third normal form we need to break out the Student Advisor and Advisor Phone fields into a separate table.  There resulting third normal form tables look like:

Third Normal Form Tables

Third Normal Form

The two tables are now in third normal form.

Contact Information

Table Design Guidelines
Microsoft Access 2003 2000 2002(XP) All Windows Versions