<%dim crumb crumb="Third Normal Form (3nf)" crumb1="3RD Normal Form" crumb2="Third Normal Form Table Design" %> Third Normal Form
Home  Fees/Services  Access Examples  Tutorials  Access Downloads  Articles  Search  Forums  Blockchain Tech  Contact  Privacy  Links
<% response.write crumb1 %>
Home > Access Tutorials > Access Table Design Tutorial > <%response.write crumb%>
 






Link Database Tables
0th Normal Form (0nf)
First Normal Form
Second Normal Form
3rd Normal Form
Table Design Summary
Table Design Tutorial
Access Join Tables

Table Level Events

Article:
Repair Corrupted Table


3RD Normal Form Table Design

Normalization Definition Examples and Recommendations

3rd 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 is consider in third normal form (3NF). And, additionally the table must first be 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 3NF 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:

3rd Normal Form Table Design

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 3rd Normal Form tables look like:

Access Table Normalization

Access 3NF

The two tables are now in 3rd normal form.




























Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint


Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

Microsoft Access 2007, Access 2010, MS Access 2013