A relational database splits its data into linked tables. But which columns go in which table? Get that wrong and the same problems that ruin a flat file creep back in — the same fact stored in a dozen places, quietly drifting out of step. Normalisation is the disciplined process that decides, step by step, exactly how to lay the tables out so that every fact lives in exactly one place.

The goal is to remove redundancy (repeated data) and, with it, the anomalies that redundancy causes when you try to update, insert or delete rows. We do this in named stages — First (1NF), Second (2NF) and Third Normal Form (3NF) — each one tightening the rules a little further. This page works one messy table all the way through, and by the end you will be able to normalise a table yourself and say why each split was made.

The problem: one bloated table

Imagine a small shop keeps every order on a single paper-style table. Each order can contain several products, so the item details are crammed into one cell — a repeating group:

It looks compact, but it is a trap. Customer Ade and city Leeds are copied onto every order Ade makes; “Mug £4” is repeated wherever a mug is bought; and the Items cell holds a whole list, which a database cannot search or total properly. This repetition is data redundancy, and it breeds three specific faults.

Why redundancy hurts: the three anomalies

When facts are duplicated, ordinary operations start to misbehave. These are the anomalies that normalisation exists to prevent:

• Update anomaly. If Ade moves to Manchester, her city is written on many rows. Change some and miss others and the database now claims Ade lives in two cities at once — it disagrees with itself.
• Insertion anomaly. You want to add a new product to the catalogue that nobody has ordered yet. In this table there is nowhere to put it — a product only exists as part of an order, so you are blocked from recording a fact you clearly know.
• Deletion anomaly. If order 1003 is the only order containing the Pad (P5) and you delete that order, the very existence of the Pad — its name and price — vanishes with it. You lost a fact you never meant to throw away.

All three have the same root cause: a fact is tangled up with other, unrelated facts. Normalisation untangles them so each fact stands on its own, stored once. Let's do it stage by stage.

Stage 1 — First Normal Form (1NF)

A table is in 1NF when:

• every value is atomic — a single value per cell, not a list;
• there are no repeating groups — no “Item 1, Item 2, Item 3…” columns and no lists crammed into one cell;
• there is a primary key that uniquely identifies each row.

We fix our table by giving each item its own row. Now “P3 Mug £4 ×2” becomes a proper row. Because one order spans several rows, no single column is unique any more — but the combination of OrderID and ProductID is. That pair is a composite primary key (underlined below).

The lists are gone and every cell is atomic — this is valid 1NF. But look how much is now repeated down the rows: the whole order and customer block is copied for every item on the order, and each product's name and price reappears wherever it is bought. 1NF is only the beginning.

Stage 2 — Second Normal Form (2NF)

2NF attacks redundancy that comes from a composite key. A table is in 2NF when it is in 1NF and has no partial dependencies — that is, no non-key column depends on only part of the key. Every non-key column must depend on the whole key.

Our key is (OrderID, ProductID). Ask of each other column: what does it really depend on?

• OrderDate, CustID, CustName, CustCity depend on OrderID alone — the same for every item on the order. (Partial.)
• ProdName, Price depend on ProductID alone — the same wherever that product appears. (Partial.)
• Only Qty depends on the whole key — how many of that product on that order.

We remove each partial dependency into its own table, keyed by the part it depends on. One table becomes three:

Each product's name and price is now stored once (green). The OrderLines table keeps only the fact that genuinely needs both parts of the key — the quantity — and uses OrderID and ProductID as foreign keys back to the other two tables. Notice that this also cures the insertion and deletion anomalies: a brand-new product can now go straight into Products even if no one has bought it, and deleting an order can never erase a product.

Stage 3 — Third Normal Form (3NF)

One table is still hiding redundancy. Look at Orders: Ade and Leeds are still repeated on orders 1001 and 1002. Why? Because those columns don't really depend on the order at all — they depend on the customer.

A table is in 3NF when it is in 2NF and has no transitive dependencies: no non-key column depends on another non-key column rather than directly on the key. Here OrderID → CustID → CustName: the customer's name depends on the key only indirectly, by way of CustID. That indirect chain is a transitive dependency.

We break it by lifting the customer details out into their own table, leaving CustID behind as a foreign key:

Ade and Leeds are now written once. Together with the earlier split, the whole design has reached 3NF as four clean tables — Customers, Orders, Products and OrderLines — each about one kind of thing, linked by keys. Step through the finished schema:

The whole journey at a glance

A neat sanity check: in a fully 3NF table, every non-key attribute is a fact about the primary key, and nothing else. If a column is really a fact about something other than the key of its table, it is in the wrong table.