Database normalization vs denormalization in HLD - Design Approaches Compared

Design: Database Design: Normalization vs Denormalization

Focus on database schema design choices between normalization and denormalization. Exclude specific database engine tuning or hardware scaling.

Functional Requirements

FR1: Design a database schema that supports efficient data storage and retrieval

FR2: Ensure data consistency and minimize redundancy

FR3: Support fast read queries for reporting and analytics

FR4: Allow easy updates and inserts without data anomalies

Non-Functional Requirements

NFR1: Handle up to 1 million records

NFR2: Read latency under 200ms for common queries

NFR3: Availability of 99.9% uptime

NFR4: Support concurrent read and write operations

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Relational database management system (RDBMS)

Tables and relationships (foreign keys)

Indexes and materialized views

Caching layers for read optimization

Design Patterns

Normalization forms (1NF, 2NF, 3NF)

Denormalization techniques (pre-joined tables, redundant columns)

Use of indexes and query optimization

Eventual consistency models for denormalized data

Reference Architecture

  +-------------------+       +-------------------+
  |   Normalized DB   |       |  Denormalized DB  |
  |  (3NF schema)     |       |  (redundant data) |
  +---------+---------+       +---------+---------+
            |                           |
            |                           |
     +------+-------+           +-------+-------+
     | Application  |           | Application   |
     |  Server      |           |  Server       |
     +--------------+           +---------------+

Components

Normalized Database

Relational DB (e.g., PostgreSQL, MySQL)

Store data in multiple related tables to reduce redundancy and maintain consistency

Denormalized Database

Relational DB or NoSQL

Store data with some redundancy to optimize read performance and simplify queries

Application Server

Any backend framework

Handles business logic, queries database, and serves client requests

Request Flow

1. Client sends query to application server

2. Application server decides to query normalized or denormalized schema based on query type

3. For normalized DB: joins multiple tables to fetch consistent data

4. For denormalized DB: fetches pre-joined or redundant data for faster reads

5. Application server processes data and returns response to client

6. For writes, application updates normalized tables and propagates changes to denormalized tables if used

Database Schema

Entities: - User (UserID PK, Name, Email) - Order (OrderID PK, UserID FK, OrderDate) - Product (ProductID PK, Name, Price) - OrderItem (OrderItemID PK, OrderID FK, ProductID FK, Quantity) Normalized relationships: - User 1:N Order - Order 1:N OrderItem - Product 1:N OrderItem Denormalized example: - OrderSummary (OrderID PK, UserName, OrderDate, TotalAmount) with redundant UserName and pre-calculated TotalAmount

Scaling Discussion

Bottlenecks

Normalized DB can have slow read queries due to multiple joins

Denormalized DB can have data inconsistency if updates are not properly synchronized

Write operations can be slower in denormalized DB due to multiple updates

Large data volume can increase query latency and storage costs

Solutions

Use caching layers (e.g., Redis) to speed up frequent read queries

Implement background jobs to update denormalized tables asynchronously

Use database partitioning and indexing to improve query performance

Adopt eventual consistency where strict consistency is not critical

Monitor and optimize slow queries regularly

Interview Tips

Time: 10 minutes to clarify requirements and constraints, 20 minutes to design schema and explain trade-offs, 10 minutes to discuss scaling and optimizations, 5 minutes for questions

Explain difference between normalization and denormalization clearly

Discuss trade-offs: consistency vs performance

Show understanding of query patterns and how they influence design

Mention real-world examples where each approach fits best

Highlight scaling challenges and practical solutions

Practice

1. What is the main goal of database normalization?

easy

A. To reduce data duplication and improve data accuracy

B. To combine tables for faster data retrieval

C. To increase storage space usage

D. To make queries more complex

Solution

1. Step 1: Understand normalization purpose

   Normalization organizes data to minimize duplication and maintain consistency.

2. Step 2: Compare with other options

   Combining tables and increasing storage are characteristics of denormalization, not normalization.

