Normalization

Normalization

Why This Matters

Think of normalization like organizing a filing cabinet. Without normalization, you might store customer information in every order file. If the customer's address changes, you'd have to update it in hundreds of files. With normalization, you store customer information once in a customer file, and orders just reference it. If the address changes, you update it once.

This matters because unnormalized databases have problems: data redundancy (same data stored multiple times), update anomalies (changing data requires updating multiple rows), and inconsistency (data can become inconsistent). Normalization eliminates these problems by organizing data into separate tables with clear relationships.

In interviews, when someone asks "How would you design a database schema?", they're testing whether you understand normalization. Do you know when to normalize? Do you understand the trade-offs? Most engineers don't. They either over-normalize (too many joins, slow queries) or under-normalize (data redundancy, inconsistency).

What Engineers Usually Get Wrong

Most engineers think "normalization is always good." But normalization has trade-offs. Normalized databases require more joins, which can slow down queries. For read-heavy systems, denormalization (storing redundant data) can improve performance. The key is balance—normalize to eliminate redundancy, but denormalize when performance requires it.

Engineers also don't understand that normalization is about eliminating redundancy, not just splitting tables. You can have multiple tables and still have redundancy if you're not normalizing correctly. Understanding normal forms (1NF, 2NF, 3NF) helps you identify and eliminate redundancy systematically.

How This Breaks Systems in the Real World

A service had an unnormalized database. Customer information was stored in every order row. When a customer's email changed, the team had to update hundreds of order rows. Sometimes they missed some, causing inconsistent data. Also, the database was larger than necessary due to redundancy. The fix? Normalize. Create a separate customers table, and orders reference it. Now customer updates happen in one place.

Another story: A service over-normalized their database. They split everything into tiny tables. A simple query required 10 joins. Queries were slow. The database became a bottleneck. The fix? Denormalize strategically. Combine related tables, or add redundant columns for frequently accessed data. This improved query performance significantly.

---

Why Normalize?

Problems without normalization:

• Data redundancy: Same data stored multiple times (wastes space, causes inconsistency)
• Update anomalies: Changing data requires updating multiple rows
• Insertion anomalies: Can't add data without other related data
• Deletion anomalies: Deleting data accidentally removes other data

Example of problems:

Orders Table (Unnormalized):
order_id | customer_name | customer_email | product_name | quantity | price
1        | John Doe      | john@email.com | Widget       | 2        | 10.00
1        | John Doe      | john@email.com | Gadget       | 1        | 20.00
2        | Jane Smith    | jane@email.com | Widget       | 3        | 10.00

Problems:
- Customer info repeated for each order item
- If John's email changes, must update multiple rows
- Can't add a customer without an order
- Deleting an order might lose customer information

---

Normal Forms

First Normal Form (1NF)

Rules:

• Each column contains atomic (indivisible) values
• Each row is unique
• No repeating groups or arrays

Example - Before 1NF:

Users Table:
id | name | phone_numbers
1  | John | 555-1234, 555-5678

After 1NF:

Users Table:
id | name
1  | John

Phone Numbers Table:
user_id | phone_number
1       | 555-1234
1       | 555-5678

Interview tip: JSON columns in modern databases can violate 1NF if not used carefully. Consider if the data should be in a separate table.

Second Normal Form (2NF)

Rules:

• Must be in 1NF
• All non-key attributes are fully functionally dependent on the primary key
• No partial dependencies (for composite keys)

Example - Violates 2NF:

Order Items Table:
order_id | product_id | product_name | quantity | price
1        | 101        | Widget       | 2        | 10.00
1        | 102        | Gadget       | 1        | 20.00

Problem: product_name depends only on product_id, not the full key (order_id, product_id)

After 2NF:

Order Items Table:
order_id | product_id | quantity | price

Products Table:
product_id | product_name
101        | Widget
102        | Gadget

Third Normal Form (3NF)

Rules:

