When to perform Denormalization ?

Denormalize when the cost of maintaining normalized data becomes more expensive than the cost of storing and maintaining duplicated data.

The key is: don't denormalize because a schema "looks too normalized." Denormalize because you have measured a workload problem.

A practical decision rule

Start with normalized tables:

Customer
--------
id
name

Order
-----
id
customer_id
total_amount
created_at

Suppose your most frequent query is:

SELECT o.id, c.name, o.total_amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > ...;

If this is fast enough → stay normalized.

If profiling/load testing shows that this join is a significant bottleneck, and you've already considered:

1. Proper indexes

2. Better query/query plan

3. Fetching only required columns (beware when ORM is being used)

4. Caching (given that a proper cache invalidation mechanism is in place)

5. Read replicas (replication lag must be handled in case of sync replication)

6. Query/result optimization (e.g., paginated APIs or other batching mechanisms)

then denormalization becomes worth considering.

For example:

Order
-----
id
customer_id
customer_name       <-- duplicated
total_amount
created_at

Now the common query doesn't need the Customer table.

---

Common situations where denormalization makes sense

1. Read-heavy workloads

For example, an e-commerce product page repeatedly needs:

Product
Product price
Brand name
Category name
Rating
Review count

If constructing this view requires many joins and happens millions of times, you might maintain a read-optimized representation:

ProductSummary
--------------
product_id
product_name
brand_name
category_name
rating
review_count

You trade write complexity for read performance.

---

2. Expensive aggregations

Imagine:

SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

If this calculation is extremely frequent and expensive, you might maintain:

Customer
--------
id
name
total_order_amount

When an order is created:

total_order_amount += order.amount

Now:

SELECT total_order_amount
FROM customer
WHERE id = ?;

is cheap.

But you've introduced a consistency problem:

What happens if the order is inserted successfully but updating total_order_amount fails?

That's the price of denormalization.

---

3. Reporting / analytics

OLTP schema:

Order
Customer
Product
Payment
Address
...

Great for transactional integrity.

But analytical queries might repeatedly join 8–15 tables.

A data warehouse/star schema or precomputed analytical tables can be much more appropriate.

---

4. Avoiding extremely expensive access patterns

Sometimes the application's natural access pattern doesn't match the normalized relational model.

For example, an API always returns:

{
  "orderId": 123,
  "customerName": "John",
  "shippingAddress": "...",
  "items": [...]
}

If assembling this requires many queries/joins and is a proven bottleneck, maintaining a read model specifically for this access pattern can be sensible.

This is essentially the idea behind CQRS/read models.

---

The important distinction

Don't think:

"My database has too many tables → denormalize."

Think:

"This particular access pattern is expensive → can I deliberately duplicate data to make this access pattern cheaper?"

That's a much better mental model.

A useful progression

Normalized schema
       ↓
Measure actual workload
       ↓
Find bottleneck
       ↓
Optimize query/indexes
       ↓
Check execution plan
       ↓
Consider caching
       ↓
Still too slow?
       ↓
Denormalize selectively

And one more important point given your earlier question about over-normalization:

API load testing is indeed useful for discovering whether your database design/query workload is causing a performance problem, but load testing alone doesn't tell you "the DB is over-normalized." You need database metrics and query execution plans to identify why the workload is slow.

The real target isn't normalization level; it's measured workload + access pattern + performance requirement.