Normalisation

01The problem

Kolanut's old invoice sheet had one row per product sold, with the customer's name and city and the product's category typed on every row. Peace Provisions' city appeared on every line of every invoice. When they moved, some rows were updated and others weren't. That's an update anomaly, and a flat sheet makes it almost inevitable.

02The concept

Normalisation reorganises tables so each fact lives in exactly one place. It prevents three problems:

It's done in steps called normal forms:

1. First normal form (1NF): one value per cell, no repeating groups (no product1, product2, product3 columns), and a key for every row.
2. Second normal form (2NF): 1NF, and every non-key column depends on the whole key. In a line table keyed by (invoice_id, product_id), invoice_date depends only on invoice_id, so it moves to an invoices table.
3. Third normal form (3NF): 2NF, and no non-key column depends on another non-key column. customer_city depends on customer, not on the invoice, so it moves to a customers table.

A memory aid: every non-key column should depend on the key, the whole key, and nothing but the key.

What stays on the line? unit_price stays on invoice_lines even though products have a list price. The price charged is a fact about that sale (discounts, the January 2026 price rise), not about the product. Deciding which facts belong to which entity is the judgement at the heart of normalisation.

03Example

Kolanut's customers.csv stores the sales rep's name on every customer row. Should reps be their own table?

There are only a few reps, each named on many customers. If a rep's surname changed, it would need updating on every one of their customers. In a fully normalised operational database, sales_reps(rep_id, rep_name, region) becomes its own table and customers keep a rep_id.

For analysis, though, a rep name on the customer table is often fine, as the next lesson explains.

04Walkthrough

Normalising a flat sheet, in order:

1. Find the grain and key of the flat sheet: here (invoice, product).
2. Split out repeating facts about the first key part: invoice date and customer → invoices.
3. Split out facts that depend on a non-key column: customer city → customers; product category → products.
4. Keep facts about the combination on the line table: quantity, price charged.
5. Add foreign keys so the tables join back together.
6. Test: can you rebuild the original sheet with joins? You should get exactly the same rows.

05Practice

06Check your understanding