The 5 normal forms
A ladder of rules that squeezes redundancy out of a schema, one dependency at a time.
Normalization organizes columns to eliminate redundancy and update anomalies. 1NF demands atomic values; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies; BCNF is a stricter 3NF; 4NF removes multi-valued dependencies. Each form builds on the last — but more normalization means more joins, so teams often denormalize for read performance.
Why normalize
When the same fact is stored in many places, those copies inevitably drift out of sync, producing update, insertion, and deletion anomalies. Normalization is a staged process that removes that redundancy one dependency at a time, so each fact lives in exactly one place. Each normal form is a rule that assumes the previous one is already satisfied.
1NF — atomic values
First normal form requires every column to hold a single, indivisible (atomic) value, with no repeating groups. A "tags" column holding "red,blue,green" or three columns tag1/tag2/tag3 both violate 1NF. The fix is to move the repeating values into their own related table, one row per value.
2NF — no partial dependency
Second normal form applies when the primary key is composite (made of several columns). It forbids a non-key column from depending on only part of that key rather than the whole thing. For example, in an order-line table keyed by (order_id, product_id), storing the product's name is a partial dependency, because the name depends on product_id alone.
3NF — no transitive dependency
Third normal form removes transitive dependencies, where a non-key column depends on another non-key column instead of directly on the key. If an employees table stores department_id and also department_name, the name depends on department_id, not on the employee — so it belongs in a departments table. This eliminates the indirect redundancy 2NF leaves behind.
BCNF & 4NF — the strict tiers
Boyce-Codd normal form tightens 3NF so that every determinant (anything that decides another column's value) must be a candidate key, closing edge cases with overlapping keys. Fourth normal form goes further, removing independent multi-valued dependencies where two unrelated one-to-many facts share a table. More forms mean fewer anomalies but more tables — and therefore more joins to read the data back.
The problem: redundancy causes anomalies
Normalization exists to solve one concrete problem: storing the same fact in more than one place. It sounds harmless, but duplication is where data quietly rots. Picture a single orders table that repeats the customer's name, email, and address on every order they've ever placed. That design suffers three classic anomalies:
- Update anomaly. A customer changes their email. Now you must find and update *every* order row they own; miss one and your data contradicts itself, with no way to know which copy is right.
- Insertion anomaly. You want to record a new customer who hasn't ordered yet — but there's no place to put them, because the table is keyed by order. You'd have to invent a fake order.
- Deletion anomaly. You delete a customer's only order and accidentally erase the only record that the customer ever existed.
The root cause of all three is that unrelated facts (customer identity vs. order details) are crammed into one table, so a fact about the customer is duplicated across many order rows. Normalization is the systematic cure: a series of rules that reorganize columns so that each fact lives in exactly one place, tied to the thing it actually describes. Its currency is the functional dependency — the statement "column A determines column B." The whole discipline is really about lining up every dependency so it points at a key and nothing else.
First and second normal form
The normal forms are a ladder; each assumes the one below it already holds.
First normal form (1NF): atomic values, no repeating groups. Every cell must hold a single indivisible value. A tags column containing "urgent,billing,vip" violates 1NF, and so does bolting on tag1, tag2, tag3 columns — that's just a repeating group in disguise. Both make querying miserable: you can't cleanly ask "all rows tagged vip" without string parsing, and you cap how many tags a row can have. The fix is a related tags table with one row per tag, joined back to the parent. 1NF is what makes a table genuinely *relational* rather than a spreadsheet.
Second normal form (2NF): no partial dependencies. 2NF only bites when the primary key is composite — made of two or more columns. It says no non-key column may depend on just *part* of that key. Consider an order_lines table keyed by (order_id, product_id) that also stores product_name. The product's name depends on product_id alone, not on the full key — a partial dependency. The consequence is redundancy: the same product name repeats on every order line for that product, reintroducing the update anomaly. The remedy is to move product_name into a products table keyed by product_id, leaving order_lines holding only facts that depend on the whole (order_id, product_id) pair, like quantity.
Notice the pattern already forming: each form finds a category of misplaced dependency and relocates the offending column into a table where it depends on the full key.
Third normal form, BCNF, and 4NF
Third normal form (3NF): no transitive dependencies. A transitive dependency is when a non-key column depends on *another non-key column* rather than directly on the key. Suppose an employees table keyed by employee_id stores department_id and department_name. The key determines department_id, and department_id in turn determines department_name — so department_name depends on the key only *transitively*. The redundancy is obvious: every employee in the same department repeats its name, and renaming the department means touching every one of their rows. The fix is a departments table keyed by department_id; the employee table keeps only the foreign key. The informal slogan captures 1NF–3NF neatly: every non-key column must depend on "the key, the whole key, and nothing but the key."
Boyce-Codd normal form (BCNF) is a stricter 3NF for edge cases the slogan misses. It requires that *every* determinant — anything that functionally determines another column — must itself be a candidate key. This matters when a table has multiple overlapping candidate keys, a situation 3NF technically permits but which can still hide an anomaly. In practice, a schema in 3NF is usually in BCNF too; the difference only surfaces in unusual key structures.
Fourth normal form (4NF) removes multi-valued dependencies — two *independent* one-to-many facts sharing one table. If an instructors table lists both the courses an instructor teaches and the languages they speak, and those two are unrelated, every course pairs with every language, multiplying rows meaninglessly. 4NF splits them into two separate tables. Beyond 4NF lie 5NF and 6NF, but they're rarely relevant outside specialized modeling.
The trade-off: normalization vs. denormalization
Normalization is not an unqualified good, and treating "more normalized" as "better" is a beginner's mistake. The cost is spelled out directly by the joins article: the more you split data apart, the more tables you must join back together to answer a query.
A fully normalized schema for an e-commerce order might spread a single "show me this order" screen across orders, order_lines, products, customers, and addresses — five tables, four joins, for one page. On a read-heavy, high-traffic system, that join cost is paid on every request, and past a point it dominates. The very redundancy normalization eliminates is sometimes exactly what makes reads fast.
Denormalization is the deliberate reintroduction of redundancy to buy read performance. Common tactics:
- Duplicating a column to avoid a join — storing
customer_nameon theorderstable so the order screen needs no customer join. - Precomputed aggregates — keeping a
comment_counton a post rather thanCOUNT-ing the comments table every render. - Read-optimized copies — materialized views or reporting tables shaped for how they're queried.
The critical word is deliberate. Denormalization is a conscious trade — you accept the update anomalies normalization was designed to prevent, and take on the burden of keeping the duplicates consistent (via application logic, triggers, or scheduled jobs). You normalize first to get a correct, anomaly-free model, then denormalize *specific hot paths* with eyes open, measuring that the read win justifies the write-side complexity.
Normalization in practice
In real projects, the goal isn't to march every table to 4NF — it's to design a schema that's correct by default and fast where it needs to be. A few guidelines make that practical.
Aim for 3NF as the working default. Third normal form eliminates the anomalies that cause the most pain (mismatched duplicates, orphaned records) while keeping the join count reasonable. The vast majority of transactional (OLTP) schemas — the databases behind apps that constantly insert and update — should live comfortably at 3NF/BCNF. It's the sweet spot between data integrity and query simplicity.
Let the workload guide deviations. The right shape depends on read/write balance:
- OLTP systems (order entry, user accounts) write constantly, so anomaly-free integrity matters most — stay normalized.
- OLAP / analytics systems (dashboards, reporting warehouses) read enormous volumes and rarely update, so they deliberately denormalize into star schemas with wide, redundant dimension tables that minimize joins.
Denormalize with a mechanism to stay consistent. If you duplicate a column, decide up front how the copies stay in sync — application-level updates, database triggers, or a nightly rebuild — and accept that this is now your responsibility, not the schema's.
Normalization also connects to the rest of the database story. Because it multiplies tables, it multiplies joins, which is why indexes on foreign keys matter so much in a normalized schema, and why the query optimizer works hardest on join-heavy normalized queries. Understanding normalization is understanding the fundamental tension every relational schema navigates: the same redundancy that corrupts your data is the redundancy that speeds up your reads, and good design is knowing exactly where to allow each.
Normalization removes redundancy and the update/insert/delete anomalies it causes.
1NF: every column holds a single atomic value — no repeating groups or lists in a cell.
2NF: 1NF plus no partial dependency — non-key columns depend on the whole composite key, not part of it.
3NF: 2NF plus no transitive dependency — non-key columns depend only on the key, not on other non-key columns.
BCNF: a stricter 3NF where every determinant is a candidate key; 4NF removes multi-valued dependencies.
Trade-off: more normalization means less redundancy but more joins — denormalize deliberately for read-heavy workloads.
Quick quiz
A table stores tags as a comma-separated string in one column. Which normal form does it violate?
What's the core trade-off of heavy normalization?
The 4 types of SQL join
Inner, left, right, full — which rows survive when two tables meet.
How a SQL query executes
The pipeline that turns a text string into rows fetched off disk.
Database index types
The data structures that turn a full-table scan into a pinpoint lookup.