Denormalisation and When to Use It

Concept

Denormalisation is the deliberate introduction of redundancy into a normalised schema in order to reduce the cost of reading. It is not the absence of normalisation — you normalise first, measure, and then relax specific decisions with reasons.

The distinction that matters: an unnormalised schema is one nobody thought about. A denormalised schema is one somebody thought about twice.

Why reads can be slow in a normalised schema

  Fully normalised, to show one order line on a screen:

    ORDER_LINE -> ORDER -> CUSTOMER
        |           |
        |           +-> ADDRESS
        +-> PRODUCT -> CATEGORY -> DEPARTMENT

  Six joins for one line of output. Correct, and on a busy
  listing page, measurably slow.

The common techniques

Worked example: the order total

  NORMALISED
    orders      ( order_no, cust_no, ordered_on )
    order_lines ( order_no, line_no, product, qty, price )

    the total is computed by summing the lines.
    Correct always. Costs a scan of the lines every time
    an order list is displayed.

  DENORMALISED
    orders      ( order_no, cust_no, ordered_on, total )

    the total is read directly. An order list becomes one
    scan of orders with no join at all.

  WHAT YOU NOW OWE
    every insert, update or delete of a line must update
    the total, IN THE SAME TRANSACTION
    a periodic reconciliation must confirm the stored total
    still equals the sum of the lines
    the reconciliation must be monitored, or it is useless

The decision checklist

Denormalise only when all of these are true:

1. The read is measurably slow — you have timings, not a suspicion.
2. Indexing and query rewriting were tried first and were not enough.
3. The read to write ratio is high. Denormalisation trades write cost for read speed; it is a bad trade on a write heavy table.
4. The duplicated value changes rarely, or the update path is fully controlled.
5. Every write path is known, so all of them can maintain the copy.
6. A reconciliation check exists to detect drift.

Legitimate cases that are not really denormalisation

Some duplication is correct design rather than a performance compromise, because the copied value is a historical fact rather than a duplicate of a current one.

Ask: is the copied value the same fact, or a snapshot of a fact at a moment in time? If it is a snapshot, storing it is not denormalisation at all — it is the only correct design.

Risks

• Drift. The copy and the source disagree, and nothing detects it until someone notices a wrong number.
• Hidden write paths. A migration script, an admin tool or a bulk import updates the source and not the copy.
• Anomalies return. Every problem in note 6161 comes back, because their cause has been reintroduced.
• Complexity moves to the application, where it is not enforced by anything.
• It is hard to reverse once queries and reports depend on the denormalised shape.

Safer alternatives to try first

1. Add the right index. Most slow joins are missing indexes, not too many joins.
2. Rewrite the query. Fetch what is needed and no more.
3. Cache the result outside the database, where staleness is visible and bounded.
4. Use a materialised view so the DBMS maintains the copy rather than your code.
5. Separate reporting from transactions, with a replica shaped for reading.

Worked decision

  PROBLEM
    a product listing shows the category name; the join to
    categories appears on every page; the page is served
    thousands of times a minute

  STEP 1  is it actually slow?
            measure. If the join costs under a millisecond
            on an indexed key, stop here - it is not the
            problem

  STEP 2  is the index right?
            products.category_id must be indexed

  STEP 3  read to write ratio?
            categories change perhaps monthly; the listing
            is read constantly -> extremely read heavy

  STEP 4  controlled write path?
            only an admin screen renames a category -> yes

  DECISION
    copying category_name into products is defensible.

  OBLIGATIONS ACCEPTED
    renaming a category must update every product row in
    the same transaction
    a nightly check must compare the copies against the
    source and alert on any mismatch
    the decision, and its reason, is written into the
    schema documentation

Common mistakes

• Denormalising before measuring. The most common and least defensible error.
• Skipping indexes. An index usually solves the problem with none of the cost.
• Not updating both copies in one transaction. A crash between the two writes creates exactly the inconsistency you were told to avoid.
• Denormalising a write heavy table. The trade runs the wrong way.
• Calling an unnormalised design denormalised. One is a decision; the other is an omission.
• Never reconciling. Drift is silent by nature.

Exam and interview questions

1. Define denormalisation and distinguish it from an unnormalised design.
2. List four denormalisation techniques and the cost of each.
3. State the conditions under which denormalisation is justified.
4. Give an example where duplicating a value is correct design rather than denormalisation.
5. What must be true about the write path before a value may be duplicated?

Practice

1. For an e-commerce listing page, propose one denormalisation, list every write path that must maintain it, and write the reconciliation check.
2. Argue against denormalising a table that receives more writes than reads.
3. Identify three places in the college schema of Phase 4b where a snapshot value would be correct design.

Conclusion

Normalise first, measure, then denormalise deliberately and locally with a written reason, a controlled write path and a reconciliation check. And separate the two cases carefully: copying a current fact is a performance trade, while copying a historical fact was correct all along.