🗄 SQL & Databases · Chapter 9

Normalisation vs denormalisation

Normalise so a fact is stored once. Denormalise so a question is answered once. Which you want depends entirely on whether you're writing or reading.

Study this lesson free in the app →

The basics

Normalisation is designing tables so that every fact is stored in exactly one place; denormalisation is deliberately copying facts into wide tables so questions can be answered without joins.

The problem it solves: if a driver's phone number is copied onto 40,000 ride rows, changing it means 40,000 updates — and if the job dies halfway, the data disagrees with itself and nothing tells you which copy is right. Normalised design makes that impossible: the number lives in drivers, and rides point to it by driver_id.

The mental model: your phone's contacts list versus printed wedding invitations. Contacts hold each person once — edit one entry and you're never confused. The invitations carry copies of addresses because at posting time you don't want 200 lookups. Databases that take constant small writes (a payments system, a booking app) want the contacts list. Warehouses that rebuild tables in bulk and scan millions of rows want the invitations.

Explained

A driver changes their phone number.

Normalised: one row updates in drivers. Done.

Denormalised: the number is copied onto 40,000 ride rows. You update 40,000 rows, and if the job dies halfway, some rides now say one number and some say another — and there is no way to tell which is right.

That's normalisation's whole argument: store each fact exactly once, so it can't disagree with itself.

The counter-argument is equally simple, and it's why warehouses ignore all of this.

The address book and the wedding invitations

Your address book holds each person once. Someone moves, you change one entry, and you're never confused about where they live. That's normalised — one fact, one place.

But the wedding invitations you printed have addresses written on them. Two hundred envelopes, each with a copy. If someone moves after printing, some envelopes are wrong.

Why copy at all? Because at posting time you don't want to look up two hundred addresses. The copy exists so that the reading is fast.

Normalised = the address book, optimised for change. Denormalised = the envelopes, optimised for use. Both are correct; they're solving different problems, and the whole discipline is knowing which problem you have.

The normal forms, practically

Textbooks list six. Three matter, and the third is where most real bugs live.

1NF — no repeating groups. One value per cell.

✗  ride_id | driver_vehicles
   r1      | 'car, bike, scooter'      ← can't query, can't index
✓  a separate row per vehicle

2NF — no partial dependency on part of a composite key. If the key is (ride_id, vehicle_id), a column depending only on ride_id is misplaced.

3NF — no transitive dependency. A non-key column must not depend on another non-key column.

✗  rides(ride_id, driver_id, driver_phone, driver_city)
      driver_phone depends on driver_id, not on ride_id
✓  rides(ride_id, driver_id)  +  drivers(driver_id, phone, city)

The plain-English version, and it's enough:

Every non-key column describes the key, the whole key, and nothing but the key.

If a column in rides actually describes the driver rather than the ride, it belongs in drivers. That single test catches nearly everything the formal definitions do.

Watch an anomaly appear

Update, delete and insert anomalies are the three symptoms. They all come from the same cause: one fact stored in more than one place.

Why warehouses deliberately denormalise

Everything above assumes writes are frequent and reads are point lookups. In analytics both assumptions are false.

| | OLTP (normalised) | OLAP (denormalised) | | --- | --- | --- | | Writes | constant, small | bulk, append-only | | Reads | one row by key | scan millions, aggregate | | Cost of a join | small | large — shuffles across a cluster | | Cost of duplication | inconsistency | just storage, which is cheap |

A warehouse fact table rebuilt nightly has no update anomaly risk — nothing is updated in place. So the argument for normalisation largely evaporates, while the argument against joins gets stronger with every extra row.

The standard compromise is the star schema: a big fact table plus a handful of wide, denormalised dimension tables.

fact_rides(ride_id, driver_key, city_key, date_key, fare)
dim_driver(driver_key, driver_id, name, phone, city, vehicle_type, ...)

One join instead of five. And a snowflake schema — where dimensions are themselves normalised into sub-tables — is usually a step backwards for exactly the reason this lesson is about: it trades cheap storage for expensive joins.

Where does this column belong?

A single table:

rides(ride_id, ride_date, fare, driver_id, driver_name,
      driver_rating, city_id, city_name, city_timezone,
      surge_multiplier, payment_method)

The rating that changed history

A reporting table stores driver_rating directly on each ride, copied at query time from drivers:

SELECT r.ride_id, r.fare, d.rating
FROM rides r JOIN drivers d ON r.driver_id = d.driver_id;

In March, an analyst reports: "rides by drivers rated below 3 stars in January: 1,240".

In April, the same query for January returns 890.

Nothing about January changed. But drivers' current ratings did — some improved above 3 stars, and the join picks up today's rating, not January's.

The report is not reproducible, and every historical analysis quietly rewrites itself as the dimension drifts.

The fix is a Type 2 dimension — keep a row per version with validity dates, and join on the date:

JOIN dim_driver d
  ON r.driver_id = d.driver_id
 AND r.ride_date >= d.valid_from
 AND r.ride_date <  d.valid_to

Now January's report uses January's ratings, permanently.

The principle: a fact should record what was true when it happened. Joining a fact to a mutable dimension without a temporal condition means your history changes under you — and unlike most bugs, it produces numbers that were right yesterday and are right differently today.

Every non-key column describes the key, the whole key, and nothing but the key.

Normalise for writes (one fact, one place). Denormalise for reads (one question, one table).

Duplication costs consistency in OLTP and only storage in a warehouse.

Words to know

In two minutes

• Normalise to store each fact once, so it can't disagree with itself — the right default for OLTP systems.
• 3NF in one line: every non-key column describes the key, the whole key, and nothing but the key.
• Duplication causes update, insert and delete anomalies; nothing errors, the data just becomes wrong.
• Warehouses denormalise on purpose: tables are rebuilt, not updated, and every join is a shuffle.
• Star schema: a narrow fact table plus wide, denormalised dimensions — not snowflaked further.
• Denormalising a mutable attribute rewrites history; changing attributes need SCD Type 2 with validity dates.
• Ask what the table is for before choosing — write-heavy system of record vs read-heavy analytics.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.