SQL ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-16

Database Normalization and Normal Forms

An employees table stores the salary in every employee’s row. The salary of position 1 is raised with UPDATE employees SET salary = 1200 WHERE emp_id = 1; — and now Miller earns 1200 while Clark, who holds the same position, still earns 1000. The database reported no error; the data simply contradicts itself. Normalization exists to make this situation impossible.

Database normalization is a step-by-step process of splitting tables into smaller ones so that every fact is stored in exactly one place and no information is lost. The goal is to remove duplication and the insert, update and delete anomalies it causes.

How many normal forms are there? Textbooks usually list six levels of normalization: 1NF, 2NF, 3NF, BCNF (Boyce–Codd normal form), 4NF and 5NF. Each one is stricter than the previous. In practice it is almost always enough to bring a schema to 3NF, and sometimes to BCNF.

Why Normalize a Database

You normalize a database so that each fact is stored once: then a change touches one row, and the data cannot contradict itself. When different entities are kept in one table, data is duplicated, and changes cause three kinds of anomalies:

  • update anomaly — the same value is stored in several rows, and it is easy to change only some of the copies;
  • insert anomaly — you cannot add a fact until related data exists (for example, a new position without an employee);
  • delete anomaly — deleting a row also destroys unrelated information (the last employee is deleted and the salary of the position is lost).

Normalization removes these problems. In return, queries need more JOINs. The tables in this lesson are linked by primary and foreign keys, which are covered in the lesson Keys and Relationships Between Tables in SQL; if you have not yet seen what tables, rows and columns are, start with Relational Databases and DBMS.

Functional Dependency and Keys

All normal forms except the first are defined through functional dependency. The notation A → B means that the value of A uniquely determines the value of B. For example, an employee number determines the employee’s last name: Employee number → Last name.

  • A candidate key is a minimal set of columns on which all other columns functionally depend. A table can have several of them.
  • The primary key is one of the candidate keys, chosen as the main one.
  • A prime attribute is a column that belongs to some candidate key; a non-prime (non-key) attribute belongs to none.
  • A superkey is any set of columns that identifies a row uniquely (a candidate key plus any extra columns).

Strictly speaking, the rules of 2NF and 3NF apply to all candidate keys, not only to the primary key. In textbook examples there is usually one candidate key, so below we simply talk about the primary key.

First Normal Form (1NF)

A table is in first normal form (1NF) when it has a key, all its rows are distinct and every cell holds a single atomic value rather than a list or a composite value. The criteria:

  • the table has a key and all rows are distinct;
  • all values in cells are atomic (not lists and not composite values).

A value is atomic if it cannot be split into parts that the table needs separately. A comma-separated list of products in one cell does not meet this requirement.

Not 1NF: a list of products in one cell
Category Products
Books War and Peace, ABC Book
Toys Spinning top

The fix: one row per product.

1NF: values are atomic
Category Product
Books War and Peace
Books ABC Book
Toys Spinning top

Second Normal Form (2NF)

A table is in second normal form (2NF) when it is in 1NF and every non-key column depends on the whole primary key, not on just a part of it. The criteria:

  • the table is in 1NF;
  • every non-key column is fully functionally dependent on the primary key, that is, on the whole key and not on a part of it.

If the key consists of one column (for example, id), there are no other composite candidate keys and the table is in 1NF, it is automatically in 2NF. The rule matters when the key is composite.

In the table below the primary key is the pair (Part, Warehouse). Quantity depends on both columns together. But Warehouse address is determined by Warehouse alone and does not depend on Part: this is a dependency on part of the key, so 2NF is violated.

1NF but not 2NF
Part (PK) Warehouse (PK) Quantity Warehouse address
Monitor Warehouse 1 56 34 Oak Street
Mouse Warehouse 1 78 34 Oak Street
Keyboard Warehouse 3 90 56 Pine Avenue

The warehouse address is repeated for every part stored there. To remove this, move the dependency on part of the key into a separate table:

