Stop 06 · SQL DatabasesTopics · 5/14

Normalization

In short

Ever updated a phone number in one place and found the old one still showing somewhere else? That's duplicated data. Normalization is the habit that prevents it.

The process of structuring tables to minimize data duplication and avoid update anomalies, typically described through normal forms (1NF, 2NF, 3NF).

The blob sorts a heap of repeated paper slips into a tidy chest of drawers.

Think of it like this

Saved address, not retyped

Food apps save your address once and attach it to each order. If you retyped it every time, one typo would send your biryani to the wrong gate. Normalization works the same way: store each fact once, and point to it from everywhere else.

Watch it happen

Cleaning up an orders sheet

Raw sheet

1NF

2NF

3NF

1/5One sheet: order_id, customer, phone, items 'dosa, vada', city. The phone repeats on every order.

The big ideas

What you need to know

Duplicates cause bugs

The same fact in two places will drift apart. One copy gets updated and the other doesn't.

1NF: one value per cell

No comma lists inside a column. Each item gets its own row.

2NF and 3NF

Every column should depend on the key, the whole key, and nothing but the key.

Denormalize on purpose

Read-heavy pages sometimes copy data for speed. That's fine if you know why and keep the copies in sync.

See it in code

Walk through the code

sql

1-- Before: one table, phone repeated on every order2-- orders(id, customer_name, phone, items, city)3 4-- After: each fact stored once5CREATE TABLE customers (6  id SERIAL PRIMARY KEY, name TEXT, phone TEXT, city TEXT7);8CREATE TABLE orders (9  id SERIAL PRIMARY KEY,10  customer_id INT REFERENCES customers(id)11);12CREATE TABLE order_items (13  order_id INT REFERENCES orders(id),14  item TEXT,15  PRIMARY KEY (order_id, item)16);

1/4The old shape: change a phone and you must fix every order row.

This is 3NF for a small food app. Prices would go in a menu table, keyed by item.

Save yourself the pain

Common mistakes

Oops

Storing tags or items as 'a,b,c' in one column.

Do this instead

Give them their own table, one row each. Then you can search, count and join them.

Oops

Normalizing so far that one screen needs eight joins.

Do this instead

Stop at 3NF for most apps, and denormalize a hot read path only after you've measured it's slow.

Your mission

Fix a messy sheet

~25 min

Quick check

orders has columns id, customer_id and customer_phone. Which normal form does customer_phone break?

Go deeper

1Take a denormalized spreadsheet-style table and normalize it into related tables.
2Learn what problem each normal form (1NF, 2NF, 3NF) actually solves.
3Understand when deliberate denormalization is a reasonable performance trade-off.