Database normalisation

We’ll start with the messes that bad table design creates, then climb the ladder — 1NF, 2NF, 3NF and BCNF — one rung at a time. Along the way you can play with live simulations: trigger the anomalies, split tables apart, and test yourself. No headset, no sign-in, nothing collected.

Start here

What goes wrong without normalisation?

When one table tries to store everything at once, the same fact gets written in many places. That redundancy quietly breeds three classic anomalies. Poke the table below to feel each one.

This one table stores students, departments and courses all at once. Tap a button to see how that bites back.

Update anomaly

A repeated fact is changed in some rows but not others, so the database ends up contradicting itself.

Insertion anomaly

You can’t record one fact without inventing another — a department needs a student before it can exist.

Deletion anomaly

Removing one row quietly destroys unrelated information that happened to live in the same place.

The core idea

Functional dependencies

Normalisation is really about one question: which columns determine which? We write X → Y to mean “if you know X, you know exactly one Y.” Getting these right is the whole game.

X is the determinant · Y is the dependent

Does the left side really determine the right side? Make your guess, then tap to check.

Interactive · the main event

The Normalisation Studio

Here is one messy table. Step it up the ladder and watch tables split apart, redundancy drain away and the anomalies disappear — the whole journey from un-normalised to BCNF in one place.

Un-normalised

StudentCourses PK: StudentID

Everything lives in one table, and a single cell can hold a list of courses. Impossible to query cleanly and riddled with repetition.

⚠️ Update, insert & delete anomalies still possible

First Normal Form · 1NF

Break apart the lists

1NF asks for one thing: every cell holds a single, atomic value — no comma-separated lists, no repeating groups. Flip the table below and watch a multi-valued column become tidy rows.

Three products crammed into one cell. Counting, filtering or joining on a single product is painful.

1NF · Real-world walkthrough

Fixing a music streaming playlist table

This is the kind of table a junior dev might design for a Spotify-style app. Walk through three steps to see exactly what 1NF demands — and what gets unlocked when every cell is atomic.

A music-streaming playlist table. The tracks column hides a comma-separated list — already violating 1NF.

Climbing higher

2NF and 3NF, in really plain words

1NF was about tidying up the cells. 2NF and 3NF are about one thing only: making sure each column is sitting in the right table. Let’s take them one at a time — slowly.

Does each column need the whole key, or just half of it?

2NF only matters when your table’s key is made of two columns stuck together (a “composite” key). Picture an order table where the key is {OrderID, ProductID}. Now look at ProductName. Does it care which order it was on? Nope — it only depends on ProductID. So you’d be repeating “iPhone 15” on every single order that includes it.

The fix: move ProductName into its own little Products table, where ProductID alone is the key.

In one line: if a column only needs part of the key, it’s sitting in the wrong table.

Does each column point straight at the key — or sneak in through another column?

Once 2NF is sorted, 3NF asks the next question. Take an employee table. Each Employee has a DeptID, and that DeptID tells you the DeptName and DeptPhone. So DeptName doesn’t really depend on the employee — it depends on the department, which depends on the employee. That extra hop is a “middleman”:

Employee → DeptID → DeptName

The fix: give departments their own table, and let the employee row just keep the DeptID.

In one line: every column should point straight at the key — no hopping through another column.

Nice to have — but you usually don’t need it.

Here’s the honest truth: most real databases stop at 3NF and are completely fine. BCNF is just a stricter, extra-tidy version of 3NF that handles a few rare edge cases. Think of it as a polish, not a box you have to tick. If your table is solidly in 3NF, you’re already in great shape — so feel free to treat this one as bonus reading.

Self-check

Is my table in this form?

A quick checklist for the three forms that matter. Tap a tab and run down the boxes — if they all hold true, your table has reached that form.

Tidy up the cells

One value per cell. Nothing crammed together.

• ✓ Every cell holds a single value — no lists like “Maths, Science” stuffed into one box.
• ✓ No repeating columns like Phone1, Phone2, Phone3 to hold “more of the same thing”.
• ✓ Each row can be told apart from the rest (there is a key).

Quick example

Split a cell that says “Maths, Science” into two separate rows — one per subject.

Tick all the boxes on a tab? Your table is in that form. Each form builds on the one before it.

2NF · Real-world walkthrough

Fixing an e-commerce order table

This is the table a new developer builds on day one. It looks sensible — until you trace the partial dependencies and see the update anomalies hiding inside.

3NF · Real-world walkthrough

Fixing an HR employee records table

Employee info that carries along department details creates transitive chains. Watch the chain animate, then see exactly which table gets extracted and why.

HR table for a NZ company. The Engineering team in Auckland appears three times — why is that a problem?

The fine print

A good split keeps two promises

Splitting a table isn’t free — a careless decomposition can invent fake rows or lose rules you cared about. Two properties tell you whether a split is safe.

Joining the pieces back together must reproduce the exact original table — no spurious, invented rows and nothing lost.

R = R₁ ⋈ R₂

Every functional dependency from the original can still be checked on the new tables, without re-joining them first.

F ≡ F₁ ∪ F₂

The trade-off: BCNF always gives you a lossless join but may sacrifice dependency preservation. 3NF guarantees both — which is why it’s often the practical target in real systems.

Interactive · put it together

Normal Form Detective

Read each schema and its dependencies, then call the highest normal form it satisfies. This is exactly the reasoning you’ll use on real designs.

The schema

Enrolment(StudentID, CourseID, StudentName, CourseName, Grade)

PK = {StudentID, CourseID}

StudentID → StudentName CourseID → CourseName {StudentID, CourseID} → Grade

What is the highest normal form this table satisfies?

At a glance

The normal forms, side by side

One card per rung of the ladder — the rule it enforces and how you fix a violation.

Every cell is atomic, no repeating groups, a primary key exists.

Fix: One value per cell; give multiple values their own rows.

In 1NF and no non-key attribute depends on only part of a composite key.

Fix: Split partial dependencies into their own table.

In 2NF and no non-key attribute depends on the key through another non-key attribute.

Fix: Extract the transitive chain (A → B → C) into a new table.

For every dependency X → Y, X is a superkey. Stricter than 3NF.

Fix: Decompose so every determinant is a key — may cost dependency preservation.

Unnormalised → 1NF → 2NF → 3NF → BCNF

Check yourself

Five quick questions

See how much of the lesson stuck.

A table in 1NF can still have plenty of redundant data.