What are the trade-offs between normalization and denormalization?

Question

IT Interview Practice · Accepted Answer

**Normalization** organizes data so each fact lives in exactly one place, removing redundancy. **Denormalization** deliberately duplicates data to make reads faster. Most schemas start normalized and denormalize only where measurements demand it.

## Normalization (up to 3NF)

Each table describes one entity; related data is referenced by foreign key.

```sql
-- Normalized: a customer's city is stored once
customers(id, name, city_id)
cities(id, name)
orders(id, customer_id, total)
```

- **Pros** — no update anomalies (change a city name in one row), smaller storage, integrity via foreign keys.
- **Cons** — reads need JOINs; a report touching many entities can join five tables.

## Denormalization

```sql
-- Denormalized: city copied onto every order for fast reporting
orders(id, customer_id, city_name, total)
-- + a summary table maintained by the app or triggers
order_daily_totals(day, city_name, total_sales)
```

- **Pros** — fewer joins, faster reads, cheaper aggregates.
- **Cons** — duplicated data must be kept in sync (write more, risk drift), more storage, and every writer must remember to update every copy.

## How to decide

Default to normalized for correctness. Denormalize a **specific hot read path** only after EXPLAIN shows joins are the bottleneck — and prefer keeping the source of truth normalized while maintaining a derived copy (summary table, materialized cache) you can rebuild.

## Why it matters

This is a core schema-design trade-off: over-normalizing makes dashboards crawl, over-denormalizing breeds inconsistent data that's expensive to reconcile. Knowing *why* you'd duplicate data — and how to keep the copy trustworthy — separates a schema that scales from one that rots.

Normalization (up to 3NF)

Denormalization

How to decide

Why it matters

Related questions