MySQL + SQL · Lesson 30

Denormalization in DBMS

In this lesson: What denormalization is, why big apps use it, pros and cons vs normalization — with practical MySQL examples.

What is denormalization

Denormalization is deliberately adding some redundant data back into tables to make reading faster. It is the opposite of normalization. Where normalization splits data to remove duplication, denormalization accepts a little duplication to avoid slow joins on large, read-heavy databases.

💡 Normalization removes redundancy for correctness; denormalization adds some back for speed.

Why denormalize

A fully normalized database stores each fact once, but reading it often needs joins across many tables, which can be slow when data is large. Denormalization stores some data together so queries can read it directly, without joins. The goal is faster reads.

A simple example

Normalized: an orders table stores only customer_id, and you join to customers to get the name:

orders(id, customer_id)  JOIN  customers(id, name)

Denormalized: the orders table also stores the customer's name directly:

orders(id, customer_id, customer_name)

Now reading an order with its customer name needs no join — but the name is duplicated in every order.

The trade-off

Faster reads — no joins needed for common queries.
More storage — duplicated data takes extra space.
Risk of inconsistency — if a customer's name changes, every copy must be updated.

You trade update simplicity and storage for read speed. It only pays off when reads far outnumber writes.

Normalization vs denormalization

Point	Normalization	Denormalization
Goal	Remove redundancy	Speed up reads
Redundancy	Minimal	Some, on purpose
Joins	More needed	Fewer needed
Updates	Simple, safe	Must sync copies
Best for	Correctness, writes	Read-heavy performance

When to use it

Reads vastly outnumber writes — like reporting or analytics.
A normalized design is measurably too slow.
You can manage the duplicated data and keep copies in sync.

For most applications, start normalized. Denormalize selectively, only where it clearly helps performance.

Common mistakes

Denormalizing too early. Start normalized; optimise only when needed.
Forgetting to sync copies. Duplicated data must be updated everywhere.
Denormalizing write-heavy tables. It helps reads, not frequent writes.
Confusing it with poor design. Denormalization is deliberate, not accidental redundancy.

Summary

Denormalization adds some redundancy back for faster reads.
It avoids joins by storing data together, at the cost of duplication.
The trade-off is read speed vs storage and update complexity.
Use it only when reads dominate and normalization is too slow.
Start normalized; denormalize selectively as an optimisation.

Next, revise the opposite in normalization, or see worked steps in normalization examples.

Frequently Asked Questions

What is denormalization?

Denormalization is deliberately adding some redundant data back into tables to make reading faster. It is the opposite of normalization: you accept some duplicate data to avoid expensive joins, usually to speed up queries on large, read-heavy databases.

Why would you denormalize a database?

You denormalize to improve read performance. Normalized data often needs joins across many tables, which can be slow. By storing some data together (even if duplicated), queries run faster because they avoid those joins.

What is the difference between normalization and denormalization?

Normalization removes redundancy by splitting data into related tables, which is great for consistency and updates. Denormalization adds some redundancy back for faster reads. One optimises correctness, the other optimises speed.

What are the downsides of denormalization?

The main downsides are duplicate data, more storage, and the risk of inconsistency — if the same fact is stored twice, updating one copy but not the other causes errors. You must manage those updates carefully.

When should I denormalize?

Denormalize only when a normalized design is measurably too slow for reads, and reads vastly outnumber writes. For most applications, a normalized design is the right default; denormalize selectively as a performance optimisation.