Database normalization is a way of organising tables so every fact is stored exactly once, in one place, so your data can never disagree with itself. It sounds like housekeeping. Skip it, and one typo can leave a single customer living at three different addresses.
Instead of reciting definitions, we'll follow one small online shop that started life as a giant spreadsheet. Each normal form shows up at the moment the shop needs it. At the end, we'll look at when breaking the rules on purpose is the smart move.
- Duplicated facts drift apart quietly and cause update, insert and delete anomalies.
- 1NF: one value per cell, no lists stuffed into a column.
- 2NF and 3NF: non-key columns depend on the whole key, and only on the key.
- BCNF: anything that determines another column must be a candidate key.
- Normalize by default, and denormalize deliberately from a clean source of truth.
- 0:00Intro
- 0:28Your database just lied to you
- 1:24Redundancy breeds three anomalies
- 2:18Codd's rule: every fact lives once
- 3:19One cell, one value
- 4:19Depend on the whole key
- 5:20Kill transitive dependencies
- 6:29Every determinant is a candidate key
- 7:35Normalizing an online shop
- 8:41Normalized data meets JOINs
- 9:43Analytics denormalizes on purpose
- 10:38Denormalize deliberately, not accidentally
- 11:43Where normalization goes wrong
- 12:40How to normalize any table
- 13:40Normalization in five lines
- 14:15Normalize by default, denormalize deliberately
The shop whose database lied about an address
Picture a small online shop running on one big orders table. Every row repeats the customer's name, address and product details. It feels convenient, because every answer sits in one place. Then a customer moves house. Someone updates the address on one order and misses the other two. Now the same person lives in three places at once.
Nothing complains. There's no error and no red alert, because from the database's point of view every copy is perfectly valid data. Shipping reads one address, billing reads another, and support sees a third. The parcel goes to the old house, and you pay for a reshipment, a refund and an angry ticket. Reports start disagreeing, and once people stop trusting the data, every decision slows down.
Update, insert and delete anomalies: the root problem
The shop's real problem is redundancy: the same fact stored in several rows. Redundancy doesn't cause just one bug. It causes three, which database people call anomalies. All three trace back to one root: a single fact living in many rows, or unrelated facts glued into one row.
The good news is that the cure is shared too. Fix the structure, and all three anomalies disappear together. That's the job normalization was invented to do.
- Update anomaly: change an address, miss a copy, and rows disagree.
- Insert anomaly: you can't record a new product until someone orders it.
- Delete anomaly: cancel a customer's only order and the customer disappears too.
What is database normalization? Codd's ladder
In the 1970s, Edgar Codd, the computer scientist behind the relational model, laid out a method for organising tables so redundancy has nowhere to hide. That's normalization, and it works like a ladder. Each rung is a normal form, each one assumes you've passed the rung below, and each removes a specific kind of redundancy.
First Normal Form is the ground floor: atomic cells. Second and Third Normal Form deal with dependencies, meaning which columns truly decide which. Boyce-Codd Normal Form is a stricter Third that closes a few edge cases. Our shop will climb them in order.
First Normal Form: one cell, one value
The shop's sheet has an items column holding comma-separated lists, the same move as a phone column holding two numbers. It looks tidy. But the database can't see inside that string. Finding who owns a number means slow string matching, deleting one item means rewriting text, and you can't enforce a format or foreign key on half a cell.
The fix is to give repeating values their own rows. In the phone example, Ana gets two rows in a phones table, one per number. In the shop, each item in an order gets its own row. Once every value stands alone, the database can index it, validate it and query it cleanly.
# Breaks 1NF: a list in one cell
customer | phones
Ana | 555-0101, 555-0199
# 1NF: one value per row
customer_phones(customer_id, phone)Second Normal Form: depend on the whole key
Splitting items gives the shop an order items table whose key is two columns together: order ID plus product ID, because one order can hold many products. Second Normal Form only matters with composite keys like this. Its rule: every non-key column must depend on the whole key, not part of it.
Quantity passes, since it means how many of this product in this order. Product name fails. It depends only on product ID, so it repeats in every order containing that product. That's a partial dependency. Move product details into a products table keyed by product ID alone, and renaming a product happens once, everywhere. Your test: cover up part of the key and ask whether the column still makes sense.
# key = (order_id, product_id)
order_items(order_id, product_id,
quantity, product_name)
# product_name needs only product_id
# fix: products(product_id, product_name)Third Normal Form: fixing the customer address for good
Third Normal Form says non-key columns depend on the key and nothing else, not on each other. The classic memory aid: the key, the whole key, and nothing but the key. The video's example is an employees table where employee ID decides department ID, which decides department name. The name reaches the key through a middleman, a transitive dependency, and repeats for every employee.
Our shop has the same chain: an order points to a customer, and the customer decides the address. So addresses move into a customers table, and orders just store the customer ID. The opening disaster is solved. The address lives in exactly one row, and one edit means every order, invoice and shipment sees the same truth.
- Result: four linked tables, orders, order items, products and customers.
- Add products before anyone buys them, and delete orders without losing customers.
BCNF: when 3NF still lets redundancy slip through
Boyce-Codd Normal Form adds one blunt rule: every determinant must be a candidate key. A determinant is any column or set of columns that decides another column's value. A candidate key is any column set that could uniquely identify a row. If knowing X always tells you Y, X had better be a key.
The classic case is a table of student, course and instructor where each instructor teaches exactly one course. Instructor determines course, but instructor alone isn't a key, so the table can pass 3NF and still repeat each instructor's course. Splitting instructors out fixes it. The catch: a BCNF split can spread one rule across two tables, making it harder to enforce.
How do you see a full order? JOINs and denormalizing
With data spread over four tables, you reassemble a full order at query time with joins: orders to order items by order ID, on to products by product ID, and to customers by customer ID. Relational databases are built for this, and joins on indexed keys are usually fast. Writes stay tiny and safe, one change touching one row.
Heavy reporting is where the bill arrives, since dashboards may join many large tables. That's why analytics warehouses often denormalize on purpose, copying data into wide, pre-joined tables. It's safe there because a controlled pipeline loads the data, not people editing rows, so copies refresh together. The golden pattern: keep normalized tables as the source of truth, derive fast copies like materialized views, summary tables and caches, and only after you've measured a real read bottleneck.
Common normalization mistakes and a playbook for any table
Three traps bring the anomalies back. Fake atomic values: a JSON blob or delimited string is really a hidden list in a cell. Over-normalizing: splitting beyond what the data needs until one screen takes a dozen joins, so stop at 3NF or BCNF without a real reason. Accidental copies: duplicating a customer's email for convenience with no sync plan. Store the ID and join instead.
For any table, the process runs on one question: what does this column really depend on? Make values atomic first, then name candidate keys and map each non-key column's determinant. Move problem dependencies into tables keyed by their determinant, and check that joining them rebuilds the original exactly. If you can't name the anomaly a rule prevents, you're not ready to break it.
- Step 1: split lists and packed fields into one value per cell.
- Step 2: identify candidate keys and map dependencies.
- Step 3: split, then verify the joins reproduce the original.
What to remember
- Duplicated facts drift apart silently, with no error to warn you.
- Every anomaly traces back to one fact stored in more than one place.
- Climb in order: 1NF atomic values, 2NF whole key, 3NF and BCNF nothing but keys.
- Normalized data is cheap to write and is reassembled with joins on read.
- Denormalize deliberately, from a normalized source you can always rebuild from.
Questions people ask
What is database normalization in simple terms?
It's organising tables so every fact is stored once, in one place. That way, updating a fact means changing one row, and copies can't drift out of sync.
What's the difference between 2NF and 3NF?
2NF removes partial dependencies, where a column depends on only part of a composite key. 3NF removes transitive dependencies, where a non-key column depends on another non-key column instead of the key.
Do I need to normalize all the way to BCNF?
Most everyday schemas are in great shape at 3NF. BCNF closes a few edge cases, but a BCNF split can spread one rule across two tables, so weigh that trade-off.
Doesn't normalization make queries slower because of joins?
Joins on indexed keys are usually fast, and relational databases are built for them. The cost shows up mainly in heavy reports joining many large tables, which is where deliberate denormalization helps.
When is it OK to denormalize a database?
When you've measured a real read bottleneck, and the redundant copy is derived from a normalized source of truth. Materialized views, summary tables, caches and warehouse tables are common ways to do it.
Is storing JSON in a column a normalization problem?
It can be. A JSON blob or delimited string looks like one value to the database but is often a hidden list, so treat it with the same care as a 1NF violation.
