Course contentsShow
← Software Engineering
Lesson 866 of 1,93327. Database Design in PracticeFree lesson

Denormalization Patterns

Explore common denormalization techniques: computed columns, materialized views, and duplicated data.

Denormalization Patterns

What you'll learn: The specific techniques you can use to intentionally denormalize data for performance gains.

Now that you understand why strategic denormalization matters (from the previous lesson), let's explore how to actually do it. Denormalization patterns are proven approaches to storing redundant or derived data that would otherwise require expensive joins or calculations.

Common Denormalization Techniques

Computed Columns store the result of a calculation directly in the table. Instead of calculating a customer's total order value every time by joining orders, you might add a total_spent column to the customers table. Think of it like writing the answer on a worksheet instead of solving the math problem repeatedly.

Materialized Views are precomputed query results stored as physical tables. Unlike regular views (which are just saved queries), materialized views contain actual data that's periodically refreshed. Imagine a daily sales report that's generated once each morning and saved, rather than recalculating it every time someone asks.

Duplicated Data means copying information from one table into another to avoid joins. For example, storing the customer's name in the orders table even though it's already in customers. Yes, you're storing it twice—but reading one table is faster than joining two. It's like keeping a spare key at a friend's house: redundant, but convenient.

The Trade-off Pattern

Each pattern sacrifices write performance and storage space for read performance. You're making the database do extra work during inserts/updates so queries can be faster. Choose these patterns only when read performance matters significantly more than write performance.

Key Takeaway: Denormalization patterns—computed columns, materialized views, and duplicated data—are tactical techniques for trading storage and write complexity for faster reads, applied strategically where performance justifies the maintenance cost.