Course contentsShow
← Computer Science
Lesson 1649 of 2,87237. Database Design and NormalizationFree lesson

Common Denormalization Patterns

Learning standard denormalization techniques: precomputed aggregates, copied columns, and materialized views.

Common Denormalization Patterns

You'll learn the three most widely-used denormalization techniques that trade redundancy for faster query performance.

Why Denormalize?

After normalizing a database to eliminate redundancy, you sometimes need to strategically reintroduce redundancy for performance. Three patterns dominate real-world systems:

1. Precomputed Aggregates

Instead of running COUNT, SUM, or AVG on millions of rows every query, store the result directly. For example, rather than counting a user's total orders each time, keep a total_orders column in the users table that updates whenever an order is added.

Think of it like a running scoreboard instead of recalculating points after every game.

2. Copied Columns

Copy frequently-accessed columns from one table into another to avoid joins. If you often need a customer's name when querying orders, store customer_name directly in the orders table instead of joining to the customers table every time.

Like keeping a contact's phone number in your recent calls list, even though it's already in your contacts.

3. Materialized Views

A materialized view is a precomputed query result stored as a physical table. Unlike a regular view (which runs the query each time), a materialized view snapshots the result. It's periodically refreshed to stay reasonably current while serving fast reads.

Imagine a daily newspaper instead of checking news sources every second—slightly stale but instantly available.

The Trade-Off

All three patterns speed up reads but require extra storage and update logic. You must maintain consistency: when source data changes, you must update or refresh the denormalized copies.

Key Takeaway: Precomputed aggregates, copied columns, and materialized views are standard denormalization patterns that sacrifice write simplicity and storage efficiency for dramatic read performance gains.