Denormalization
Practice this topic in a realistic system design interview
In this chapter, we'll learn what denormalization is, why systems intentionally store duplicate data, and the trade-offs that come with keeping those copies consistent.
1. Starting From a Normalized Schema
To understand denormalization, we first need to understand what it moves away from.
When we design a relational database, we usually start with a normalized schema. Suppose we're building an e-commerce application. We might have four tables:
users(id, name, email)products(id, name, price)orders(id, user_id, created_at, total)order_items(id, order_id, product_id, quantity)
Each piece of information lives in exactly one place. The customer's name is stored only in the users table. The product's name is stored only in the products table. An order simply stores the id of the user who placed it.
This design has real benefits:
- If a customer changes their name, we update one row, and every part of the system sees the new value.
- There's no risk of two tables disagreeing about the same fact.
- We don't waste storage repeating the same values across millions of rows.
So normalization gives us a single source of truth. But it comes with a cost that shows up when we read the data.
2. The Cost of Joins
Suppose a customer opens their order history page. For each order, the page shows the order date, the total amount, the customer's name, and the names of the products they bought.
In a normalized schema, that information is spread across four tables. So the database has to join orders with users, then with order_items, then with products.
For one request on a small dataset, this is fast.
But now imagine the orders table has hundreds of millions of rows, and this page is loaded thousands of times per second. Every request repeats the same joins. The database has to look up matching rows in several tables, often through several index lookups per order. That work adds up in CPU, memory, and disk reads.
The problem gets worse when the data no longer fits on one machine. If users are stored on one shard and orders on another, a join may require fetching data across the network from multiple database servers. Many distributed databases don't support joins across nodes at all.
So the question becomes: what if we stored the data in the shape the page needs, so the database doesn't have to assemble it on every read?
That's the idea behind denormalization.