Normalisation: designing tables that don't contradict themselves

Normalisation is designing tables so that each fact is stored in exactly one place. The usual target is third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.

Why it matters

If every order row stored the customer's email, changing Priya's email means updating five rows — miss one and the database says two different things. That is an update anomaly. Normalisation designs it out, and it is a favourite interview question.

The idea

First normal form (1NF): one value per cell, no lists like 'Pen, Mug', and no repeating columns like item1, item2.
Second (2NF): in a table with a composite key, every column must depend on the whole key. The product name depends only on product_id, so it belongs in products, not in order_items.
Third (3NF): no column depends on another non-key column — a customer's city name shouldn't carry the city's country too; that belongs in a cities table.

The practice database is already normalised: customers, orders, order_items and products each hold one kind of fact. Denormalisation is copying data back on purpose, for read speed, accepting the risk.

A school keeping each pupil's address in one office file, instead of writing it on every exercise book. When a family moves, one change updates everything — and no book can show an old address.

In a school, looking up the office file is quick. In a database, every split costs a join at read time. That is why analytics warehouses often denormalise on purpose: reads there matter more than updates.

How it works

  1. Start with one wide "flat" table: order, customer name, email, product, price, quantity.
  2. Priya's email appears on every line she bought — 9 times. Changing it means 9 updates.
  3. 1NF: one product per row, not 'Headphones, Backpack' in one cell.
  4. 2NF: product_name and price depend only on product_id, so they move to products; customer details move to customers.
  5. 3NF: nothing depends on a non-key column. What is left is the practice database: each fact once, joined back when needed.

Key points

Common mistakes

Not true: The further a design is normalised, the better it always is.
In fact: Past third normal form, simple questions can need many joins. 3NF is usually the right place to stop.

In the interview

Normalisation organises tables so each fact is stored once, which prevents update, insert and delete anomalies. First normal form means atomic values with no repeating groups. Second: no partial dependency on part of a composite key. Third: no transitive dependency through a non-key column. In short, every column depends on the key, the whole key and nothing but the key. I normalise transactional systems to about 3NF and denormalise deliberately for analytics or read-heavy paths.

Part of Unit 6: Building tables and changing data in the free SQL course.