By now you have been querying a database that was designed well: employees, departments, and projects each live in their own table, linked by foreign keys. That design did not happen by accident. It followed rules called normal forms that prevent data from contradicting itself and make the schema resilient to change.
Imagine instead that everything was crammed into one giant spreadsheet: employee name, department name, department city, project name, and budget all in the same row. If Engineering moves from Oslo to Bergen, you must find every employee in Engineering and update their city cell. Miss one, and the database disagrees with itself. Worse, if you delete the last employee in Research, you accidentally delete the fact that Research exists. That is a delete anomaly. Normalization is the discipline that prevents these traps.
flowchart TD
subgraph BAD["Unnormalized"]
B1["invoice_flat"]
end
subgraph GOOD["Normalized"]
G1["customers"]
G2["items"]
G3["invoice_lines"]
end
B1 -->|"split into"| G1
B1 -->|"split into"| G2
B1 -->|"split into"| G3
style BAD fill:#b45309,color:#fff
style GOOD fill:#0e7490,color:#fff
The three anomalies
Redundancy creates three specific problems. An update anomaly happens when a fact is stored in many places and you change it in only some of them. An insert anomaly happens when you cannot add a fact because it depends on another fact that does not exist yet — you cannot create a department until it has an employee. A delete anomaly happens when removing a row accidentally destroys a fact you wanted to keep — deleting the last employee in Research erases Research itself. Normalization eliminates all three by ensuring each fact lives in exactly one place.
First normal form: atomic values
First normal form, or 1NF, has one rule: every column in every row must hold a single, indivisible value. A comma-separated list like 'widget, gadget, thingama' is not atomic — it is three values pretending to be one. Searching for every row containing 'gadget' now requires string parsing instead of a clean equality check. Updating the price of just one item means editing the whole string and hoping you do not corrupt the others.
A table that stores a repeating group in one column violates 1NF. The fix is to split the repeating group into its own table and link it back with a foreign key. One row per fact, one column per value. This is the foundation every other normal form builds on.
CREATE TABLE invoice_flat ( invoice_id INTEGER, customer TEXT, items TEXT, total INTEGER ); INSERT INTO invoice_flat VALUES (1, 'Acme', 'widget, gadget', 150); SELECT * FROM invoice_flat; -- Finding every invoice with 'gadget' requires string parsing. -- Changing the price of one item means editing the whole string.
Second normal form: no partial dependency
Second normal form applies when a table has a composite primary key — two or more columns that together identify a row. 2NF says every non-key column must depend on the entire key, not just part of it.
Consider an enrollment table with primary key (student_id, course_id) and a column student_name. The name depends only on student_id, not on the combination with course_id. That is a partial dependency, and it means the name is stored redundantly for every course the student takes. The fix is to move student_name into a students table and keep only student_id in the enrollment table.
flowchart LR PK["(student_id, course_id)"] --> G["grade"] SK["student_id"] -.-> SN["student_name"] style PK fill:#0e7490,color:#fff style SK fill:#b45309,color:#fff
CREATE TABLE demo_customers ( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE demo_items ( id INTEGER PRIMARY KEY, name TEXT, price INTEGER ); CREATE TABLE demo_lines ( invoice_id INTEGER, item_id INTEGER, qty INTEGER ); INSERT INTO demo_customers (id, name) VALUES (1, 'Acme'); INSERT INTO demo_items (id, name, price) VALUES (1, 'widget', 50), (2, 'gadget', 100); INSERT INTO demo_lines (invoice_id, item_id, qty) VALUES (1, 1, 1), (1, 2, 1); SELECT c.name, i.name, i.price FROM demo_customers c JOIN demo_lines l ON c.id = l.invoice_id JOIN demo_items i ON l.item_id = i.id;
Third normal form: no transitive dependency
Third normal form removes columns that depend on other non-key columns rather than on the primary key directly. Suppose an employee table stores department_name and department_city. The city depends on the department name, not on the employee's id. That is a transitive dependency: id → department_name → department_city.
The fix is familiar by now: move department_name and department_city into a departments table, and store only department_id in the employee table. The employee table now depends only on its own key, and the department table depends only on its own key. No fact is stored more than once.
flowchart LR E["employee_id"] --> D["department_name"] D --> C["department_city"] style E fill:#0e7490,color:#fff style D fill:#b45309,color:#fff
When to denormalize
Denormalization is not a rebellion against the rules — it is an optimization with a known cost. You consider it when a query is slow, the data changes rarely, and the speed gain matters to users. A classic example is a dashboard that shows department headcounts. With normalized tables you compute the count with a GROUP BY every time the dashboard loads. If the dashboard is viewed a thousand times per hour and the headcount only changes once a day, pre-computing the count into a summary table saves enormous work at the cost of keeping that summary up to date.
The rule of thumb is simple: normalize first, measure second, and denormalize only when the data proves you need to. Starting with a denormalized schema because 'it might be faster' is premature optimization that usually creates data-quality bugs instead of speed. Always keep the denormalized data in sync with a trigger, a batch job, or clear documentation so the next developer knows where the copies live.
CREATE TABLE demo_summary ( department_name TEXT PRIMARY KEY, headcount INTEGER, total_salary INTEGER ); INSERT INTO demo_summary SELECT d.name, COUNT(e.id), SUM(e.salary) FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name; SELECT * FROM demo_summary ORDER BY total_salary DESC;
Which of these is a first normal form violation?
1NF requires atomic values. A comma-separated list is a repeating group disguised as a single value, which makes searching and updating individual items impossible.
A table has columns (order_id, product_id, product_name, quantity). The primary key is (order_id, product_id). Which normal form is violated and why?
2NF requires every non-key attribute to depend on the entire primary key. Here product_name depends only on product_id, not on the combination (order_id, product_id).
Create two tables to replace storing skills in a single text column. Create people (id, name) and person_skills (person_id, skill). Insert Ada with skills 'SQL' and 'Python', and Bo with 'Python'.
CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE person_skills (person_id INTEGER, skill TEXT); -- insert Ada and Bo, then their skills
Create both tables first, then insert the people, then insert their skills.
Bo only has one skill; Ada has two.
CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE person_skills (person_id INTEGER, skill TEXT); INSERT INTO people (id, name) VALUES (1, 'Ada'), (2, 'Bo'); INSERT INTO person_skills (person_id, skill) VALUES (1, 'SQL'), (1, 'Python'), (2, 'Python');
A denormalized table stores book and author data in one row. Split it into authors and books with a foreign key, then insert the data. Insert Franz Kafka from the Czech Republic, and two books by him: 'The Metamorphosis' and 'The Trial'.
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT, country TEXT); CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER); -- insert the author and both books
Insert the author first so the author_id foreign key exists.
Use two INSERT statements: one for the author, one for both books.
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT, country TEXT); CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER); INSERT INTO authors (id, name, country) VALUES (1, 'Franz Kafka', 'Czech Republic'); INSERT INTO books (id, title, author_id) VALUES (1, 'The Metamorphosis', 1), (2, 'The Trial', 1);
Write a query that returns each department's name and the number of employees in it, ordered by headcount from highest to lowest. This is the kind of report that would require a stored count in a denormalized schema, but with normalized tables you compute it on demand.
SELECT -- department name and count of employees FROM departments d -- join employees and group ORDER BY headcount DESC ;
Use LEFT JOIN so every department appears, even if it has zero employees.
GROUP BY both d.id and d.name to avoid ambiguity.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.id, d.name ORDER BY headcount DESC;
Recap
- 1NF requires atomic values: no lists, no repeating groups inside a single column.
- 2NF requires every non-key attribute to depend on the entire primary key.
- 3NF requires every non-key attribute to depend on the key, and nothing but the key.
- Redundancy causes update, insert, and delete anomalies — normalization removes it.
- Denormalization is a deliberate performance trade-off, not a design default.
With these rules in mind, you can design tables that stay clean as your application grows.