Normalization
Normalization is a long word for one short, sensible rule: write each fact down in EXACTLY ONE place. The moment you copy the same fact into many rows, you've planted a time bomb — change one copy, forget another, and now your database tells you two different things at once. Normalization is the habit of splitting a fat, repeating table into smaller tidy tables linked by id, so every fact has a single home. People talk about '1NF, 2NF, 3NF' (normal forms), but they're really three steps of the same idea: stop repeating data.
The mess: one fat table that repeats itself
Imagine a shop that keeps everything in a single Orders table: the item, the customer's name, and the customer's city, all in one row. Maya buys a book — row says 'Book, Maya, Pune'. Maya buys a pen — another row says 'Pen, Maya, Pune'. Maya buys ten more things — and 'Maya, Pune' is now written twelve times. Nothing is broken yet, but look at what you've built: the same fact ('Maya lives in Pune') copied a dozen times. That copying is the problem normalization exists to fix. The fat table feels simple — everything's in one place — but the duplication will bite you the first time anything changes.
Why copying a fact is dangerous (anomalies)
Say Maya moves to Mumbai. Now you have to hunt down EVERY order row with her name and change 'Pune' to 'Mumbai'. Miss even one, and your database insists Maya lives in two cities at the same time. Which is true? Nobody knows. That contradiction is called an UPDATE ANOMALY — the data disagreeing with itself because a fact was stored in many places. The duplication causes two more headaches. If Ali is a customer but hasn't ordered anything yet, there's NO row to hold his details — you can't record a customer without an order (an 'insertion anomaly'). And if you delete his last order, you accidentally erase the only record that Ali exists at all (a 'deletion anomaly'). All three problems share one root cause: a fact stored in more than one place.
The fix: split into linked tables
The cure is to give each KIND of thing its own table. Customer facts go in a Customers table — one row per customer, holding their name and city exactly once. Order facts go in an Orders table — one row per order, holding the item and just the customer's ID (a foreign key pointing back to Customers). Now 'Maya lives in Pune' is written down a single time. Maya moves? You change ONE row in Customers, and every order automatically reflects it, because the orders never stored a copy — they only stored her id. Ali with no orders? He gets a Customers row of his own, no problem. Delete his last order? His customer row is untouched. Every anomaly vanishes, because every fact now has exactly one home.
1NF, 2NF, 3NF — three steps of the same idea
Textbooks split normalization into 'normal forms' with scary names, but don't let them spook you — each is just a smaller, more specific version of 'don't repeat, give each fact one home'. • 1NF (First Normal Form): one value per cell. No cramming a whole list into a single box — instead of a 'phones' column holding '555-1, 555-2', give each phone its own row. Keep cells atomic. • 2NF: no column should depend on only PART of the key. (This matters when a table's id is made of two columns together.) If a fact really belongs to just one of them, move it to its own table. • 3NF: no column should depend on another non-key column. If you store a customer's city AND that city's pin-code in the same row, the pin-code really 'belongs to' the city, not the order — so pull cities into their own table. You rarely need to recite these. If you follow the gut rule — 'each fact in exactly one place' — you'll naturally land in third normal form without memorizing a thing.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| Normalize fully (each fact in one place) | Data can never contradict itself. Updates touch one row. No insertion or deletion surprises. Tidy, trustworthy, and smaller on disk. | To show an order WITH the customer's name, the database must combine tables (a 'join'). Lots of small joins can be a little slower than reading one fat row. |
| Denormalize on purpose (keep some copies) | Reading is fast — the data you need is already together in one row, no joins. Useful for huge read-heavy systems and reports. | You're back to copies that can disagree. You must carefully keep every copy in sync yourself, or the old anomalies return. Only do this deliberately, once you've measured a real speed need. |
Questions you might have
▸Why not just keep one big table? It seems simpler.
It looks simpler until something changes. One big table copies the same fact into many rows, and the day you update one copy and miss another, your data starts lying to you — two different cities for one person, say. Splitting into linked tables means each fact has a single home, so it can never contradict itself. The 'simple' big table is a trap that springs later.
▸Do I have to memorize 1NF, 2NF, and 3NF?
No. They're just three increasingly precise versions of one gut rule: store each fact in exactly one place and don't repeat it. If you split your data so that no fact is copied around, you'll naturally satisfy all three without ever reciting their definitions. The names are for exams; the instinct is what matters.
▸If splitting tables means I have to join them back together, isn't that slower?
Sometimes a tiny bit, yes — combining two tables is one extra step. But databases are extremely good at joins (especially with keys indexed), so it's usually fast and totally worth it for data you can trust. You'd only keep deliberate copies (denormalize) after measuring a real, proven speed problem — never as your starting point.
▸What exactly is an 'anomaly'?
It's a way the data can go wrong because a fact was stored in more than one place. Three flavors: an UPDATE anomaly (you change one copy and forget another, so the data disagrees), an INSERTION anomaly (you can't record a fact because there's no row to put it in), and a DELETION anomaly (deleting one thing accidentally erases another). Normalization removes all three by giving every fact one home.
▸Is normalization the same as the relational model?
They're close cousins. The relational model gives you the TOOLS — tables, primary keys, foreign keys. Normalization is the GOOD HABIT of using those tools well: splitting data so no fact repeats. You can have tables and still make a mess (one fat repeating table). Normalization is the discipline that keeps the tables tidy.
Best read after: The relational model