DBMS

Database Design

When to Denormalise

A design that was never normalised is not

JrCodex·7 min read

Jr Codex DBMS Notes

Level: Intermediate Prerequisites: Chapter 4: Normalisation Time to complete: ~20 minutes

Table of Contents

  1. Denormalisation Is a Trade, Not a Shortcut
  2. The Five Patterns
  3. Keeping Redundant Data Honest
  4. Historical Snapshots
  5. The Decision Procedure
  6. Summary & Next Steps

1. Denormalisation Is a Trade, Not a Shortcut

The Exchange
─────────────────────────────────────────
  YOU GAIN    fewer joins, faster reads

  YOU PAY     redundant data
              slower, more complex writes
              the anomalies of Chapter 3, back
              a permanent obligation to keep
                copies in sync
─────────────────────────────────────────

The Precondition
─────────────────────────────────────────
  Denormalise only AFTER normalising.

  A design that was never normalised is not
  denormalised — it is unconsidered. You cannot
  make an informed trade without knowing what the
  correct design was.

  And denormalise only after MEASURING that the
  join is genuinely the bottleneck. It usually is
  not; a missing index usually is (Module 5).
─────────────────────────────────────────

2. The Five Patterns

1. STORED AGGREGATES
─────────────────────────────────────────
  Keep a running count or sum rather than
  recomputing it.

    ALTER TABLE posts ADD COLUMN comment_count INT
      NOT NULL DEFAULT 0;

  WHEN: the aggregate is read far more often than
  the underlying rows change — a comment count
  shown on every page view.

2. DUPLICATED COLUMNS
─────────────────────────────────────────
  Copy a column from a parent to avoid a join.

    ALTER TABLE order_items ADD COLUMN product_name TEXT;

  WHEN: the join is on a very hot path and the
  source value rarely changes.
  RISK: highest of all five. Two copies, one truth.

3. PRE-JOINED TABLES
─────────────────────────────────────────
  A materialised view, or a reporting table
  refreshed on a schedule (Module 3, Chapter 6).

  WHEN: expensive analytical queries that tolerate
  being minutes stale.
  BEST option of the five — the redundancy is
  explicit, isolated, and rebuildable.

4. REPEATING GROUPS
─────────────────────────────────────────
    phone1, phone2, phone3

  WHEN: the count is genuinely fixed and small and
  will not grow. Rarely true.
  Almost always a mistake — this is the 1NF
  violation from Chapter 4 with extra steps.

5. STAR SCHEMAS
─────────────────────────────────────────
  A central fact table with wide, denormalised
  dimension tables.

  WHEN: analytics and data warehousing. This is the
  STANDARD design there, not a compromise —
  workloads are read-mostly and bulk-loaded, so
  update anomalies barely apply.
─────────────────────────────────────────

3. Keeping Redundant Data Honest

Once a value is stored twice, something must keep the copies in agreement. There are four mechanisms, in decreasing order of reliability.

-- 1. TRIGGERS — the database maintains it. Cannot be bypassed.
CREATE TRIGGER bump_comment_count AFTER INSERT ON comments
BEGIN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
END;
 
CREATE TRIGGER drop_comment_count AFTER DELETE ON comments
BEGIN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
END;
The Four Mechanisms
─────────────────────────────────────────
  TRIGGERS         the database enforces it. Every
                   writer is covered, including
                   manual fixes. Cost: invisible
                   logic, harder debugging.

  APPLICATION      update both in one transaction.
  CODE             Visible and testable. Fails the
                   moment anything else writes to
                   the table.

  SCHEDULED        recompute periodically.
  RECONCILIATION   Tolerates drift; good for
                   pre-joined tables.

  MATERIALIZED     the database owns the copy and
  VIEWS            the refresh. Cleanest when your
                   database supports it.
─────────────────────────────────────────

-- Whichever you choose, WRITE A DRIFT CHECK and run it on a schedule.
SELECT p.id, p.comment_count AS stored, COUNT(c.id) AS actual
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.comment_count
HAVING p.comment_count <> COUNT(c.id);        -- should return ZERO rows
The Rule
─────────────────────────────────────────
  Every denormalised value needs a query that can
  detect drift, and that query should run
  automatically.

  Redundancy without a drift check is not a
  performance optimisation. It is a data-corruption
  bug with a delay on it.