• Must be in 2NF
• No transitive dependencies (non-key attributes don't depend on other non-key attributes)

Example - Violates 3NF:

Orders Table:
order_id | customer_id | customer_name | customer_email | total
1        | 100         | John Doe      | john@email.com | 30.00

Problem: customer_name and customer_email depend on customer_id, not order_id

After 3NF:

Orders Table:
order_id | customer_id | total
1        | 100         | 30.00

Customers Table:
customer_id | customer_name | customer_email
100         | John Doe      | john@email.com

Interview scenario: "Why is customer email in the orders table a problem?" Answer: It creates transitive dependency and update anomalies.

Boyce-Codd Normal Form (BCNF)

Rules:

• Must be in 3NF
• Every determinant is a candidate key

When needed: When you have overlapping candidate keys or multiple determinants.

Fourth Normal Form (4NF)

Rules:

• Must be in BCNF
• No multi-valued dependencies

Example: A table with independent multi-valued attributes should be split.

Fifth Normal Form (5NF)

Rules:

• Must be in 4NF
• No join dependencies

Rarely used in practice. Most databases stop at 3NF or BCNF.

---

When to Denormalize

While normalization reduces redundancy, sometimes denormalization improves performance:

Read-Heavy Workloads

Denormalized data can reduce JOIN operations:

-- Normalized (requires JOIN)
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- Denormalized (no JOIN needed)
SELECT name, email, order_count
FROM user_summary;  -- Pre-computed view

Reporting Databases

Data warehouses often use star/snowflake schemas (denormalized):

Fact Table (denormalized):
sale_id | date | customer_id | customer_name | product_id | product_name | amount

Caching Layers

Store computed/aggregated values:

-- Denormalize order count into users table
ALTER TABLE users ADD COLUMN order_count INT;
-- Update via trigger or application logic

Trade-off: Performance vs. data consistency and storage efficiency.

---

Best Practices

1. Start normalized: Begin with 3NF, then denormalize only when needed
2. Measure first: Profile queries before denormalizing
3. Document decisions: If you denormalize, document why and how to maintain consistency
4. Maintain consistency: Use triggers or application logic to keep denormalized data in sync
5. Consider materialized views: Use views for read optimization without denormalizing base tables

---

Interview Questions

1. Beginner Question

Q: What is database normalization, and why is it important?

A: Normalization is the process of organizing database tables to eliminate redundancy and dependency issues. It's important because:

1. Reduces redundancy: Saves storage space and prevents data inconsistency
2. Prevents anomalies: Update, insertion, and deletion anomalies
3. Improves data integrity: Easier to maintain consistent data
4. Simplifies maintenance: Changes to data only need to be made in one place

Example: Without normalization, if a customer's email is stored in 10 order records, updating it requires changing 10 rows. With normalization, it's stored once in the customers table.

2. Intermediate Question

Q: Explain the difference between 2NF and 3NF with an example.

A:

2NF: Eliminates partial dependencies (for composite keys). All non-key attributes must depend on the entire primary key.

3NF: Eliminates transitive dependencies. Non-key attributes must not depend on other non-key attributes.

Example:

-- Violates 2NF (if order_id, product_id is composite key)
Order Items:
order_id | product_id | product_name | quantity
-- product_name depends only on product_id, not full key

-- After 2NF:
Order Items: order_id | product_id | quantity
Products: product_id | product_name

-- Still violates 3NF:
Orders:
order_id | customer_id | customer_name | customer_email | total
-- customer_name and customer_email depend on customer_id, not order_id

-- After 3NF:
Orders: order_id | customer_id | total
Customers: customer_id | customer_name | customer_email

Key difference: 2NF addresses partial dependencies in composite keys; 3NF addresses transitive dependencies in single-key tables.

3. Senior-Level System Question

Q: Design a database schema for an e-commerce platform handling 10M products, 100M orders, and 1B order items. How would you balance normalization with performance?

A:

Strategy: Hybrid approach—normalize for writes, denormalize for reads

1. Core normalized schema (source of truth):

   -- Fully normalized for data integrity
   products (product_id, name, description, category_id, ...)
   categories (category_id, name, parent_id)
   orders (order_id, customer_id, created_at, status, ...)
   order_items (order_id, product_id, quantity, price, ...)
   customers (customer_id, name, email, ...)
   

2. Denormalized read-optimized views:

   -- Materialized view for product catalog (read-heavy)
   CREATE MATERIALIZED VIEW product_catalog AS
   SELECT 
     p.product_id,
     p.name,
     p.description,
     c.name as category_name,
     c.parent_name,
     p.price,
     p.stock
   FROM products p
   JOIN categories c ON p.category_id = c.id;
   
   -- Refresh periodically or on change
   

3. Caching layer for hot data:

   • Product details in Redis (frequently accessed products)
   • User shopping carts in Redis (temporary, high-read)
   • Search indexes (Elasticsearch) with denormalized product data

4. Partitioning strategy:

   -- Partition orders by date for query performance
   CREATE TABLE orders_2024_01 PARTITION OF orders
   FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
   

5. Write path (normalized):

   • All writes go to normalized tables
   • Triggers update denormalized views/cache
   • Ensures data consistency

6. Read path (optimized):

   • Check cache first
   • Use materialized views for analytics
   • Fall back to normalized tables for complex queries

Trade-offs:

• Storage: Denormalization increases storage (acceptable for read performance)
• Consistency: Eventual consistency between normalized and denormalized data
• Complexity: More moving parts (views, triggers, cache invalidation)

Monitoring:

• Track query performance on normalized vs. denormalized
• Monitor cache hit rates
• Alert on data inconsistencies

---

Failure Stories You'll Recognize

The Unnormalized Database: A service had an unnormalized database. Customer information was stored in every order row. When a customer's email changed, the team had to update hundreds of order rows. Sometimes they missed some, causing inconsistent data. Also, the database was larger than necessary due to redundancy. The fix? Normalize. Create a separate customers table, and orders reference it. Now customer updates happen in one place.

The Over-Normalization: A service over-normalized their database. They split everything into tiny tables. A simple query required 10 joins. Queries were slow. The database became a bottleneck. The fix? Denormalize strategically. Combine related tables, or add redundant columns for frequently accessed data. This improved query performance significantly.

The Denormalization Without Maintenance: A team denormalized their database for performance. They stored redundant data in multiple places. But they didn't have a process to keep it consistent. Over time, data became inconsistent. Users saw different values in different places. The fix? Either normalize again, or implement a process to keep denormalized data consistent (e.g., update all copies when data changes).

What Interviewers Are Really Testing

They want to hear you talk about normalization as a trade-off, not an absolute rule. Junior engineers say "always normalize to 3NF." Senior engineers say "normalize to eliminate redundancy and prevent anomalies. But denormalize when performance requires it. Start normalized, then denormalize strategically based on actual query patterns. Document denormalization decisions."

When they ask "How would you design a database schema?", they're testing:

• Do you understand normalization and its benefits?

• Do you know when to denormalize?

• Can you balance data integrity with performance?

• Normalization eliminates redundancy and prevents data anomalies (update, insertion, deletion)

• 1NF requires atomic values and unique rows—no repeating groups

• 2NF eliminates partial dependencies in composite keys

• 3NF eliminates transitive dependencies—most databases stop here

• Start normalized, then denormalize only when performance requires it

• Denormalization trades storage/consistency for read performance—measure before optimizing

• Use materialized views for read optimization without denormalizing base tables

• Document denormalization decisions and how to maintain consistency

• Hybrid approach works best—normalize for writes (data integrity), denormalize for reads (performance)

• Understand the trade-offs—normalization improves integrity but can hurt query performance

How InterviewCrafted Will Teach This

We'll teach this through production failures, not definitions. Instead of memorizing "normalization eliminates redundancy," you'll learn through scenarios like "why did our database become inconsistent when we updated customer information?"

You'll see how normalization affects data integrity, query performance, and system design. When an interviewer asks "how would you design a database schema?", you'll think about normalization, denormalization, and trade-offs—not just "normalize to 3NF."

• SQL Joins - Normalized schemas require joins to combine data. Understanding joins helps understand normalization trade-offs.
• Query Optimization - Normalization affects query performance (more joins vs. less redundancy). Understanding query optimization helps balance normalization.
• Indexing - Indexes can help mitigate normalization performance costs. Understanding indexing helps optimize normalized schemas.
• Partitioning - Partitioning strategies work with normalization. Understanding both helps design scalable database schemas.
• ACID Properties - Normalization helps maintain data consistency. Understanding ACID helps understand normalization benefits.