Many databases start life as a spreadsheet: one wide sheet where every row repeats the customer's name and city, the product's name and price, and anything else that seemed useful. It works for a while. Then a customer moves city and you have to change it in forty rows. You miss one, and now the database says she lives in two places at once.
Normalization is a set of rules for organising tables so each fact is stored in exactly one place. Data that belongs together lives in its own table, and tables are connected with keys. The rules are called normal forms, and the first three, 1NF, 2NF and 3NF, cover what almost every application needs.
In this lesson you'll see the problems repeated data causes, apply the three normal forms to a messy order sheet step by step, and learn when it's reasonable to break the rules on purpose.
The problem: repeated data
Here's a flat "orders sheet", the kind you'd get from a spreadsheet export:
| order_id | customer | city | products | total |
|---|---|---|---|---|
| 1 | Ana | Lisbon | Backpack 34.99 | 34.99 |
| 3 | Ana | Lisbon | Pen 1.20 x10 | 12.00 |
| 2 | Ben | Leeds | Notebook 3.50 x5 | 17.50 |
Three kinds of problem, called anomalies, appear:
- Update anomaly: Ana's city is stored in many rows. Change one and miss another, and the data contradicts itself.
- Insert anomaly: you can't record a new product until someone orders it, because products only exist inside orders.
- Delete anomaly: delete Ben's only order and you lose the fact that Ben exists at all.
First normal form (1NF)
A table is in 1NF when:
- Every column holds a single value: no lists like "Pen, Notebook" and no packed values like "Pen 1.20 x10".
- There are no repeating groups of columns such as
product1,product2,product3. - Each row can be identified by a primary key.
To fix the sheet, give each product in an order its own row with separate product, price and quantity columns. Single values can be searched, sorted, totalled and checked with constraints; packed text can't.
Second and third normal form
2NF applies when a table's key has more than one column. Every other column must depend on the whole key, not just part of it. In an order_lines table keyed by (order_id, product_id), quantity depends on both, but product_name and price depend only on product_id, so they move to a products table.
3NF says non-key columns must depend on the key directly, not through another column. In an orders table, city depends on the customer, not on the order, so it moves to customers, and the order keeps just customer_id.
A popular summary: every non-key column must depend on "the key, the whole key, and nothing but the key".
The normalised design
Applying the three forms to the sheet gives four tables:
- customers (id, name, city)
- products (id, name, price)
- orders (id, customer_id, order_date)
- order_lines (order_id, product_id, quantity, unit_price)
Each fact now lives in one place. Ana's city is stored once. A product can exist before anyone orders it. Deleting an order leaves the customer untouched. And whenever you want the flat view back for a report, a query with joins, saved as a view if you like, rebuilds it on demand.
Normalization Syntax
Normal form checklist
1NF one value per cell, no column1/column2/column3, has a primary key
2NF 1NF + no column depends on only part of a composite key
3NF 2NF + no column depends on another non-key column
Ask of every column: does it describe the key, the whole key,
and nothing but the key?Normalization Summary
- Normalization stores each fact in one place to prevent update, insert and delete anomalies.
- 1NF: single values per column, no repeating groups, a primary key.
- 2NF: every non-key column depends on the whole of a composite key.
- 3NF: non-key columns depend only on the key, not on other non-key columns.
- Joins and views rebuild flat reports; denormalise deliberately and only for a measured reason.
Normalization Examples
Each example shows the code, the output it produces and what each part does.
Rebuilding the flat view with joins
SELECT o.id AS order_id,
c.name AS customer,
c.city,
p.name AS product,
p.price,
o.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
ORDER BY o.id;order_id | customer | city | product | price | quantity
---------+----------+--------+--------------+-------+---------
1 | Ana | Lisbon | Backpack | 34.99 | 1
2 | Ben | Leeds | Notebook | 3.5 | 5
3 | Ana | Lisbon | Pen | 1.2 | 10
4 | Dev | Leeds | Desk lamp | 24.5 | 1
5 | Chloe | Lyon | Water bottle | 12 | 2
6 | Ben | Leeds | Water bottle | 12 | 1Line-by-line explanation
- Lines 2 to 3Ana's name and city appear in two rows of the result, but they're stored only once, in customers.
- Lines 8 to 9The joins put the pieces back together whenever a report needs them.
Fixing an update anomaly
-- flat sheet: city repeated in every order row
UPDATE order_sheet SET city = 'Porto' WHERE customer = 'Ana';
-- normalised: one row to change
UPDATE customers SET city = 'Porto' WHERE id = 1;order_sheet: 2 rows updated (and any typo like 'Ana ' is missed)
customers: 1 row updated, every order sees the new cityLine-by-line explanation
- Line 2In the flat design, every copy must be found and changed, and any copy you miss creates contradictory data.
- Line 5In the normalised design, the city is a single fact about one customer.
Splitting a repeating group
-- before (not 1NF)
CREATE TABLE courses_flat (
student TEXT, course1 TEXT, course2 TEXT, course3 TEXT
);
-- after (1NF and beyond)
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
student_id INTEGER REFERENCES students(id),
course_id INTEGER REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);Before: a fourth course needs a new column, and "who takes SQL?"
means searching three columns.
After: any number of courses per student, and one simple join
answers "who takes SQL?".Line-by-line explanation
- Lines 2 to 4course1, course2 and course3 are a repeating group.
- Lines 7 to 8Students and courses each get their own table.
- Lines 9 to 13A junction table records which student takes which course, as in the relationships lesson.
Common Normalization Mistakes
Packing several values into one column
INSERT INTO orders (id, products) VALUES (7, 'Pen, Notebook, Pen');INSERT INTO order_lines (order_id, product_id, quantity) VALUES (7, 2, 2), (7, 1, 1);Storing values you can calculate
CREATE TABLE order_lines (quantity INT, unit_price DECIMAL, line_total DECIMAL);
-- line_total can disagree with quantity * unit_priceCREATE TABLE order_lines (quantity INT, unit_price DECIMAL);
-- calculate quantity * unit_price in queries or a viewSplitting tables with no reason
CREATE TABLE customer_names (customer_id INT, name TEXT);
CREATE TABLE customer_cities (customer_id INT, city TEXT);CREATE TABLE customers (id INT PRIMARY KEY, name TEXT, city TEXT);Normalization is about removing repeated facts, not about making as many tables as possible.
Normalization in Real Projects
Normalised and denormalised designs
The databases behind shops, banks and booking systems are normalised, because correctness matters more than anything when data is constantly changing. Data warehouses used for analysis often denormalise on purpose, storing wide tables that are quick to read but rebuilt from the normalised source each night. The skill is knowing which you're building and why.
Normalization FAQ
Do I always need to normalise to third normal form?
For tables that store business data, such as customers, products and orders, aiming for third normal form is a sensible default because it prevents update mistakes. Reporting tables and caches are sometimes deliberately denormalised for speed, but that should be a documented decision, not an accident.
What is denormalisation?
Deliberately storing some data more than once, such as a customer's name on each order row or a running order total, to make particular reads faster or simpler. It trades extra storage and the work of keeping copies in step for fewer joins. Use it for measured performance needs.
Does normalisation make queries slower?
It means more joins, but databases are built to join well, especially on indexed keys. For most applications the difference is small, and normalised tables often make writes faster and data smaller. Measure before denormalising.
What is Boyce-Codd normal form?
BCNF is a slightly stricter version of third normal form: every column that determines other columns must be a candidate key. Most tables in third normal form already meet it. The normal forms and BCNF lesson covers the cases where they differ.
Is storing JSON in a column bad design?
Not always. JSON columns, such as PostgreSQL's jsonb, suit flexible extra attributes that you rarely filter or join on. Data you search, join or validate regularly, such as prices or customer ids, belongs in proper columns with types and constraints.
Normalization Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Complete the rule of normalization.
Each fact is stored in exactly place.Which column breaks first normal form?
You delete a customer's only order and lose all record of the customer. Which problem is this?
An orders table has columns id, customer_id and customer_city. Which normal form does customer_city break?
Why does order_lines store unit_price even though products has a price?
Normalization Quiz
5 questions to check what you learned.
What is the main goal of normalization?
- Faster typing
- Removing repeated data so each fact is stored once
- Making tables wider
- Using fewer keys
What does 2NF add to 1NF?
- Every table needs an index
- No NULLs are allowed
- Non-key columns must depend on the whole composite key
- Columns must be sorted
What's a repeating group?
- Columns like item1, item2, item3 that hold the same kind of value
- A GROUP BY clause
- Two identical rows
- A junction table
How do you get a flat report from a normalised database?
- Copy the data into a spreadsheet
- Denormalise every table
- Use UNION
- Join the tables in a query, optionally saved as a view
When can denormalisation make sense?
- For read-heavy reporting where speed is measured and data is rebuilt from a normalised source
- Always, it's simpler
- When you don't know SQL
- Never