Part 1 — The relational foundation › Chapter 05

Normalisation, and denormalising on purpose

1NF to BCNF in plain words, the anomaly each form removes, and when to break the rules deliberately.

From the advert

“Progettazione e modellazione di basi dati relazionali” — this is the chapter they mean. Expect to be asked to name the normal forms, and expect the follow-up to be “when would you break them?”

What normalisation is actually for

🧒 Simple

If you write your friend's phone number on twenty different pages of your notebook, then one day she changes it, you have twenty places to fix — and you will miss one. So you write it once, on its own page, and everywhere else you write “see page 4”. That is the whole idea.

🎓 Professional

Normalisation removes redundancy, and redundancy is what makes the three update anomalies possible. Update: a fact stored twice can be changed in one place and not the other, so the database now contradicts itself. Insertion: you cannot record a supplier until they have a product, because supplier facts only exist on product rows. Deletion: removing the last product from a supplier erases the supplier's address. Every normal form is a rule that removes one class of these.

The forms, in the order you will be asked for them

Form	The rule	The one-sentence version
1NF	Atomic values; no repeating groups	No comma-separated lists, no `phone1 phone2 phone3`
2NF	1NF, and no non-key column depends on part of a composite key	Only matters when the key is composite
3NF	2NF, and no non-key column depends on another non-key column	No transitive dependencies
BCNF	Every determinant is a candidate key	3NF's edge case, when candidate keys overlap

✔ The mnemonic that actually works

“Every non-key attribute must depend on the key (1NF), the whole key (2NF), and nothing but the key (3NF) — so help me Codd.” Say it and then give an example of each; the mnemonic alone reads as memorised.

One table, normalised in three steps

Start with the table every legacy system has somewhere:

The starting point: one flat table, the shape every legacy system has somewhere.

-- 0NF-ish. Everything about an order, flattened into one row per order.
order_id | customer_name | customer_city | city_region | products              | total
---------+---------------+---------------+-------------+----------------------+-------
    1001 | Rossi SpA     | Modena        | Emilia-R.   | "A12 x2, B7 x1"      | 89.00
    1002 | Rossi SpA     | Modena        | Emilia-R.   | "C3 x5"              | 45.00

How to read this, and what is already wrong

This is a result, not a statement: pipes are column separators, one line per row. Nothing here runs.
products holds a list inside one value. You cannot join to it, filter on it, or sum it without parsing text. That is the 1NF violation.
Rossi SpA and Modena are repeated on every order. Rename the customer and you must find every row — miss one and the database now holds two answers to the same question. That is an update anomaly, and it is what the next two steps remove.

1NF — make values atomic. products holds a list, so it cannot be joined, filtered or summed. Split it into rows in a child table:

1NF: the repeating list becomes rows in a child table.

orders(order_id, customer_name, customer_city, city_region, total)
order_lines(order_id, sku, quantity)          -- composite key (order_id, sku)

How to read this notation

table(col, col, …) is the standard shorthand for a relation and its attributes. It is not SQL — it is how a data model is sketched on a whiteboard, and being able to read it is worth as much in an interview as being able to write the CREATE TABLE.
Composite key (order_id, sku): neither column alone identifies a line, both together do. In SQL that is PRIMARY KEY (order_id, sku), and the column order decides which lookups the resulting index can serve (chapter 26).
What was one text value is now one row per product, so “how many of A12 did we sell” becomes a SUM instead of a parsing problem.

2NF — remove partial dependencies. If order_lines also carried product_name, that depends on sku alone — part of the composite key, not all of it. It moves to a products table.

3NF — remove transitive dependencies. In orders, city_region depends on customer_city, which depends on the customer, not on the order. Two hops from the key is one hop too many:

3NF: every fact sits in the table of the thing it is actually about.

