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.
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.
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:
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.
Source: Authored by Vincent Tran