Data Modeling & Schema Design
Denormalization Trade-offs
Denormalization deliberately stores derivable or repeated data to improve a measured read path. It trades read simplicity or latency for write amplification, consistency machinery, storage, and more complicated repair.
- Denormalization begins with a correct normalized source of truth.
- A performance hypothesis must name the query, target, and evidence.
- Every duplicate needs a freshness and failure contract.
- Snapshots can be semantically correct rather than denormalized.
- Materialized views and summary tables centralize derivation but still consume storage and refresh capacity.
- The operational design includes backfill, reconciliation, idempotency, observability, and rollback—not only an extra column.
Choosing how to serve order totals
| Design | Read path | Write and consistency cost | Best fit |
|---|---|---|---|
Compute from order_items | Aggregate at read time | Single source; no duplicate maintenance | Moderate line counts or infrequent reads |
Stored orders.total_amount | Direct header read | Every line mutation must update total atomically | Hot read path with tightly controlled writes |
| Materialized aggregate | Read precomputed result | Refresh work and possible staleness | Reporting or bounded-lag feeds |
| Cache outside schema | Fast keyed lookup | Invalidation, eviction, and cold misses | Disposable acceleration where database remains authoritative |
SELECT o.order_id,
o.total_amount AS stored_total,
COALESCE(SUM(i.quantity * i.unit_price), 0) AS derived_total
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> COALESCE(SUM(i.quantity * i.unit_price), 0);