3. Final Answer:

   To reduce data duplication and improve data accuracy -> Option A

4. Quick Check:

   Normalization = reduce duplication [OK]

Hint: Normalization means less duplication, more accuracy [OK]

Common Mistakes:

• Confusing normalization with denormalization
• Thinking normalization increases storage
• Believing normalization makes queries faster

2. Which of the following is a correct statement about denormalization?

easy

A. It always reduces data redundancy

B. It combines tables to speed up read queries

C. It enforces strict data integrity rules

D. It splits data into many small tables

Solution

1. Step 1: Identify denormalization effect

   Denormalization merges tables to reduce joins and speed up reads.

2. Step 2: Eliminate incorrect options

   Reducing redundancy and splitting tables are normalization traits; strict integrity is also normalization.

3. Final Answer:

   It combines tables to speed up read queries -> Option B

4. Quick Check:

   Denormalization = combine tables for speed [OK]

Hint: Denormalization speeds reads by merging tables [OK]

Common Mistakes:

• Mixing up normalization and denormalization goals
• Assuming denormalization reduces redundancy
• Thinking denormalization splits tables

3. Consider a database where customer and order data are stored in separate tables (normalized). If we denormalize by combining them into one table, what is the likely effect on query performance?

medium

A. Write queries become faster, read queries become slower

B. Both read and write queries become slower

C. No change in query performance

D. Read queries become faster, write queries may slow down

Solution

1. Step 1: Analyze denormalization impact on reads

   Combining tables reduces joins, so read queries speed up.

2. Step 2: Analyze impact on writes

   More data in one table means writes may take longer due to larger rows and possible duplication.

3. Final Answer:

   Read queries become faster, write queries may slow down -> Option D

4. Quick Check:

   Denormalization = faster reads, slower writes [OK]

Hint: Denormalize to speed reads, but writes may slow [OK]

Common Mistakes:

• Assuming writes always get faster
• Ignoring join cost in normalized reads
• Believing no performance change occurs

4. A developer denormalized a database by duplicating user address data in multiple tables but forgot to update all copies when the address changed. What problem does this cause?

medium

A. Data inconsistency due to redundant copies

B. Faster query performance without side effects

C. Reduced storage space usage

D. Improved data integrity

Solution

1. Step 1: Understand denormalization risk

   Duplicating data can cause inconsistencies if updates are not synchronized.

2. Step 2: Evaluate consequences

   Failing to update all copies leads to conflicting data, harming accuracy.

3. Final Answer:

   Data inconsistency due to redundant copies -> Option A

4. Quick Check:

   Denormalization risk = inconsistency if not managed [OK]

Hint: Duplicated data needs careful updates to avoid inconsistency [OK]

Common Mistakes:

• Assuming denormalization always improves integrity
• Ignoring update complexity in denormalized data
• Thinking denormalization reduces storage

5. You are designing a high-traffic e-commerce system. You want to optimize for fast product search and order history retrieval. Which approach balances normalization and denormalization best?

hard

A. Normalize all data strictly to avoid any duplication

B. Normalize product data and denormalize order history

C. Denormalize product data for fast search, normalize order history for accuracy

D. Denormalize all tables to speed up all queries

Solution

1. Step 1: Identify system needs

   Fast product search benefits from denormalization to reduce joins and speed reads.

2. Step 2: Consider order history requirements

   Order history needs accuracy and consistency, so normalization is better.

3. Step 3: Choose balanced approach

   Denormalize product data for speed, normalize order data for integrity.

4. Final Answer:

   Denormalize product data for fast search, normalize order history for accuracy -> Option C

5. Quick Check:

   Balance = denormalize for speed, normalize for accuracy [OK]

Hint: Denormalize for speed, normalize for accuracy in critical data [OK]

Common Mistakes:

• Denormalizing everything causing data errors
• Normalizing all data causing slow reads
• Ignoring trade-offs between speed and accuracy