customers(customer_id, name, city_id)                    -- a customer is in one city
cities(city_id, name, region_id)                         -- a city is in one region
regions(region_id, name)                                 -- the region name lives here, once
orders(order_id, customer_id, placed_at, total_cents)    -- an order points at its customer
order_lines(order_id, line_no, product_id, quantity, unit_price_cents)  -- and at its products
products(product_id, sku, name, list_price_cents)        -- the catalogue, independent of orders

What the split bought, and what it cost

Every non-key column now depends on its own key and nothing else. Renaming a region is one UPDATE of one row, and there is nowhere for a second spelling to hide.
unit_price_cents is deliberately duplicated from products. That is not a normalisation failure: the price at the time of the order is a different fact from the price today, and losing it would rewrite history every time the catalogue changes.
The cost is joins. Answering “revenue by region” now walks four tables — which is exactly what indexes and chapter 08 are for, and why chapter 39 denormalises deliberately for reporting.

⚠ Trap — the exception that is not a violation

order_lines.unit_price_cents looks like a 3NF violation: the price is on products, so why copy it? Because it is a different fact. The product table holds the current list price; the order line holds the price this customer was actually charged on that day. Copying it is not redundancy — it is capturing a value at a point in time. If you normalise it away, reprinting last year's invoice silently reprices it. Being able to explain this distinction is worth more in an interview than reciting BCNF.

Denormalising on purpose

🎓 The three legitimate reasons

Point-in-time capture. As above: the price charged, the address shipped to, the VAT rate applied. Not really denormalisation; a different fact that happens to look like one.
A measured read problem. A dashboard joins nine tables and takes eleven seconds. A summary table or materialised view fixes it. The word measured is load-bearing — denormalising before you have a slow query is how you get a fast wrong answer.
Analytics. A star schema is deliberately denormalised because its workload is different: bulk-loaded, rarely updated, and read with wide aggregate scans. See chapter 39.

If you denormalise, say how it stays true

Every denormalised copy needs a named mechanism keeping it correct: a trigger, a scheduled refresh, a materialised view, or an explicit “this may be up to 10 minutes stale and that is acceptable because…”. A denormalised column with no stated mechanism is a bug that has not happened yet. Answering “…and I would keep it in step with X, accepting Y staleness” is the difference between sounding senior and sounding reckless.

How far should you go?

3NF for anything transactional, and stop there unless you find a reason. BCNF, 4NF and 5NF exist and matter in edge cases, but almost every real schema in BCNF is already in 3NF, and I have never seen a production incident caused by a missing 5NF decomposition. Naming them shows you read the book; knowing that 3NF is where the value is shows you have used one.

Normalisation removes redundancy; redundancy is what makes update, insertion and deletion anomalies possible.
The key, the whole key, and nothing but the key — with an example for each, or it sounds memorised.
1NF means atomic values: no comma-separated lists, no phone1 phone2 phone3.
2NF only ever applies when the primary key is composite.
3NF is the practical target for transactional schemas. Go further only for a reason you can state.
A value captured at a point in time — the price charged, the address shipped to — is a different fact, not redundancy.
Denormalise only after measuring, and always name the mechanism that keeps the copy true.
Analytics schemas are denormalised on purpose because their workload is different, not because the rules stopped applying.

Interview questions this chapter answers

Explain 1NF, 2NF and 3NF.

Give the rule and an example for each. 1NF: a products column holding "A12 x2, B7 x1" becomes child rows. 2NF: with a composite key (order_id, sku), a product_name column depends only on sku, so it moves. 3NF: city_region depends on customer_city rather than on the order key, so cities and regions get their own tables.

When would you denormalise?

When I have measured a read problem the normalised design cannot solve, when the workload is analytical, or when the "duplicate" is really a point-in-time capture. And I would say in the same breath how the copy is kept correct and what staleness is acceptable.

Is storing the unit price on the order line a normalisation violation?

No — it is a different fact. products.list_price is the current price; order_lines.unit_price is what was charged that day. Normalising it away means reprinting an old invoice reprices it, which is wrong and in some jurisdictions illegal.