All topics
10 / 66
schema design

Normalization

Organize tables so each fact lives in one place — eliminating update anomalies and redundancy.

Normalization means storing each fact in exactly one place.

Don't copy your address into every order — store it once in the customer table and reference it.

Key Concepts

1
The reason is not tidiness. Duplicated data goes wrong, and it goes wrong silently.
2
What duplication actually costs. Put the customer's address on every order:
3
    order  customer  address
    101    Asha      12 MG Road
    102    Asha      12 MG Road
    103    Asha      12 MG Road
4
Asha moves. Now three rows must change together. Three problems follow, and they have names.
5
Update anomaly — update two of the three and the database now holds two different addresses for one person, with nothing to say which is right.
6
Insertion anomaly — a new customer with no orders yet has nowhere to live, because the address only exists as a column on an order.
7
Deletion anomaly — delete Asha's last order and her address is gone.
8
Move the address to a customers table and all three vanish. There is one row to change, it can exist without orders, and deleting an order cannot touch it.
customers
9
The normal forms, as three questions.1NF — is every value atomic? No lists in a field. tags = "java,sql,aws" breaks it, because you cannot index, join or constrain a substring. One row per tag instead.
tags = "java,sql,aws"
10
2NF — does every column depend on the whole key? Only an issue with a composite key. In a table keyed by (order_id, product_id), a product_name column depends on product_id alone — half the key. So it belongs in products.
(order_id, product_id)product_nameproduct_idproducts
11
3NF — does every column depend only on the key? In an orders table, country_calling_code depends on country, which is not the key. It has hitched a ride on another non-key column. Move it out.
orderscountry_calling_codecountry
12
A useful shorthand for 3NF: every column depends on the key, the whole key, and nothing but the key.
13
Where to stop. 3NF, or Boyce-Codd if a table has overlapping candidate keys. Past that the forms address rare situations and cost joins for little benefit.
14
What it costs. Facts now live in separate tables, so answering a question means joining them back together. That is the price, and it is the reason denormalization exists.
15
The position worth stating: normalise by default because it makes inconsistency structurally impossible, then denormalise a specific place once you have measured that reads there are too slow.
16
What the interviewer is probing.1. "What does third normal form actually require?" Probing: precision. Stalls: "No duplication." Moves up: every non-key column depends on the key, the whole key and nothing but the key — so no partial or transitive dependencies.
17
2. "What problem does normalisation solve?" Probing: the purpose. Stalls: "Saves space." Moves up: update anomalies — one fact stored once means it cannot become inconsistent, which matters more than the storage.
18
3. "When would you stop at second normal form?" Probing: judgement. Stalls: "Never." *Moves up:* rarely by choice; denormalisation is a deliberate later decision for read performance, not a reason to normalise incompletely.
19
4. "What is the cost of a highly normalised schema?" Probing: the trade. Stalls: "None." Moves up: more joins per query, which at scale is what drives selective denormalisation.
Use cases:Normalization3NFSchema Design