When and how would you denormalize a schema?

Denormalization stores the same fact in more than one place, or stores a precomputed result. It trades write complexity and consistency risk for fewer joins and faster reads. It should be driven by a measured access pattern, not by a preference for wide tables.

ALTER TABLE orders
ADD COLUMN item_count INTEGER NOT NULL DEFAULT 0;

UPDATE orders o
SET item_count = (
  SELECT COUNT(*)
  FROM order_items i
  WHERE i.order_id = o.id
);

Maintenance strategies

• Update the copy in the same transaction as the source change
• Use a trigger for database-local invariants
• Use an outbox or event stream for cross-service read models
• Rebuild periodically with a reconciliation job and compare counts
• Store a source timestamp or version so stale data is detectable

The hard part is not writing the duplicated field, it is every path that changes the source. Enumerate all writers, make the update atomic where possible, and add monitoring for drift. For analytics, a materialized view is often a cleaner form of denormalization because it can be refreshed as a unit.