Pointer Walk

Normalization

Store each fact once. A table that repeats a customer's address on every order will sooner or later hold two different addresses.

DatabasesBeginner9 min read

The table that seemed convenient

A small shop keeps its orders in one table. Every row has everything you could want to know: the order, the item, the customer's name and the customer's address. One table, no joins, easy to read.

Then real life happens to it: a customer moves, an order is cancelled, somebody signs up.

One wide tableInteractive

orders

ordercustomeraddressitem
1Asha12 Lake RoadKeyboard
2Ben5 Hill StreetMouse
3Asha12 Lake RoadMonitor
4Asha12 Lake RoadCable

Start. One table holds everything about an order, including who placed it and where they live. Asha has three orders, so her address is written three times.

Places Asha's address is stored
3
Different addresses on file for Asha
1

Three ordinary events, three problems. They have names.

ProblemWhat happened
Update anomalyA fact stored in several places was changed in only some of them. The table now disagrees with itself.
Delete anomalyRemoving one thing (an order) removed something unrelated (the only record of a customer).
Insert anomalyA thing (a customer) could not be stored without inventing something else (an order).

All three have the same cause: the table is about two different things at once, orders and customers.

The rule

Store each fact once.

Asha's address is a fact about Asha. It is not a fact about order 1, order 3 or order 4. So it belongs in a table about customers, in one row. Orders refer to her by an id.

Two tables, each fact stored onceInteractive

customers

idnameaddress
1Asha12 Lake Road
2Ben5 Hill Street

orders

ordercustomer_iditem
11Keyboard
22Mouse
31Monitor
41Cable

Start. Two tables. Each customer is stored once. An order does not repeat the customer's details. It holds only the customer's id, which points at their row.

Places Asha's address is stored
1
Different addresses on file for Asha
1

Reorganising tables this way is called normalization.

sql
CREATE TABLE customers (
  id      INT PRIMARY KEY,
  name    TEXT NOT NULL,
  address TEXT NOT NULL
);

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  item        TEXT NOT NULL
);

Two words from that:

  • A primary key is the column that identifies a row. No two rows share one.
  • A foreign key is a column holding another table's primary key. REFERENCES customers(id) makes the database enforce it: an order cannot point at a customer who does not exist.

When you want the wide view back, ask for it with a join:

sql
SELECT orders.id, customers.name, customers.address, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id;

The wide table still exists. It is now something you compute when needed, not something you store and keep in step by hand.

Try both designs

One wide tableInteractive
Design

orders

ordercustomeraddressitem
1Asha12 Lake RoadKeyboard
2Ben5 Hill StreetMouse
3Asha12 Lake RoadMonitor
4Asha12 Lake RoadCable

Start. One table holds everything about an order, including who placed it and where they live. Asha has three orders, so her address is written three times.

Places Asha's address is stored
3
Different addresses on file for Asha
1

The normal forms

Textbooks describe normalization as a ladder of "normal forms". The first three are the ones people mean in practice. A plain way to read them:

First normal form: one value per cell

No lists inside a cell, and no numbered columns such as item1, item2, item3.

Example

A list in a cell

An order row with items = "Keyboard, Mouse, Cable" cannot be searched, counted or joined properly. "Which orders include a mouse?" becomes text matching.

Fix: an order_items table with one row per item per order.

Second normal form: every column depends on the whole key

This matters when a table's key is made of two columns. In order_items(order_id, product_id, quantity, product_name), the quantity depends on both the order and the product. The product name depends on the product alone, so it is repeated for every order that includes that product.

Fix: move product_name to a products table.

Third normal form: no column depends on another non-key column

In customers(id, name, postcode, city), the city is decided by the postcode, not by the customer. Change one customer's city and others with the same postcode now disagree.

Fix: a postcodes table, if that level of care is worth it for your data.

There is an old line that sums up all three: every column should describe the key, the whole key, and nothing but the key.

The price, and when to break the rule

Normalized data is safe to change. The cost is paid when reading: showing an order with its customer and products means joining several tables.

Usually that is fine. Joins on indexed keys are fast (see Indexes and B-Trees). But sometimes a copy is the right choice. Storing a copy on purpose is called denormalization.

Reason to keep a copyExample
The value must not change afterwardsThe price and delivery address at the time of the order. If Asha moves next year, last year's invoice must still show where the parcel went.
Reads are heavy and the data rarely changesA comment_count column on a post, to avoid counting rows on every page view
ReportingWide, flat tables built for analysis, refilled from the normalized ones

The first row is worth a second look. An order's delivery address is a fact about that order, so storing it on the order is not a violation at all. Deciding what a fact is really about is the whole skill.

Key takeaways

  • A table that mixes two kinds of thing repeats facts, and repeated facts drift apart.
  • The three symptoms are update, delete and insert anomalies.
  • Normalization stores each fact once, in a table about the thing it describes, and links tables by id.
  • A join rebuilds the wide view whenever it is needed.
  • Copying data on purpose is sometimes right, for history or for speed, but every copy must be kept in step.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    7 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress