Database schema design — normalization vs denormalization tradeoffs

Database schema is either over-normalized (too many JOINs, slow reads) or under-normalized (data duplication, update anomalies). Need guidance on when to normalize and when to denormalize.

(1) Start normalized (3NF): eliminate data duplication, ensure referential integrity. (2) Denormalize selectively for read performance: add computed columns, materialized views, or duplicate data for hot read paths. (3) Denormalize when: read:write ratio is very high (>100:1), JOINs are too expensive, data changes rarely. (4) Keep normalized when: data integrity is critical (financial), writes are frequent, storage is a concern. (5) Hybrid approach: normalized source of truth + denormalized read replicas or search indexes (Elasticsearch). (6) Document databases (MongoDB): embed related data that's always read together; reference data that's shared across documents. (7) Always denormalize with a sync mechanism (triggers, events, materialized views) — never manual.

Normalization optimizes for write correctness (single source of truth). Denormalization optimizes for read performance (pre-computed JOINs). The right choice depends on your access patterns.