Optimizing Data Models: Normalization vs. Denormalization

Hello! Welcome back to our course on system design.

In our last lesson, we explored how to make our data storage systems fault-tolerant and scalable by using replication. We saw that by creating copies of our data, we can handle server failures and scale out our ability to serve read requests.

Today, we're going to zoom in and look at the data inside the database. The way we structure our data has a massive impact on our system's performance, maintainability, and cost.

Our learning outcome for this lesson is to choose between normalized and denormalized data models based on access patterns. We will explore two fundamental approaches to data modeling, understand their trade-offs, and learn a framework for deciding which is right for a given problem.

A Quick Recap

In previous lessons, we've discussed:

• Partitioning (Sharding): Splitting a large database into smaller, more manageable pieces.
• Replication: Creating copies of data to ensure availability and improve read performance.

Now, we'll decide how to organize the tables and columns within each partition or replica.

---

1. The Two Philosophies of Data Modeling

Imagine you are designing a complex product. You have two choices:

1. A Modular Design: You could create a library of standardized, reusable components (e.g., screws, buttons, displays). Each component is designed and manufactured independently. To build the final product, you assemble these standard parts. This makes it easy to update a single component (like a better button) and use it everywhere.
2. An Integrated Design: You could create a custom, all-in-one part specifically for this product (e.g., a unibody frame for a high-performance car). This part is not reusable and is harder to change, but it's incredibly efficient and fast for its specific purpose.

In database design, we face a similar choice between Normalization (the modular approach) and Denormalization (the integrated approach).

Let's watch a video that introduces these concepts using a simple, real-life analogy.

The video gives us a great starting point. Now, let's formalize these ideas.

2. Normalization: The Source of Truth

Normalization is the process of organizing data in a database to minimize redundancy (duplication) and improve data integrity. The goal is to ensure that each piece of data is stored in exactly one place.

Think of the "three brothers" from the video keeping separate, specialized registers. This is the essence of normalization.

Key Characteristics:

• Data is split into multiple tables: Each table represents a single entity (e.g., Students, Courses, Instructors).
• Relationships are defined by keys: Tables are linked using foreign keys.
• Data integrity is high: When you need to update a piece of information (like an instructor's email), you only have to change it in one place. This prevents inconsistencies.
• Optimized for writes: Adding or updating data is fast and efficient because you are touching small, specific tables.

The Downside:

To get a complete picture, you often need to combine data from multiple tables using JOIN operations. For complex queries involving many tables, these joins can be slow and computationally expensive.

3. Denormalization: Built for Speed

Denormalization is the process of intentionally adding redundant data to a database to improve read performance. Instead of splitting data, you combine it.

Think of the "bigger register" the brothers created. It had all the information in one place, making it fast to find the customers they were looking for, even though some information was duplicated or left blank.

Key Characteristics:

• Fewer tables, wider tables: Data that would be in separate tables in a normalized model is combined into one.
• Redundancy is intentional: The same piece of data (e.g., a product name) may be stored in multiple rows.
• Optimized for reads: Queries are faster because they don't need to perform complex joins. You can often get all the data you need from a single table.

The Downside:

• Slower writes and updates: When you update a piece of data, you have to find and change every copy. This is slow and creates a risk of inconsistency if a copy is missed.
• Increased storage: Storing duplicate data takes up more space.

4. The Core Trade-Off: Access Patterns are Key

The decision between normalization and denormalization boils down to a fundamental trade-off driven by your system's access patterns: how your application reads and writes data.

• Write-Heavy Systems (OLTP): Applications with frequent inserts, updates, and deletes (e.g., e-commerce order processing, banking systems, inventory control) benefit from normalization. The priority is data consistency and efficient writes.
• Read-Heavy Systems (OLAP): Applications where the primary goal is fast data retrieval for analysis and reporting (e.g., analytics dashboards, news feeds, recommendation engines) benefit from denormalization. The priority is query speed.

This table provides a concise summary of the trade-offs.

To make this even more concrete, the same article includes a benchmark showing the performance difference. A query on a normalized schema required multiple JOINs and took 34ms. The same query on a denormalized schema (with proper indexing) took only 17.2ms—a 2x speedup for the read operation. This is the performance gain that denormalization aims to achieve.

5. A Framework for Making the Decision

So, how do you apply this in a system design interview? You need a structured way to justify your choice.

The Golden Rule

A great rule of thumb comes from a Meta Staff Engineer. The advice is to always start with a normalized model and only denormalize when you identify a clear performance bottleneck.

A Quantitative Framework

Starting normalized is safe, but a great system designer can anticipate when denormalization will be necessary. To do this, you analyze the requirements and access patterns.

This article from Pure Storage provides an excellent set of quantitative questions to guide your decision.

Let's turn those points into a practical checklist:

1. What are the query latency requirements?
   • If reads must be extremely fast (e.g., sub-10ms for a real-time feed), denormalization is a strong candidate.
2. What is the write-to-read ratio?
   • If the system has many more reads than writes (e.g., 100 reads for every 1 write), denormalization is likely beneficial.
   • If writes are frequent (e.g., 1 write for every 10 reads), normalization is safer to maintain consistency.
3. How often does the data change?
   • Data that is written once and rarely updated (e.g., an event log) is a great candidate for denormalization.
   • Data that changes frequently (e.g., a user's current location) is better off normalized.
4. What are the storage costs?
   • Denormalization increases storage. In a cloud environment, this can directly impact cost. Is the performance gain worth the extra storage expense?
5. Are there compliance or regulatory requirements?
   • Regulations like GDPR include a "right to be forgotten." Deleting a user's data is much simpler and safer in a normalized model where their information exists in a single, known location.

6. Hybrid Approaches: The Best of Both Worlds

You don't have to choose one or the other for your entire system. A very common and powerful pattern is to use a hybrid approach:

• Maintain a normalized database as your primary data store. This is your "source of truth" and handles all write operations, ensuring data integrity.
• Create denormalized read replicas or caches for specific, performance-critical read queries.

For example, when a user posts on a social network, the write goes to a normalized database. A separate process reads from this database and creates denormalized "feed" objects for that user's followers, which are stored in a fast cache. This way, you get the write safety of normalization and the read speed of denormalization.

---

Conclusion

You now have a framework for one of the most fundamental decisions in data storage design. The choice is not about which model is "better," but which is more appropriate for the specific job at hand.

Key Takeaways:

• Normalization minimizes redundancy to ensure data integrity and optimize for write-heavy workloads (OLTP systems). Its cost is slower reads due to joins.
• Denormalization intentionally adds redundancy to minimize joins and optimize for read-heavy workloads (OLAP systems). Its cost is slower writes, increased storage, and the risk of inconsistency.
• The decision is driven by access patterns. Analyze the write-to-read ratio, latency requirements, and data change frequency.
• A robust practical strategy is to start with a normalized model and introduce denormalization strategically (often in a separate read replica or cache) to solve specific performance problems.

Preview of the Next Lesson:

We've learned why and when to denormalize. In our next lesson, we will dive deeper into the how. We will learn to design denormalization strategies to optimize read-heavy workloads, exploring specific techniques like pre-calculating aggregates and embedding data to build high-performance systems.