Normal Forms in Four Moves

Normalization means store each fact once. When a fact lives in many rows, one change becomes many writes and the copies drift apart. There are four steps and each step fixes one problem.

1NF: one value per cell

Two phones live in one cell. You cannot search or join on half a cell. Give each phone its own row:

2NF: the column needs the whole key

Only matters when the key has two parts. A grade sheet keyed by (student, course):

grade belongs to the pair. Rahim's Math grade and English grade are different. But student_name belongs to the student alone. The course does not change their name, so the name gets written again on every course row. Fix a spelling in the name and you must fix many rows. Move names to students(student, name) and keep only the id here.

After the split, students holds the name once:

And the grade sheet keeps only the id:

A name change is now one row. Every join sees the new name at once.

3NF: no tag-along columns

Every column has to depend on the key, not on another normal column.

City does not belong to the person. It belongs to the zip. Zip 1207 is always Dhaka, so Dhaka gets written again and again. Type it wrong once and the same zip now shows two cities. Fix: keep only zip in this table and save the city one time in zips(zip, city).

BCNF: every decider is a key

BCNF stands for Boyce-Codd Normal Form, a slightly stricter 3NF. Anything that decides another column must itself be a key.

A teaching table keyed by (student, subject):

Subject does not belong to the pair. It belongs to the teacher. Mr. Karim always teaches Math, so Math gets written again for every student of his. The problem: teacher decides subject but teacher is not a key here. Move Mr. Karim to a new subject and you must fix every student row.

After the split, teachers holds the subject once:

And lessons keeps who studies with whom:

The subject now lives in one row. Change it there and every student row stays untouched.

Where I stop

In production I design to 3NF by default. BCNF earns its keep only when a non-key column decides things and causes real update bugs. And I denormalize on purpose for read-heavy paths, with the normalized tables staying the source of truth.

Quick recall: