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

Second Normal Form (2NF)

A table in second normal form (2NF) follows all the rules of first normal form, and one additional rule:

  • Columns that belong to no candidate key must depend on every whole candidate key, not just part of one.

Example of 1NF but Not 2NF

In this table, the primary key is a combination of first_name + last_name.

first_namelast_namefirst_initial
LaneWagnerl
LaneSmalll
AllanWagnera

This table does not adhere to 2NF. The first_initial column is entirely dependent on the first_name column, rendering it redundant.

Example of 2NF

One way to convert the table above to 2NF is to move first_initial to a new table that maps each first_name directly to its first_initial. This removes any duplicates by storing each name and initial once.

first_namelast_name
LaneWagner
LaneSmall
AllanWagner
first_namefirst_initial
Lanel
Allana

2NF Is Usually a Good Idea

You should probably default to keeping your tables in second normal form. That said, there are good reasons to deviate from it, particularly for performance reasons. The reason being that when you have to query a second table to get additional data, it can take a bit longer.

My rule of thumb is: optimize for data integrity and data de-duplication first. If you have speed issues, de-normalize accordingly.

Assignment

Another developer on our team has created a joining table for the companies ↔ users many-to-many relationship. Unfortunately, they did it a bit... weird. They included meta-information about companies on the joining table!

A good joining table simply has the IDs of the entities in the relationship. It manages the relationship and nothing else. Any information about the entities themselves belongs in their respective tables.

Move the column that's out of place to its proper table. Be sure to add it as the last column in that table.