Where each fact should live

The rule, before the jargon

Normalisation has an intimidating vocabulary and one idea underneath it: every fact is stored once, in the table about the thing it is a fact about. Everything else is a consequence.

Take a badly shaped table:

order_id | customer_name | customer_phone | customer_city | product | qty | price

Three of those columns are facts about the customer, not about the order. So they are repeated on every order the customer places. Change the phone number and you must change it in 34 places, and last week’s export still holds the old one. This is exactly the tuition-centre spreadsheet from the first lesson, and it is what normalisation removes.

The three steps, in plain language

Step one: no repeating groups. A column named phone1, phone2, phone3 is a list crammed into a row. So is a tags column holding "urgent,billing,vip". Both are fine until you have to ask “how many customers have a billing tag”, at which point you are writing string searches instead of counting rows. The fix is a second table with one row per phone or per tag.

Step two: no partial dependency. If your key is (order_id, product_id) and a column depends only on order_id (the order date, say) it belongs in the orders table, not repeated on every line item. Otherwise two line items can disagree about when their own order happened.

Step three: no transitive dependency. A column that depends on another non-key column belongs elsewhere. customer_city depends on the customer, and the customer depends on the order. Move it to customers.

Applied to the table above:

customers   (id, full_name, phone, city)
orders      (id, customer_id, placed_at, status)
order_items (order_id, product_id, qty, unit_price)
products    (id, name, current_price)

Now the phone number exists in one row. The city exists in one row. A product’s name exists in one row.

The exception that is not an exception

order_items.unit_price looks like a violation: the price is a fact about the product, surely? It is not. products.current_price is what the product costs now. order_items.unit_price is what this customer was charged, which is a fact about that line of that order and must never change again. If you normalise it away and join to products for a price, every historical invoice silently rewrites itself the next time somebody runs a sale.

The same reasoning applies to a delivery address copied onto a shipped order, and a tax rate recorded on an invoice. Anything that was true at a moment and must stay true is a fact about the event, not about the entity. Copying it in is correct design, not duplication.

That distinction (a current value versus a recorded value) is the one people most often get wrong, in both directions.

When to denormalise on purpose

Normalisation is a default, not a religion. Deliberate denormalisation is a real technique with a real cost:

• A reporting table with one wide row per order, built nightly, because forty analysts joining six tables is slower and more error-prone than one scan.
• A cached order_count on the customer row, because the count is read on every page load and computed rarely.

Both are fine if two conditions hold: the derived value has exactly one place that writes it, and there is a way to rebuild it from the source of truth. A cached count updated in three different code paths will drift, and once it has drifted nobody can tell which number is real. Write it in one place, and keep a job that recomputes it and complains when it disagreed.

The rule is: normalise the system of record, denormalise the copies you make from it.

How to tell if a table is wrong

Three questions, faster than any theory:

1. Can I change one true fact about the world by updating one row? If a change of city needs an UPDATE with a WHERE clause that touches many rows, the city is in the wrong table.
2. Can two rows disagree with each other about the same fact? If orders has both customer_city and a customer_id pointing to a customer with a different city, your data already contains a contradiction and no query can tell you which side is right.
3. Is there a column whose value is a list? Commas inside a cell mean a missing table.

The honest cost

Normalised data needs joins, and joins are the part of SQL people find hard, which is most of the next module. Very wide, very denormalised tables are easier to query and are exactly what analytical warehouses build on purpose.

The trade is: normalised data is harder to read and almost impossible to make inconsistent; denormalised data is easy to read and drifts out of agreement with itself unless something rebuilds it. Systems that take writes should be normalised. Tables built for reading can be as flat as you like, as long as everyone knows they are derived.