Imagine storing each employee's department name directly on their row. Engineering has four people, so the word Engineering is written four times. When the team renames itself to Platform, you must find and rewrite all four rows — miss one and the database now contradicts itself.
Normalization is the discipline of giving each fact a single home. A department's name lives once, in departments; every employee points at it through a department_id. Change the name in one place and every employee sees the update instantly.
The reward is integrity: no contradictions, no missed updates, no wasted space. The price is that answers get scattered across tables, and reassembling them costs a join. Every schema decision is a trade between those two forces — write safety against read speed — and this lesson teaches you to see the trade and choose deliberately.
flowchart LR
subgraph DN["Denormalized: facts repeated"]
D1["Ada — Engineering — Oslo"]
D2["Bo — Engineering — Oslo"]
end
subgraph NO["Normalized: facts linked"]
E["employees: name, department_id"]
DEP["departments: id, name, city"]
E -->|"department_id"| DEP
end
style DN fill:#b45309,color:#fff
style NO fill:#0e7490,color:#fff
Three problems that disappear
A table that repeats the same value invites three classic failures. An update anomaly leaves copies out of sync when you change one and forget the rest. An insertion anomaly stops you recording a department that has no employees yet, because the department's facts only live on employee rows. A deletion anomaly erases a department's last fact the moment you delete its last employee.
Normalization dissolves all three by separating entities. Each entity — a department, an employee, a project — gets its own table and its own primary key. Relationships travel as foreign keys: employees.department_id references departments.id. The department can exist with zero employees, and deleting an employee never touches the department row it points to.
The rule of thumb is one fact, one place. Whenever a column repeats the same value across many rows, ask whether it describes a different entity that deserves its own table.
CREATE TABLE offices (
id INTEGER PRIMARY KEY,
city TEXT NOT NULL
);
INSERT INTO offices (city)
SELECT DISTINCT city FROM departments WHERE city IS NOT NULL;
SELECT * FROM offices;
The price you pay: joins
Normalization is not free. The moment you split a fact into its own table, every question that needs both halves must stitch them back together. Asking for an employee's city now means following department_id from employees into departments — a join where a single-table query used to suffice.
Each level of normalization tends to add one more table to the average read. A report that joined two tables in a flatter design may join five in a fully normalized one. On a small database the cost is invisible; on a large, read-heavy system it can dominate response time. That is the central tension: every split protects integrity and adds join work, and the art of schema design is deciding where the protection is worth the cost.
flowchart LR N["Highly normalized<br/>integrity, more joins"] --> P["Practical middle<br/>3NF plus indexes"] P --> D["Denormalized<br/>fast reads, write risk"] style N fill:#0e7490,color:#fff style D fill:#b45309,color:#fff style P fill:#3776ab,color:#fff
SELECT e.name, d.name AS department, d.city FROM employees e JOIN departments d ON e.department_id = d.id LIMIT 5;
How far to normalize
Normalization comes in levels, called normal forms, each removing a specific kind of redundancy. Most application schemas aim for third normal form (3NF): every non-key column depends on the whole primary key and nothing else. In practice that means a column belongs in a table only if it describes that table's entity and nothing but that entity.
Chasing the higher normal forms past 3NF rarely pays off — the remaining redundancies are exotic and the joins they add cost more than they save. The pragmatic stopping point is a clean 3NF schema with foreign keys enforced and indexes on the columns you join and filter. From that baseline, denormalize the few queries that prove too slow, rather than denormalizing everything up front and inheriting anomalies across the board.
flowchart TD
R["Rename Marketing to Brand"] --> Q{"normalized?"}
Q -->|"yes: stored once"| O["one update, every reader sees Brand"]
Q -->|"no: copied per row"| M["update each copy — miss one and rows disagree"]
style O fill:#0e7490,color:#fff
style M fill:#b45309,color:#fff
Denormalizing on purpose
Sometimes the join cost wins. A reporting dashboard that runs the same five-table join on every page load is paying for integrity it does not need at that moment, because reports are read-only snapshots. Denormalization is the deliberate choice to duplicate data so a hot query can read it from one place.
A common technique is a flattened table that pre-computes the joins and is refreshed on a schedule. The employees and their department stay normalized for writes, while a separate summary table holds the joined result for fast reads. You trade extra storage and a refresh step for dramatically simpler, faster queries. The decision is always economic: denormalize a query that runs thousands of times more often than the underlying data changes, and keep the system of record normalized so writes stay safe.
CREATE TABLE employee_flat AS SELECT e.name, e.salary, d.name AS department_name, d.city FROM employees e JOIN departments d ON e.department_id = d.id; SELECT name, department_name, city FROM employee_flat LIMIT 5;
Cities are currently repeated across the departments table (Oslo appears twice). Normalize them into their own lookup table: create a table named offices with columns id (INTEGER PRIMARY KEY) and city (TEXT, not null).
CREATE TABLE offices -- id and city columns here ;
The columns go inside parentheses: (id INTEGER PRIMARY KEY, city TEXT NOT NULL).
Give the table the exact name offices.
CREATE TABLE offices (id INTEGER PRIMARY KEY, city TEXT NOT NULL);
Because the city lives in departments, listing an employee's city requires a join. Return each employee's name and their department's city. (Employees with no department, like Omar, will not appear.)
SELECT e.name, d.city FROM employees e -- join departments and select the city ;
Join employees e to departments d on e.department_id = d.id.
Select e.name and d.city.
SELECT e.name, d.city FROM employees e JOIN departments d ON e.department_id = d.id;
This query is meant to count how many employees sit in each department, but it joins on d.id = e.id — pairing each department with an employee who happens to share its id, which is meaningless. Fix the join to follow the real foreign key, and keep the grouping.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.id GROUP BY d.id, d.name ;
The link is the foreign key: e.department_id = d.id.
Keep the GROUP BY d.id, d.name.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
You store the department name redundantly on every employee row to speed up a report. Marketing is renamed to Brand. What is the main risk?
Duplicating a fact across rows reintroduces the update anomaly. The fix is either to normalize (one home for the name) or to automate the refresh of the copy so it can never fall behind.
Transfer task: find the departments located in Oslo that have at least two employees, and return each such department's name and its employee count. You will need a join, a filter on the department's city, grouping, and a HAVING clause.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d -- join, filter by city, group, and require at least 2 ;
Join departments d to employees e on e.department_id = d.id.
Filter with WHERE d.city = 'Oslo'.
Group by d.id, d.name and keep groups with HAVING COUNT(e.id) >= 2.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON e.department_id = d.id WHERE d.city = 'Oslo' GROUP BY d.id, d.name HAVING COUNT(e.id) >= 2;
Recap
- Normalization gives each fact one home, ending update, insertion, and deletion anomalies.
- The price is joins: every split adds read work to reassemble the answer.
- Relationships travel as foreign keys (
employees.department_idpoints todepartments.id). - Aim for a clean 3NF baseline, then denormalize on purpose only for hot, read-heavy queries.
- A duplicated column is a liability — automate its refresh, or it will go stale.
Next you will learn to debug SQL errors systematically — reading the message, classifying the fault, and fixing the real cause rather than the symptom.