Normalisation: designing tables that don't contradict themselves

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  F["one flat table<br/>order, customer name, email,<br/>product, price, quantity"] -->|"normalise"| C["customers<br/>id, name, email"]
  F --> O["orders<br/>id, customer_id, date"]
  F --> OI["order_items<br/>order_id, product_id, quantity"]
  F --> P["products<br/>id, name, price"]

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.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked example

SELECT o.order_id, c.name, c.email, p.product_name, p.price, oi.quantity
FROM order_items oi
JOIN orders o    ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p  ON p.product_id = oi.product_id
WHERE o.customer_id = 1
ORDER BY o.order_id, p.product_name;

Must-know points

• 1NF: one value per cell; no lists, no repeating column groups.
• 2NF: 1NF, and no column depends on just part of a composite key.
• 3NF: 2NF, and no column depends on another non-key column.
• Normalised data avoids update, insert and delete anomalies; the cost is more joins.
• Denormalise on purpose (reporting tables, caches) when reads matter more than keeping one copy.

Common mistakes

• Storing comma-separated lists in a column.
• Copying a value (like a price) into another table without deciding whether it should change when the source changes.
• Normalising so far that simple questions need eight joins. 3NF is usually the right stop.

In other databases

• Real order systems often store unit_price in order_items on purpose: the price paid at the time must not change when the product's price changes later. That isn't a 2NF violation — it is a different fact.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  Database normalisation: 1NF, 2NF, 3NF (video)

  YouTube search, free

• Watch

  CS50's Introduction to Databases with SQL — Lecture 2: Designing

  Harvard, free

• Read

  W3Schools — SQL FOREIGN KEY (linking normalised tables)

  short page with a try-it editor, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain 1NF, 2NF and 3NF in one sentence each, using the shop tables.

   Show answer

   • 1NF, one value per cell: an order can't store 'Headphones, Backpack' in one column; each product gets its own row in order_items.
   • 2NF, every column depends on the whole key: in order_items the key is (order_id, product_id), and quantity depends on both, but a product_name would depend on product_id alone, so names live in products.
   • 3NF, no column depends on another non-key column: orders shouldn't hold the customer's email, because the email depends on customer_id, not on the order; it lives once, in customers.

2. Writefrom a blank page, then run it

   Write the query that rebuilds the flat view for Tom (customer 2), and count how many times his email appears.

   Show answer

   The same four-table join as the lesson, for customer 2. Tom has two orders with one item each, so his email appears 2 times. (His second sign-up, customer 6, repeats the same email in customers, but has no orders.)

   sqlCopy

   SELECT o.order_id, c.name, c.email, p.product_name, p.price, oi.quantity
   FROM order_items oi
   JOIN orders o    ON o.order_id = oi.order_id
   JOIN customers c ON c.customer_id = o.customer_id
   JOIN products p  ON p.product_id = oi.product_id
   WHERE o.customer_id = 2
   ORDER BY o.order_id, p.product_name;

   Try it

   2 rows

   order_id	name	email	product_name	price	quantity
   104	Tom	tom@mail.com	Headphones	40	5
   108	Tom	tom@mail.com	Desk lamp	25	3

3. Predictsay it before you run it

   If products also stored the category's manager name, which normal form would that break, and why?

   Show answer

   Third normal form. The manager's name depends on the category, not on the product: product_id → category → manager. That is a transitive dependency, through a column that isn't the key.

   The symptom: the Stationery manager's name would be stored on the pen row and the notebook row. Change the manager and miss one row, and the table says two different things. The fix is a categories table (category, manager_name), with products keeping only the category.

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Should order_items store the price at the time of the order? Argue both sides.

   Show answer

   For storing it:
   • The price paid is a different fact from today's price. When the headphones go from £40 to £45, last month's order must still say £40, for invoices, refunds and revenue reports.
   • Without it, every old order would silently change value whenever a price changes.
   • So it is not a 2NF violation: unit_price in order_items depends on the whole key (this product, in this order), not on product_id alone.

   Against:
   • If prices truly never change, it is a redundant copy taking space.
   • Two columns called "price" invite mistakes: which one does a report use?
   • An alternative is a price-history table with "valid from" dates, joined on the order date. It is fully normalised, but every read becomes more complex.

   In practice, almost every real order system stores the price at the time of the order.

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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 — 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.

Follow-up questions

Tick each check when you can do it without notes