sdlc.dev

SQL and PostgreSQL path · Shaping the data · Topic 6 of 13

Normalisation and when to denormalise

Not started

Normalisation means arranging tables so that each fact is stored once: a customer's email lives in the customers row, and orders refer to the customer by id. The formal normal forms (first, second, third, Boyce-Codd) are precise ways of saying "no column depends on anything but the whole key". In day-to-day work the question is simpler: if this value changes, how many places must change with it?

Why it matters: Duplicated facts drift apart; but some reads need copies, and the trick is knowing which copy is the truth.

  1. 1Each fact once

    A normalised schema stores each fact in one row of one table, so an update changes it in one place.

  2. 2Anomalies are the cost of copies

    When the same fact is stored twice, an update that misses one copy leaves the data contradicting itself.

  3. 3Denormalise for a measured read

    Copies, counters and pre-joined tables make specific reads cheaper, at the price of keeping them in step.

  4. 4Snapshots are not duplicates

    An order's price at purchase time is a different fact from the product's current price, and belongs on the order.

1

How it works

The explanation, with examples you can run.

What goes wrong with copies

Suppose orders carry a copy of the customer's email and country:

-- denormalised: the customer's details are repeated on every orderCREATE TABLE orders_wide (  id             bigint PRIMARY KEY,  customer_id    bigint NOT NULL,  customer_email text   NOT NULL,  customer_country text NOT NULL,  total_cents    integer NOT NULL);

Three kinds of anomaly follow:

  • Update. The customer changes their email; every one of their orders must be updated, and any that are missed now disagree.
  • Insert. You cannot record a customer who has not ordered yet, because customer data only exists on orders.
  • Delete. Deleting a customer's last order also deletes the only record of the customer.

The normalised version keeps customers in their own table, with orders.customer_id REFERENCES customers (id), and a join puts them back together when a query needs both. With an index on the join column, that join is cheap; it is rarely the bottleneck people fear.

Copies that are really snapshots

Not every repeated column is duplication. A line on an order should store the price that was charged, even though the products table has a price:

CREATE TABLE order_lines (  order_id         bigint  NOT NULL REFERENCES orders (id),  product_id       bigint  NOT NULL REFERENCES products (id),  quantity         integer NOT NULL CHECK (quantity > 0),  unit_price_cents integer NOT NULL,   -- the price at checkout, not a copy of products.price  PRIMARY KEY (order_id, product_id));

The catalogue price will change; the price paid must not. The same goes for a shipping address on an order. These are separate facts that happen to look alike.

Denormalising on purpose

Sometimes a read is too slow or too frequent to compute each time, and a copy is worth it:

  • Counters and totals (review_count, balance_cents), updated in the same transaction as the rows they summarise.
  • Generated columns, which the database computes from other columns in the same row, so they cannot drift: total_cents integer GENERATED ALWAYS AS (quantity * unit_price_cents) STORED.
  • Materialised views for reports, refreshed on a schedule with REFRESH MATERIALIZED VIEW (add CONCURRENTLY, which needs a unique index on the view, to avoid blocking readers).
  • Read models in another store (a search index, a cache), fed from the database's changes.

The rule for all of them: one place is the source of truth, and every copy says how it is kept in step and how stale it may be. Measure the slow read first; a missing index is a far more common cause than too many joins.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.The orders table stores customer_email next to customer_id. A customer changes their email. What goes wrong?
  2. 2.Should order_lines store unit_price, even though products already has a price?
  3. 3.A product page shows a review count that needs a COUNT(*) over millions of rows on every view. What is a reasonable denormalisation?

0 of 3 right so far. Get all three to finish this topic.

Go deeper