Database Normalization

Database Normalization

Normalization is the process of structuring a relational database to reduce data redundancy and improve data integrity. Each normal form adds a rule to eliminate a specific type of anomaly.

Why Normalize?

Without normalization, you get update anomalies:

• Insertion anomaly: can't add data without unrelated data
• Update anomaly: changing one fact requires updating many rows
• Deletion anomaly: deleting one record accidentally removes other facts

First Normal Form (1NF)

❌ Violates 1NF:

| student_id | name  | courses              |
|------------|-------|----------------------|
| 1          | Alice | Java, Spring, DSA    |

✅ 1NF:

| student_id | name  | course  |
|------------|-------|---------|
| 1          | Alice | Java    |
| 1          | Alice | Spring  |
| 1          | Alice | DSA     |

Second Normal Form (2NF)

Applies only to tables with composite keys.

❌ Violates 2NF (course_name depends only on course_id, not the full key):

| student_id | course_id | course_name | grade |

✅ 2NF (split into two tables):

courses:           | course_id | course_name |
enrollments:       | student_id | course_id | grade |

Third Normal Form (3NF)

❌ Violates 3NF (zip_code → city is a transitive dependency):

| employee_id | name | zip_code | city      |

✅ 3NF:

employees:  | employee_id | name | zip_code |
zip_codes:  | zip_code | city |

BCNF (Boyce-Codd Normal Form)

Rarely violated in practice; 3NF is sufficient for most applications.

Denormalization — When to Break the Rules

Normalization optimises for write integrity. For read performance, you sometimes denormalize intentionally:

• Store user_name on the orders table to avoid a JOIN on every read
• Pre-aggregate daily totals into a summary table
• Use materialized views for expensive reports

Denormalization is a deliberate trade-off: you accept some redundancy in exchange for faster reads.

Quick Reference

Interview Tips

1. Be able to identify a normalization violation in a table schema presented during the interview.
2. Know when not to normalize — read-heavy analytics systems, event stores, and document databases often intentionally denormalize.
3. The classic answer: "Normalize to 3NF, then selectively denormalize based on profiling."