─────────────────────────────────────────

4. Historical Snapshots

The one case where duplication is not denormalisation at all — and the one people most often get wrong in the opposite direction.

The Problem
─────────────────────────────────────────
  order_items references products(id), and reads
  the price by joining.

  The product's price changes next month.

  Every historical invoice now shows the NEW price.
  Your accounts no longer match what customers
  actually paid.
─────────────────────────────────────────

CREATE TABLE order_items (
    order_id     INTEGER NOT NULL REFERENCES orders(id),
    product_id   INTEGER NOT NULL REFERENCES products(id),
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC NOT NULL,        -- the price AT THE TIME OF SALE
    product_name TEXT    NOT NULL,        -- the name AT THE TIME OF SALE
    PRIMARY KEY (order_id, product_id)
);
Why This Is NOT Denormalisation
─────────────────────────────────────────
  `products.price` is the CURRENT price.
  `order_items.unit_price` is the price PAID.

  These are two different facts that happen to
  have been equal once. Neither is a copy of the
  other.

  There is no redundancy, no drift, and nothing to
  reconcile — so no anomaly is possible.
─────────────────────────────────────────

The General Principle
─────────────────────────────────────────
  Any value that was true AT A MOMENT and must
  remain true afterwards should be stored, not
  derived.

  Prices, tax rates, addresses on shipped orders,
  terms accepted at signup, exchange rates.

  Asking "is this the current value, or the value
  that applied?" resolves most arguments about
  whether a column is duplication.
─────────────────────────────────────────

5. The Decision Procedure

Before Denormalising, In Order
─────────────────────────────────────────
  1. MEASURE. Which query is slow, how slow, and
     how often does it run?

  2. READ THE PLAN (Module 6, Chapter 4). Is the
     join actually the cost, or is it a sequential
     scan?

  3. ADD AN INDEX (Module 5). This fixes most
     apparent join problems and costs no
     correctness.

  4. REWRITE THE QUERY. Aggregate earlier, select
     fewer columns, avoid the fan-out.

  5. CACHE OUTSIDE THE DATABASE. Often simpler than
     changing the schema, and trivially discardable.

  6. MATERIALISE. A view or a rebuilt reporting
     table — redundancy that is explicit and
     isolated.

  7. ONLY THEN denormalise columns, with a
     synchronisation mechanism and a drift check.
─────────────────────────────────────────

The Documentation Requirement
─────────────────────────────────────────
  Every denormalised column should carry a comment
  saying WHY, WHAT keeps it in sync, and HOW to
  detect drift.

    COMMENT ON COLUMN posts.comment_count IS
      'Denormalised for the feed query. Maintained
       by triggers bump/drop_comment_count.
       Drift check: reports/comment_count_drift.sql';

  Without that, the next engineer sees a redundant
  column, assumes it is a mistake, and either
  removes it or — worse — starts writing to it
  directly.
─────────────────────────────────────────

6. Summary & Next Steps

Key Takeaways

  • Denormalisation trades write complexity and redundancy for read speed, and is only meaningful after a normalised design exists to trade against.
  • Pre-joined and materialised tables are the safest pattern because the redundancy is explicit, isolated and rebuildable; duplicated columns are the riskiest.
  • Every denormalised value needs a synchronisation mechanism and an automated drift check — redundancy without one is a corruption bug on a delay.
  • Storing a historical value such as price-at-sale is not denormalisation: the current price and the price paid are two different facts, so no drift is possible.

Module 4 Complete — What's Next

You can now design a schema that stores each fact once and knows when to break that rule deliberately. Module 5 goes underneath the schema: how rows are actually laid out on disk, and why an index turns a full scan into a handful of page reads.

Concept Check

  1. Why is "we never normalised, so we are denormalised" a category error?
  2. Give the argument that order_items.unit_price is not redundant with products.price.
  3. Which three steps should be exhausted before denormalising a column, and why is indexing among them?

Next Module

→ Module 5: Storage and Indexing

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index