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.
Here is the classic employee table example:
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:
The zip tables is as follows:
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:
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
Here's a more straightforward example:
Above we have a Students table with SSN, Student's Advisor and the Advisor's
The two tables are now in 3rd normal form.
Copyright 2000-2017 Blue Claw Database Design
Microsoft Access 2007, Access 2010, MS Access 2013