Nexus Learn

Database Normalization Explained: 1NF, 2NF and 3NF

Normalization is the process of organising a database so that each fact is stored in only one place. It avoids repeated data and the mistakes that come with it.

Why normalize?

Imagine one big table that stores the student, the course and the teacher's phone number on every row. If a teacher changes their phone number, you must fix it on many rows. Miss one and the data disagrees with itself. These problems are called update, insert and delete anomalies.

First Normal Form (1NF): one value per cell

A table is in 1NF when every cell holds a single value and there are no repeating groups. A column that holds "Python, SQL" in one cell breaks 1NF.

Fix: give each course its own row, so Ali gets one row for Python and one for SQL.

Second Normal Form (2NF): no partial dependency

A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key, not just part of it. This matters when the key has two columns, such as (student_id, course_id).

If course_title depends only on course_id, it does not belong in a table keyed by both. Move it to a separate courses table.

Third Normal Form (3NF): no transitive dependency

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. Suppose a courses table stores teacher_id and teacher_phone. The phone depends on the teacher, and the teacher depends on the course. Move teacher_phone to a teachers table.

The three forms at a glance

FormRuleRemoves
1NFOne value per cell, no repeating groupsRepeating groups
2NF1NF and no partial dependency on the keyPartial dependency
3NF2NF and no non-key depends on a non-keyTransitive dependency

A memory trick

Every non-key column should depend on "the key, the whole key, and nothing but the key." The key is 1NF, the whole key is 2NF, and nothing but the key is 3NF.

Is more normalization always better?

Not always. A highly normalized database needs more joins, which can be slower for reporting. Teams sometimes denormalize on purpose to make reads faster, but only after understanding what they are giving up.

Frequently asked questions

What is BCNF?

Boyce-Codd Normal Form is a slightly stricter version of 3NF. In BCNF, every determinant must be a candidate key.

What is a partial dependency?

It is when a column depends on only part of a composite primary key instead of the whole key.

What is a transitive dependency?

It is when A determines B and B determines C, so C depends on A only through B.

Keep learning