Free preview
12 minNormalization vs Denormalization
Understand database normalization (1NF–3NF) and when to intentionally denormalize for performance.
Normalization eliminates data redundancy and update anomalies:
❌ Denormalized (bad) — city_name repeated in every order:
orders: (id, user_id, city_name, city_country, product_name, qty)
→ Updating a city name requires updating every row
✅ Normalized — city in a separate table:
orders: (id, user_id, city_id, ...)
cities: (id, name, country)
→ Update city name once
1NF: Each column has atomic values (no arrays or lists in a cell)
2NF: No partial dependencies (every column depends on the whole primary key)
3NF: No transitive dependencies (B depends on A; C depends on B — move C to a separate table)
Multiple choice
A table has: user_id (PK), order_id (PK), user_email, product_name. user_email only depends on user_id, not order_id. Which normal form is violated?
- A2NF — partial dependency: user_email depends on part of the composite key
- B1NF — non-atomic value
- C3NF — transitive dependency
- DNo violation
When to denormalize intentionally:
-- Normalized: calculating order total requires JOINs
SELECT SUM(p.price * oi.quantity)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 123;
-- Denormalized: store pre-computed total on orders table
SELECT total_amount FROM orders WHERE id = 123;Denormalize when: (1) reads massively outnumber writes, (2) JOIN performance is the bottleneck, (3) the calculated value changes infrequently.
Multiple choice
An analytics dashboard queries order totals 10,000 times per minute. Computing the total via JOINs is slow. What should you do?
- AStore total_amount on the orders table — denormalize this computed field
- BAdd more indexes to the joins
- CCache the result in the application
- DAdd more database RAM
Fill in the blank
3NF eliminates ___ dependencies (A→B, B→C: move C to a table where B is the key).
Multiple choice
A products table stores: tags TEXT (e.g., 'electronics,portable,sale'). Which normal form is violated?
- A1NF — the tags column is not atomic (contains multiple values)
- B2NF — partial dependency
- C3NF — transitive dependency
- DNo violation