Discuss the trade-offs between normalization and denormalization in database design.

Instruction: Explain the concepts of normalization and denormalization, and discuss the advantages and disadvantages of each approach in database design.

Context: This question aims to test the candidate's knowledge of database design principles and their ability to weigh the pros and cons of different design approaches.

Updated

Official answer

Normalization reduces avoidable duplication by organizing data around dependencies and authoritative facts. It can simplify updates and enforcement of consistency, but some reads require additional joins.

Denormalization deliberately duplicates or precomputes data for a defined use. Examples include a reporting dimension with repeated category names, a daily aggregate, or a cached customer summary. These can simplify reads, but create refresh, invalidation and reconciliation work.

Start with the business invariant and the workload. Identify the authoritative record, the acceptable delay before a derived copy changes, and the process that updates or rebuilds it. A materialized aggregate must have a freshness contract; it should not quietly become a competing source of truth.

Compare representative read latency, write amplification, storage, recovery and operational complexity before and after the change. Preserve result semantics for NULLs, duplicate facts and historical changes. More joins are not automatically slow, and fewer tables do not guarantee faster queries.

The decision can vary by layer: a transactional source may prioritize update integrity while an analytical serving model deliberately favors understandable aggregations.

Related Questions