Normalization vs Denormalization
Meera wants revenue by product category and by customer state. In the shop's database, that simple question touches six tables. Ishaan's query is long, slow and easy to get wrong, and every analyst who asks something similar writes the same joins again. The shop's design is not bad. It was built for a different job.
The rule
Normalize to write safely; denormalize to read quickly.
A normalized design stores each fact exactly once and links tables with keys. The shop's database looks like this:
orders(order_id, customer_id, order_date)order_lines(order_id, product_id, qty, price)products(product_id, name, category_id)categories(category_id, category_name)customers(customer_id, city_id)cities(city_id, city, state)
The category name "Bed Linen" lives in one row of categories. Rename it there and every product follows. There is no copy to forget, so the data cannot contradict itself. That is exactly what an application making many small writes needs.
A denormalized design copies descriptive values next to the numbers, so reading needs fewer joins:
-- Normalized: six tables, five joins
SELECT cat.category_name, ci.state, SUM(ol.qty * ol.price) AS revenue
FROM order_lines ol
JOIN orders o ON o.order_id = ol.order_id
JOIN customers cu ON cu.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = cu.city_id
JOIN products p ON p.product_id = ol.product_id
JOIN categories cat ON cat.category_id = p.category_id
GROUP BY cat.category_name, ci.state;
-- Denormalized: one wide table, no joins
SELECT category, state, SUM(revenue) AS revenue
FROM sales_wide
GROUP BY category, state;The price is repetition. "Bed Linen" now sits on thousands of sales rows. If people edited those rows by hand, a rename could update some and miss others, and the table would contradict itself: an update anomaly. In a warehouse that risk is small, because nobody types into these tables. A nightly job rebuilds them from the normalized sources, so a rename flows through automatically.
Most BI models take a middle road, the star schema of the next lesson: slim tables of numbers, surrounded by wide, denormalized tables of descriptions.
The shortcut
Ask who writes and who reads? Many small writes by an application → normalize. Big reads by many analysts, with data rebuilt by a load job → denormalize.
Count the hops
From the numbers in
order_lines, category is two joins away and state is three: five joins in total.
Drills
Answer fast — trust the rule.
A query must connect six tables. How many joins does it need, at minimum?
In the normalized shop database, "Bed Linen" is renamed "Bedding". How many rows must change?
Which system should stay normalized?
Why is denormalizing safe in the warehouse but risky in the shop's database?
Remember
- Normalize to write safely; denormalize to read quickly.
- Ask who writes and who reads: apps writing → normalize; analysts reading → denormalize.
- Trap: hand-editing denormalized copies, which lets them contradict each other.