Denormalization

The Trade Inverted

 normalization optimizes WRITES (one fact, one place).
 denormalization optimizes READS (copy facts to where they're asked).

 orders table, denormalized:

 order_id │ customer_name │ city │ total │ item_count
 ─────────┼───────────────┼──────┼───────┼──────────
 o-1001   │ Sarah Chen    │ nyc  │ 4250  │ 3

 invoice page renders from ONE row, zero joins.
 cost: name/city now live in TWO places → must stay in sync.

The Denormalization Catalog

 1. COPIED COLUMNS
    orders.customer_name ← users.name
    read win: no join on hot path
    
 2. PRECOMPUTED AGGREGATES
    users.order_count ← maintained on insert/delete
    read win: COUNT query becomes a column read
    
 3. EMBEDDED/JSON FIELDS
    orders.shipping_address JSONB (frozen snapshot at purchase)
    read win + CORRECTNESS: address is historical fact,
    should NOT follow user's current address anyway!
    
 4. DERIVED DOCUMENTS / READ MODELS
    full "order view" assembled once at write time into one doc
    read win: entire page = single fetch (CQRS territory)

The Sync Problem — Where Designs Die

 every copy needs an UPDATE STORY:

 mechanism          consistency     complexity
 ─────────────────────────────────────────────────
 same-transaction   strong          low (same DB)
 trigger            strong          medium
 app-level writes   eventual        high (bugs lurk)
 CDC/event-driven   near-real-time  infrastructure
 
 rule: NO copy without a named sync mechanism.
 "we'll remember to update it" = future corruption report.

 also decide staleness tolerance:
   shipping_address snapshot: never updates (correct!)
   customer_name display:     eventual OK (cosmetic)
   order_count badge:         seconds of lag fine

When Denormalization Pays

Signal	Example
Read ≫ write ratio (100:1)	Product pages
Joins across SHARDS	Co-locate what’s queried together
Aggregates computed constantly	Dashboards, badges
Historical snapshots are semantically right	Order addresses/prices

 and when it doesn't:
 ✗ write-heavy tables (sync overhead dominates)
 ✗ frequently-changing copied fields (invalidation churn)
 ✗ "joins feel slow" without measurement (index them first!)

The Discipline Checklist

 for each denormalized field:
 □ named sync mechanism (trigger/CDC/app code)
 □ staleness budget stated (strong? seconds? forever-snapshot?)
 □ reconciliation job exists (finds drift when bugs happen)
 □ documented WHY in schema comments

 drift detection query runs nightly; drift found = bug filed.
 trust but verify applies double to redundant data.

Interview Framing

Denormalization answers score when they include the SYNC story: “I’d copy customer_name onto orders for invoice rendering — trigger-maintained, cosmetic-staleness-tolerant.” The snapshot-vs-live distinction (addresses frozen at purchase being MORE correct) shows modeling maturity beyond performance reflexes.