Third Normal Form

1. Requirements for Third Normal Form

With the third normal form, this is generally the final stage of most normalization processes. It requires that the database design first fully fulfills the requirements of the second normal form but also has a criterion to ensure that there is no transitive functional dependency. This means that every attribute that is not the primary key in a table must depend on the primary key and only the primary key. Say that we have column A determines column B and column B determines column C. We would have transitive functional dependency as column A would determine column C and column C should be removed and placed in a separate table. We need to check this for all of our tables with each column.

2. Example

For our database design that was in the Second Normal Form tutorial:

In our case, none of the non-primary key fields depend on something else other than the primary key. So, in essence, after 2NF, our current database design is actually in 3NF. This outcome is quite common.

3. Applying Third Normal Form

However, let us extend the User table a bit further to include some additional details about the user such as the email, phone, address, city, state, and zip code. We will also make the assumption that all of the users will be in the country USA. By doing so, our User table would look like the following:

• User (UserId, UserFirstname, UserLastName, Email, Phone, Address, City, State, Zip)

One important piece to note by looking at this is that the City and State are determined by the Zip in the US which falls under the pattern that we discussed before as the Zip is dependent on the UserID but the City and State are dependent on the Zip. To truly convert this to 3NF, we would need to split up the tables like the following:

• User (UserId, UserFirstname, UserLastName, Email, Phone, Address, Zip)
• ZipCode (Zip, City, State)

The Zip in the User table would link to the Zip in the ZipCode table. This way, any time that the Zip code was entered in, the city and state would automatically be set. If we had to add an address in any other table later on, then we can use the same ZipCode table as a lookup table. The resulting ERD for 3NF would look like the following:

Note though that the zip code itself is not commonly split up on its own even with it being a 3NF rule as there are instances where a zip code in the US can reflect different cities. If we plan to have this setup for any country, this may not apply either in which case the following ERD would make the most sense:

Depending on the requirements, this will be our final normalized database design in third normal form.

---