Planned lessonMID-019-L02

Denormalization patterns

Lesson 2 of 6
Mid-level

On This Lesson 4 topics

In this lesson
Planned topics
Draft outline details
Practice

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

apply a denormalization and keep copies consistent.

Planned topics

Common patterns: a copied column (product_name in order_item as a historical snapshot), a counter (order_count), a summary table, a materialized view.
Historical snapshots (price at purchase) are not duplication: they are different facts.
Keep derived copies consistent with transactions, triggers, or periodic refresh; choose by how stale the data may be.
REFRESH MATERIALIZED VIEW CONCURRENTLY needs a unique index and avoids blocking readers.
Document who owns each copy and how it is repaired.

Draft outline details

Demonstration

sql

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day, sum(total_cents) AS revenue
FROM orders GROUP BY 1;
CREATE UNIQUE INDEX ON daily_sales (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

Check your understanding

Why does price_cents in order_item belong there even in a normalized design?

Show answer

It records the price at purchase time, which differs from the current price.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson