What is Relational Database Denormalization
- intentionally storing redundant data to make reads faster and reduce joins
- Violates any 1-5 NF for business use purpose
Why Denormalize
- Reduce expensive joins
- Speed up read-heavy workloads
- Precompute values (totals, statuses, names)
- Simplify reporting queries
- Improve performance for dashboards and analytics
Common Denormalization Techniques
1. Duplicate Lookup Data
- Instead of joining TaskStatus every time, store the status directly in the Subtask table.
Normalized
Task
| TaskId | TaskStatusCode |
|---|---|
| 10 | COMPLETED |
Subtask
| SubtaskId | TaskId | SubtaskName |
|---|---|---|
| 123 | 10 | Prepare Report |
Denormalized
Subtask (Denormalized)
| SubtaskId | TaskId | SubtaskName | TaskStatusCode |
|---|---|---|---|
| 123 | 10 | Prepare Report | COMPLETED |
2. Pre-Joined Tables (Materialized View / Indexed View)
- Store the result of a join physically.
SubtaskWithStatus (Materialized View)
| SubtaskId | SubtaskName | TaskId | TaskStatusCode |
|---|---|---|---|
| 123 | Prepare Report | 10 | COMPLETED |
3. Add Derived / Computed Columns
- Store values that could be calculated but are expensive to compute repeatedly.
Normalized
| OrderId | Quantity | UnitPrice |
|---|---|---|
| 1 | 3 | 100 |
Denormalized
| OrderId | Quantity | UnitPrice | TotalPrice |
|---|---|---|---|
| 1 | 3 | 100 | 300 |
4. Summary / Aggregate Tables
- Store precomputed totals to avoid scanning large tables.
DailySalesSummary
| Date | TotalSales |
|---|---|
| 2026-04-20 | 1,250,000 |
5. Embed Parent Attributes in Child Tables
- Useful when parent data rarely changes.
Customer (Normalized)
| CustomerId | Name |
|---|---|
| 1 | Rose |
Orders (Normalized)
| OrderId | CustomerId | Amount |
|---|---|---|
| 100 | 1 | 500 |
Orders (Denormalized)
| OrderId | CustomerId | CustomerName | Amount |
|---|---|---|---|
| 100 | 1 | Rose | 500 |
🧩 Summary Table
Pros and Cons of Denormalization
| Pros | Cons |
|---|---|
| Faster reads | Data duplication |
| Fewer joins | Harder to maintain consistency |
| Simpler queries | Risk of stale data |
| Better for analytics | More complex writes |