Warehouses
Warehouse (PK) Warehouse address
Warehouse 1 34 Oak Street
Warehouse 3 56 Pine Avenue
Parts in warehouses
Part (PK) Warehouse (PK, FK) Quantity
Monitor Warehouse 1 56
Mouse Warehouse 1 78
Keyboard Warehouse 3 90

Third Normal Form (3NF)

A table is in third normal form (3NF) when it is in 2NF and every non-key column depends on the key directly, not through another non-key column. The criteria:

  • the table is in 2NF;
  • no non-key column depends on the primary key transitively, that is, through another non-key column.

Put simply, non-key columns must describe only the entity that the key identifies, and nothing else. If some columns describe another entity, they are moved to a separate table. The next section explains this in detail.

Transitive Dependency

A transitive dependency is a dependency of a column on the key that goes not directly but through an intermediate non-key column. Formally: if A → B and B → C, then C depends transitively on A through B (provided that B is not a key and does not determine A, and C is a non-key column). Exactly this chain violates 3NF:

A (key) → B (non-key) → C (non-key)

An Example of a Table with a Transitive Dependency

Consider an employees table. Here Employee number (A) determines Position code (B), and Position code determines Salary (C). The salary depends on the employee number only through the position:

Employee number → Position code → Salary

Employees: a transitive dependency exists (2NF but not 3NF)
Employee number (PK) Last name First name Position code Salary
1 Miller John 1 1000
2 Brown Emma 2 2000
3 Clark John 1 1000

The key is simple, so the table is in 2NF. But the salary of position 1 is written twice, and this is a source of anomalies:

  • update: the salary of position 1 was raised, but only one row was fixed — two employees with the same position now have different salaries;
  • insert: you cannot create a new position with a salary until an employee is hired for it;
  • delete: Brown was dismissed — and the fact that the salary of position 2 is 2000 disappeared with her.

How to Remove a Transitive Dependency When Moving to 3NF

The solution is decomposition: move columns B and C into a separate table where B becomes the key, and leave B in the original table as a foreign key.

  1. Find the chain A → B → C (here: employee → position → salary).
  2. Create a new table from B and C, with B as the primary key.
  3. Remove C from the original table, keeping B as a foreign key.
Employees (after)
Employee number (PK) Last name First name Position code (FK)
1 Miller John 1
2 Brown Emma 2
3 Clark John 1
Positions (after)
Position code (PK) Salary
1 1000
2 2000

Now the salary is stored once. You can change it with one UPDATE, add a position without an employee and delete an employee without losing salaries. The original table can be rebuilt with a join, so no information is lost. The raise from the opening example now changes exactly one row, and both employees in position 1 see it:

CREATE TABLE positions (
    position_id INT PRIMARY KEY,
    salary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    position_id INT NOT NULL,
    FOREIGN KEY (position_id) REFERENCES positions (position_id)
);

INSERT INTO positions VALUES (1, 1000), (2, 2000);
INSERT INTO employees VALUES (1, 'Miller', 'John', 1), (2, 'Brown', 'Emma', 2), (3, 'Clark', 'John', 1);

-- the raise from the opening example: one row, no contradictions
UPDATE positions SET salary = 1200 WHERE position_id = 1;

SELECT e.emp_id, e.last_name, p.salary
FROM employees e
JOIN positions p ON p.position_id = e.position_id
ORDER BY e.emp_id;

-- Result:
-- emp_id | last_name | salary
-- 1      | Miller    | 1200.00
-- 2      | Brown     | 2000.00
-- 3      | Clark     | 1200.00

Boyce–Codd Normal Form (BCNF)

BCNF is a stronger version of 3NF. A table is in BCNF if for every non-trivial functional dependency X → Y the left side X is a superkey.

