We're sorry but this app doesn't work properly without JavaScript enabled. Please enable it to continue.

This lesson's interactive features are locked, please to keep using them

Third Normal Form (3NF)

A table in third normal form (3NF) follows all the rules of second normal form, plus the following rule:

  • All columns that aren't part of a candidate key are dependent solely on the candidate keys. They cannot depend on a non-candidate key column.

Notice that this is only slightly different from second normal form. In second normal form, we can't have a column completely dependent on only part of the candidate keys; in third normal form, we can't have a column that is entirely dependent on anything that isn't in the candidate keys.

Example of 2NF but Not 3NF

In this example, id and email are candidate keys, and we've chosen id as the primary key.

idnamefirst_initialemail
1Lanel[email protected]
2Breannab[email protected]
3Lanel[email protected]

This table is in second normal form because both candidate keys are single columns, so there's no partial-key dependency. However, it's not in third normal form because first_initial is dependent on the name column, which isn't a candidate key.

Example of 3NF

One way to convert the table above to 3NF is to move first_initial to a new table keyed by name. The name → first_initial dependency now lives in a table where name is the primary key. Notice how similar this solution is to 2NF.

namefirst_initial
Lanel
Breannab

We could also calculate the initial from name instead of storing it.

3NF Is Usually a Good Idea

The same exact rule of thumb applies to the second and third normal forms.

Optimize for data integrity and data de-duplication first by adhering to 3NF. If you have speed issues, de-normalize accordingly.

Assignment

This rollout of business accounts is really causing some headaches for our development team. The companies table has been a disaster. Our database architect pointed out that the idea behind the size field is redundant.

If a company has more than 100 employees, we consider it "large," otherwise it's "small." That's something we can calculate from the num_employees field, rather than storing it separately.

Tip

Remember the IIF function and the AS clause.