Database normalization vs denormalization in HLD - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the main goal of normalization

What is the primary purpose of database normalization in system design?

A To combine all data into a single table for simplicity

B To increase the number of tables for faster queries

C To duplicate data across tables for backup

D To reduce data redundancy and improve data integrity

💡 Hint

Think about how normalization helps keep data consistent and organized.

Normalization organizes data to minimize duplication and ensure consistency.

❓ Architecture

intermediate

Choosing denormalization for read-heavy systems

In which scenario is denormalization most beneficial for a database design?

A When the system requires fast read queries with fewer joins

B When data consistency is the highest priority

C When the database size must be minimized

D When the system has very few users and low traffic

💡 Hint

Denormalization duplicates data to speed up reads.

Denormalization improves read performance by reducing the need for complex joins.

❓ scaling

advanced

Impact of normalization on write scalability

How does strict normalization affect the scalability of write operations in a high-traffic database?

A It can slow down writes due to multiple table updates and constraints

B It speeds up writes by reducing the number of tables involved

C It eliminates the need for transactions during writes

D It has no effect on write performance

💡 Hint

Consider how many tables and constraints a write must touch in normalized design.

Normalization requires updating multiple tables and enforcing constraints, which can slow writes.

❓ tradeoff

advanced

Tradeoffs between normalization and denormalization

Which of the following best describes a key tradeoff when choosing between normalization and denormalization?

A Normalization always improves performance; denormalization always reduces storage

B Normalization improves data integrity but may reduce read performance; denormalization improves read speed but risks data inconsistency

C Denormalization eliminates the need for backups; normalization requires frequent backups

D Normalization and denormalization have no impact on system complexity

💡 Hint

Think about the balance between data accuracy and query speed.

Normalization ensures data accuracy but can slow reads; denormalization speeds reads but risks inconsistency.

❓ estimation

expert

Estimating storage impact of denormalization

A normalized database stores 100GB of data. If denormalization duplicates 30% of the data across tables to speed up reads, approximately how much storage will the denormalized database require?

A 70GB

B 100GB

C 130GB

D 230GB

💡 Hint

Calculate original size plus 30% extra for duplicated data.

Denormalization adds 30% more data, so total storage is 100GB + 30GB = 130GB.

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