How BCNF differs from 3NF. 3NF allows a dependency X → Y where X is not a superkey if Y is a prime attribute (it belongs to some candidate key). BCNF makes no such exception. Therefore every table in BCNF is in 3NF, but not the other way round. The two forms can differ only when a table has several overlapping composite candidate keys.

Example: 3NF but Not BCNF

Students enrol with teachers. The rules: each teacher teaches one subject, and a student has one teacher per subject.

Enrolment: 3NF but not BCNF
Student Subject Teacher
Adams Math Wilson
Baker Math Wilson
Adams Physics Turner

The candidate keys are (Student, Subject) and (Student, Teacher). The dependency Teacher → Subject violates BCNF: Teacher is not a superkey. But Subject belongs to a candidate key, so 3NF is formally satisfied. The consequence is duplication: the fact “Wilson teaches Math” is stored in every row of his students.

Decomposition into BCNF:

Teachers
Teacher (PK) Subject
Wilson Math
Turner Physics
Enrolment
Student (PK) Teacher (PK, FK)
Adams Wilson
Baker Wilson
Adams Turner

This split has a price: the rule “a student has one teacher per subject” can no longer be expressed with a key and needs a separate check (a trigger or application code). That is why in practice BCNF is applied when duplication is more dangerous than this extra complexity.

Fourth and Fifth Normal Forms

Fourth normal form (4NF) requires BCNF and no non-trivial multivalued dependencies. Such a dependency appears when one table stores two independent multivalued sets. For example, an employee has several skills and speaks several languages, and skills and languages are unrelated. If you keep them in one table Employee, Skill, Language, you have to store every combination: 3 skills and 2 languages give 6 rows instead of 5 facts. The solution is two tables: Employee – Skill and Employee – Language.

Fifth normal form (5NF) requires every join dependency in the table to follow from its candidate keys. The classic example is a table Supplier, Part, Project with the rule: if a supplier supplies a part, the part is used by a project and the supplier works with the project, then the supplier supplies that part to that project. Such a table can be rebuilt without loss only by joining all three two-column projections (any two are not enough), so in 5NF it is stored as three tables. In practice 5NF is rare.

Denormalization

Denormalization is a deliberate violation of normal forms: redundant data is put back into tables to make reads faster. It is not a rejection of normalization but the step after it: first normalize the schema, then measure, and only then denormalize the bottlenecks selectively.

Why Denormalize

  • Read performance. The more strictly a schema is normalized, the more tables have to be joined. For queries with many JOINs over large tables this is expensive.
  • Reports and analytics. Aggregates such as monthly sales are easier and faster to read from a ready-made table than to recalculate on every query.
  • Read-heavy workloads. When reads outnumber writes by orders of magnitude, duplication pays off.

Risks of Denormalization

  • Update anomalies: copies of one value diverge if not all of them are changed.
  • Duplicated data: more disk space and larger indexes.
  • More complex writes: every change has to be reflected consistently in all copies, which means more code, triggers and places for bugs.

Common Denormalization Techniques

Technique Example What you must maintain
Redundant column The customer name is copied into the orders table to avoid a JOIN with customers Consistency of the copy when the name changes
Stored aggregate orders stores total_amount, and customers stores orders_count Recalculation when detail rows are inserted, changed or deleted
Summary table or materialized view A sales_by_month table with ready monthly totals Periodic or streaming refresh
Array or JSON in a column Product tags in a JSON column instead of a junction table Violates 1NF: foreign keys to the elements are impossible, and searching needs special indexes (multi-valued index in MySQL 8.0.17+, GIN in PostgreSQL)

A materialized view is a stored query result that is refreshed on demand or automatically. In PostgreSQL it is created with CREATE MATERIALIZED VIEW and refreshed with REFRESH MATERIALIZED VIEW; Oracle can also refresh it automatically on commit (ON COMMIT). MySQL (including 8.x and 9.x) has no materialized views: they are imitated with a summary table that is rebuilt on a schedule (with an EVENT) or maintained by triggers.

