Schema Design & Normalization

Schema design is the most consequential decision in a relational database. A good schema prevents anomalies: inserting a row should not require duplicating data, and deleting a row should not accidentally destroy unrelated information.

Normalization is the process of organizing tables to eliminate redundancy. First Normal Form (1NF) requires atomic values — no arrays in a cell. Second Normal Form (2NF) removes partial dependencies — every non-key column must depend on the whole primary key. Third Normal Form (3NF) removes transitive dependencies — non-key columns should not depend on other non-key columns. In practice, most application schemas target 3NF, then strategically denormalize hot read paths for performance.

Before

Denormalized (redundant data)

1orders2┌────────┬──────────────┬───────────────────┬───────────┐3│ id     │ customer_name│ customer_email     │ product   │4├────────┼──────────────┼───────────────────┼───────────┤5│ 1      │ Alice        │ alice@example.com  │ Laptop    │6│ 2      │ Alice        │ alice@example.com  │ Mouse     │7└────────┴──────────────┴───────────────────┴───────────┘

After

Normalized (3NF)

1customers              orders2┌────┬───────┬──────┐   ┌────┬─────────────┬────────┐3│ id │ name  │email │   │ id │ customer_id │product │4├────┼───────┼──────┤   ├────┼─────────────┼────────┤5│ 1  │ Alice │alice@│   │ 1  │ 1           │ Laptop │6└────┴───────┴──────┘   │ 2  │ 1           │ Mouse  │7                        └────┴─────────────┴────────┘

Check your understanding

What anomaly does normalization mainly prevent?Show answerHide answer
Answer
Redundant data that causes update/insert/delete anomalies — e.g. changing an email in one row but not another.
What does 3NF require beyond 2NF?Show answerHide answer
Answer
Non-key columns should not depend on other non-key columns (no transitive dependencies).
When is deliberate denormalization justified?Show answerHide answer
Answer
When measured hot read paths need it and you accept the update cost — not by default.