Normalization (1NF – BCNF): Cleaning Up Your Database Design

When I first heard "normalization", I imagined something way more complicated than it actually is. At its core, normalization is just about organizing your database tables in a way that avoids redundancy and keeps data clean.

Let's go through each normal form step by step, using the same example throughout so it actually sticks.

The Problem We're Solving

Imagine a single table storing everything about student course enrollments:

This table has multiple values in one cell, repeated data, and a mess of dependencies. Normalization fixes all of this, step by step.

1NF — First Normal Form

"One value per cell. No repeating groups."

A table is in 1NF if:

• Every column has atomic (single) values — no lists or arrays in a cell.
• Each row is unique.

Before 1NF (problem — multiple courses in one cell):

After 1NF (fix — one row per course):

Now each cell has exactly one value. Clean.

2NF — Second Normal Form

"No partial dependencies. Every non-key column must depend on the WHOLE primary key."

This only matters when you have a composite primary key (a primary key made of multiple columns). If a column depends on only part of the key — that's a partial dependency and needs to go.

In our table, the primary key is (StudentID, Course). But StudentName only depends on StudentID, not on the course. That's a partial dependency — it violates 2NF.

Fix — split into two tables:

Students Table:

Enrollments Table:

Now every column in each table depends on the full primary key.

3NF — Third Normal Form

"No transitive dependencies. Non-key columns should depend only on the primary key, not on each other."

Let's say we add teacher info to the enrollments:

Here, TeacherPhone depends on TeacherID, not directly on (StudentID, Course). That's a transitive dependency — TeacherPhone → TeacherID → the key.

Fix — move teacher data to its own table:

Courses Table:

Teachers Table:

Now non-key columns in each table depend directly and only on the primary key.

BCNF — Boyce-Codd Normal Form

"A stricter version of 3NF. Every determinant must be a candidate key."

BCNF handles edge cases that 3NF misses — usually when there are multiple overlapping candidate keys.

Example:

Suppose students can have multiple advisors, and each advisor teaches only one subject, but a subject can be taught by multiple advisors:

Here the candidate keys could be (Student, Subject) or (Student, Advisor). But Advisor → Subject is a dependency where Advisor is not a candidate key on its own. This violates BCNF.

Fix — decompose:

Now every determinant is a candidate key. BCNF achieved.

Quick Summary

Each step builds on the previous one. You can't skip to 3NF without being in 2NF first. And in practice, getting to 3NF is usually good enough — BCNF is for when you want to be really strict about it.