-- imitating a materialized view in MySQL
CREATE TABLE sales_by_month AS
SELECT YEAR(order_date) AS y, MONTH(order_date) AS m, SUM(total_amount) AS total
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

-- full refresh (the equivalent of REFRESH MATERIALIZED VIEW)
TRUNCATE TABLE sales_by_month;
INSERT INTO sales_by_month (y, m, total)
SELECT YEAR(order_date), MONTH(order_date), SUM(total_amount)
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

When Denormalization Is Justified

  • there is a measured performance problem, and indexes and query tuning did not help;
  • data is read often but changes rarely or is only appended (history, logs, reference data);
  • you are building a data warehouse or a reporting database where data is updated in batches.

Do not denormalize “just in case” in a working transactional schema: profile first, decide after.

Not every repeated value is duplication

The price in an order line (order_items.price) is not a duplicate of the price in products and not denormalization. It is a different fact: the price at the time of purchase. It must not change when the current product price changes, so storing it in the order is correct even in a normalized schema. Before you “remove duplication”, ask yourself: must these two values always be equal?

Which Normal Form to Choose

For most business applications (including Java applications with a relational database) 3NF is enough: it removes most of the duplication and anomalies. BCNF, 4NF and 5NF are needed in specific cases, and denormalization is a targeted optimization applied after measurements.

Form What it eliminates Short rule
1NF Composite values in cells Atomic values, a key exists
2NF Dependency on part of a composite key Dependency on the whole key
3NF Transitive dependencies Only on the key, no intermediaries
BCNF Dependencies on non-superkeys The left side of every dependency is a superkey
4NF Independent multivalued dependencies One independent set of values per table
5NF Join dependencies not implied by keys Lossless decomposition only along the keys

Frequently Asked Questions

What is database normalization in simple words?

It is a way to arrange data in tables so that every fact is stored in one place. Instead of one big table where a customer name or a salary repeats in dozens of rows, you create separate tables and link them with keys. Then data is not duplicated, and inserts, updates and deletes do not create contradictions.

How many normal forms are there?

Textbooks name six: 1NF, 2NF, 3NF, BCNF, 4NF and 5NF. There are also the sixth normal form (6NF, used in warehouses that keep the history of changes) and domain-key normal form (DKNF), but they are rarely met in business systems.

Does adding an auto-increment id make a table normalized?

No. A single-column surrogate key only guarantees 2NF, and only if the table is already in 1NF and has no other composite candidate keys. It does not split a list stored in one cell, and it does not remove transitive dependencies: an employees table with an id column can still carry position code → salary and violate 3NF, as in the transitive dependency example.

Can a table be in 3NF but not in BCNF?

Yes, but only if it has several overlapping composite candidate keys. If there is only one candidate key, 3NF and BCNF coincide. An analysis of such a table is in the section Example: 3NF but Not BCNF.

How do I find a transitive dependency in a table?

Take every non-key column and ask: does it determine the values of other non-key columns? If the same position code always comes with the same salary, the chain key → position code → salary is found. A sign in the data is that the same pairs of values repeat in different rows. How to remove it is described in the section on moving to 3NF.

Is fourth normal form needed in practice?

Rarely. 4NF protects against duplication caused by two independent multivalued attributes in one table, such as the skills and languages of an employee. If you keep such sets in separate junction tables, the schema is already in 4NF without extra effort.

Is denormalization a design mistake?

No, if it is done deliberately: after normalization and measurements, with a clear way to keep the copies consistent. The mistake is a schema that was never normalized and duplicates data by accident. Techniques and risks are in the section Denormalization.

Does normalization make a database faster or slower?

Writes usually get faster: rows are shorter, a value is updated in one place and indexes are smaller. Reads can slow down because of extra JOINs, but with indexes on foreign keys the difference for typical queries is small. That is why you normalize the schema first and denormalize only measured bottlenecks.

Comments

Please log in or register to have a possibility to add comment.