Normalization means storing each piece of information in exactly one place: user data lives only in users, not copied into other tables. That prevents update anomalies, where a change lands in one place but not another. Denormalization deliberately copies data to make reads faster, and takes on the job of keeping the copies in sync.
Side by side#
Normalized: two tables, linked by user_id.
| id | username | |
|---|---|---|
| 1 | john_doe | john@example.com |
| 2 | jane_doe | jane@example.com |
| id | user_id (FK) | content | created_at |
|---|---|---|---|
| 1 | 1 | Hello, world! | 2024-01-01 10:00:00 |
| 2 | 1 | Second post | 2024-01-01 10:05:00 |
Denormalized: user fields copied onto every post.
| id | user_id | username | content | created_at | |
|---|---|---|---|---|---|
| 1 | 1 | john_doe | john@example.com | Hello, world! | 2024-01-01 10:00:00 |
| 2 | 1 | john_doe | john@example.com | Second post | 2024-01-01 10:05:00 |
In the denormalized version, if john_doe changes his username, every post he has ever written must be updated. Miss one and the data is inconsistent.
Default to normalized#
Start with a clean, normalized model and denormalize only when a specific read path needs it. Duplicated data wastes space and creates consistency problems that are usually harder to fix than the performance problem you were trying to avoid.
When denormalization makes sense#
Copying data pays off in a few specific situations.
- Analytics and reporting, where you aggregate data that rarely changes.
- Event logs and audit trails, where you want a snapshot of the data as it was at that moment.
- Heavily read-optimized systems like search indexes, where speed matters more than perfect consistency.
- Precomputed counters, like
posts.like_count, derived from a normalizedlikestable.
You can often get denormalized reads without denormalizing your database. Put a cache in front that holds a precomputed shape of the data (pre-joined rows, aggregates). The source of truth stays clean and normalized, and the cache makes reads fast.
Key takeaways#
- Normalized: each fact stored once, easy writes, no update anomalies.
- Denormalized: copies for fast reads, but every change must update every copy.
- Start normalized and denormalize a specific read path only when you need to.
- A cache or derived table can give you denormalized reads while the source of truth stays normalized.