CodeParkNormalisation stores each fact once. Denormalisation copies data to make reads faster. Start normalised, and denormalise only for read problems you have measured.
Normalisation: each fact lives in one place.
- Writes are simple and safe.
- There are no update anomalies. An update anomaly is when you change one copy of a fact and forget another copy.
- Constraints keep the data consistent.
- The cost: reads need more joins.
Denormalisation: you copy or precompute data, so busy reads skip joins or aggregates. Examples: storing order_count on customers, or storing the customer name on each order. The cost: every write must keep the copies in sync. You can do this with application code, triggers or background jobs. A bug there produces data that silently disagrees.
Good candidates for denormalisation:
- read-heavy aggregates, like counts and totals
- reporting tables
- materialized views (a saved query result that the database stores like a table)
Sometimes a copy is correct by design. The price on an order line should be the price at purchase time. It should not point to the current product price, because that price can change later.
- A normalised schema has one source of truth, but reads need more joins.
- A denormalised schema gives faster reads, but writes are harder and copies can drift apart.
- Keep copies in sync with triggers, background jobs or application code.
- Denormalise only for measured bottlenecks, for example with materialized views.
- Historical snapshots, like the price on an order, are intentional copies.