Denormalization

Introduction

In the previous lesson, you learned about normalization — the process of organizing tables to eliminate redundant data and avoid update anomalies. Normalization is almost always the right starting point for designing a database.

But normalization is not free. Every time you split data into separate, related tables, you also make it more expensive to read that data back out, because you now need JOINs to reassemble it. Denormalization is the deliberate, controlled decision to undo some of that splitting in order to make specific reads faster.

What is Denormalization?

Denormalization means deliberately introducing some redundancy back into an already-normalized design, usually by duplicating a column from one table into another, so that a common query no longer needs a JOIN to get that data.

This is different from never normalizing in the first place. Denormalization is a conscious, targeted decision made after you understand the normalized design — you know exactly which redundancy you are introducing and why, rather than accidentally leaving your schema messy.

Why Denormalize?

A fully normalized schema minimizes redundancy, but every JOIN required to reassemble related data costs the database time — especially as tables grow to millions of rows, or when a report runs very frequently.

Denormalization is most useful when reads vastly outnumber writes, and when a particular query is both expensive (many JOINs, huge tables) and run very often (a homepage, a dashboard, a nightly report).

A Worked Example

Imagine a normalized design with a "customers" table and an "orders" table, linked by customer_id. Every time you want to show an order along with the customer's name, you need a JOIN.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Every report needs a JOIN to show the customer's name
SELECT o.order_id, c.name, o.order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

+----------+-------+-------------+
| order_id | name  | order_total |
+----------+-------+-------------+
| 1        | Priya | 250.00      |
| 2        | Alex  | 90.00       |
+----------+-------+-------------+

If this report runs thousands of times per day, denormalizing "orders" by adding a customer_name column directly avoids the JOIN entirely for this common read.

-- Denormalized: customer_name duplicated onto orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    order_total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders VALUES (1, 101, 'Priya', 250.00);
INSERT INTO orders VALUES (2, 102, 'Alex', 90.00);

-- No JOIN needed anymore
SELECT order_id, customer_name, order_total FROM orders;

+----------+---------------+-------------+
| order_id | customer_name | order_total |
+----------+---------------+-------------+
| 1        | Priya         | 250.00      |
| 2        | Alex          | 90.00       |
+----------+---------------+-------------+

The Tradeoffs

Denormalization is not a free performance upgrade — it trades one cost for another. Understanding both sides is what makes it a deliberate engineering decision rather than sloppy design.

In the example above, if a customer changes their name, you now must update it in both the customers table and every order row that duplicated it — miss one, and your data becomes inconsistent.

Keeping Data in Sync

If you choose to denormalize, you need a plan for keeping the duplicated data consistent. A few common approaches:

When to Denormalize

Denormalization should come after normalization, not instead of it. Start with a clean, normalized design, measure real performance problems, and only then denormalize the specific parts that need it.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Denormalization is the controlled, deliberate opposite of normalization: you accept some redundancy in exchange for faster reads on specific, expensive, frequently-run queries.

In this lesson, you learned what denormalization is, why it can be worth the tradeoff, and how to manage the risk of data getting out of sync. Next, you will explore the different types of relationships that can exist between tables.