Database Normalization

Database normalization is the process of organizing tables and columns in a relational database to minimize data redundancy and avoid a family of update, insertion, and deletion anomalies. It works by applying a series of rules — called normal forms — each stricter than the last, that reshape a single wide table into a set of smaller, well-defined tables connected by keys.

This guide starts with the intuition and worked examples in Part 1: The Basics, then moves into the formal theory in Part 2: Going Deeper for readers who want the full picture.

Part 1: The Basics

Why Normalize?

An unnormalized table tends to produce three kinds of problems, known as anomalies:

• Update anomaly — the same fact is stored in more than one row, so changing it means updating every copy, and it’s easy for copies to drift out of sync.

• Insertion anomaly — you can’t record a new fact (a new product, say) until an unrelated fact (an order for it) also exists.

• Deletion anomaly — deleting one fact (the last order for a customer) accidentally erases another fact entirely (that the customer exists at all).

Normalization removes these problems by making sure each table describes exactly one kind of thing, and every column depends on nothing but that table’s key.

The Starting Point: One Big Table

Imagine an order-tracking system that started life as a single spreadsheet:

Orders (unnormalized)

OrderID	CustomerName	CustomerCity	CustomerState	Products	OrderDate
1001	Alice Chen	Rochester	NY	Widget A, Gadget B	2026-01-14
1002	Alice Chen	Rochester	NY	Gadget B	2026-02-02
1003	Marcus Diaz	Buffalo	NY	Widget A, Cable C, Gadget B	2026-02-10

Already you can spot the trouble:

• The Products column crams a variable-length list into one cell — a repeating group.

• Alice Chen’s city and state are copied on every row she appears in.

• There’s no way to record a new product’s price until someone orders it.

Note

Every one of these issues maps directly to a normal form below. That’s not a coincidence — each normal form exists to close one specific gap.

The Normalization Ladder

Each rung fixes one thing and leaves the fixes below it intact — you can’t skip a step, because 2NF is only meaningful once the data is already in 1NF, and so on.

Step 1: First Normal Form (1NF) — Atomic Values

Rule: every column holds a single, indivisible value. No comma-separated lists, no repeating groups of columns.

Splitting the Products list into one row per product gets us to 1NF:

After 1NF

OrderID	CustomerName	CustomerCity	CustomerState	Product	OrderDate
1001	Alice Chen	Rochester	NY	Widget A	2026-01-14
1001	Alice Chen	Rochester	NY	Gadget B	2026-01-14
1002	Alice Chen	Rochester	NY	Gadget B	2026-02-02
1003	Marcus Diaz	Buffalo	NY	Widget A	2026-02-10
1003	Marcus Diaz	Buffalo	NY	Cable C	2026-02-10
1003	Marcus Diaz	Buffalo	NY	Gadget B	2026-02-10

Every cell is now atomic. But notice the row’s identity is no longer just OrderID — it’s the combination of OrderID and Product. That composite key is exactly what the next form deals with, and the customer redundancy is now worse, not better. 1NF only ever promises atomic values; it doesn’t promise a clean design.

Step 2: Second Normal Form (2NF) — Remove Partial Dependencies

Rule: applies only when a table’s primary key has more than one column. Every other column must depend on the whole key, not just part of it.

Suppose we also track each product’s price on the order-line table, which now has the composite key (OrderID, Product):

OrderDetails (before 2NF)

OrderID (PK)	Product (PK)	ProductPrice	Quantity
1001	Widget A	12.50	2
1001	Gadget B	7.00	1
1002	Gadget B	7.00	3

ProductPrice only depends on Product — it doesn’t care what OrderID it’s attached to. That’s a partial dependency, and it means Widget A’s price is duplicated on every order line that includes it. Only Quantity genuinely depends on the full (OrderID, Product) pair.

The fix is to split off the part that depends on only half the key:

Products

Product (PK)	ProductPrice
Widget A	12.50
Gadget B	7.00
Cable C	4.25

OrderDetails (after 2NF)

OrderID (PK)	Product (PK, FK)	Quantity
1001	Widget A	2
1001	Gadget B	1
1002	Gadget B	3

Now each product’s price is stored exactly once.

Step 3: Third Normal Form (3NF) — Remove Transitive Dependencies

Rule: every non-key column must depend only on the key — not on another non-key column.

Look back at the customer columns. In a simplified Orders table:

Orders (before 3NF)

OrderID (PK)	CustomerName	CustomerCity	CustomerState	OrderDate
1001	Alice Chen	Rochester	NY	2026-01-14
1002	Alice Chen	Rochester	NY	2026-02-02
1003	Marcus Diaz	Buffalo	NY	2026-02-10

CustomerCity and CustomerState don’t really depend on OrderID — they depend on which customer placed the order. In other words: OrderID → CustomerName → CustomerCity. That chain is a transitive dependency, and it’s why Alice’s city and state show up twice.

The fix, same pattern as before — split off the part that depends on something other than the key, and give it its own key:

Customers

CustomerID (PK)	CustomerName	CustomerCity	CustomerState
C01	Alice Chen	Rochester	NY
C02	Marcus Diaz	Buffalo	NY

Orders (after 3NF)

OrderID (PK)	CustomerID (FK)	OrderDate
1001	C01	2026-01-14
1002	C01	2026-02-02
1003	C02	2026-02-10

Putting It All Together

Combine the two decompositions above and the schema settles into four focused tables:

Every table now describes exactly one thing — a customer, an order, a product, or a line item — and every fact lives in exactly one place.

Quick Reference

Step	Rule	Removes
→ 1NF	Every column holds one atomic value; no repeating groups.	Multi-valued / repeating-group columns.
→ 2NF	Every non-key column depends on the whole primary key (matters only for composite keys).	Partial dependencies.
→ 3NF	Every non-key column depends only on the key, not on another non-key column.	Transitive dependencies.

Part 2: Going Deeper

Everything above works from intuition. The formal theory beneath it — built on functional dependencies — is what lets you prove a design is correct instead of just eyeballing it, and it’s what the stricter normal forms (BCNF, 4NF, 5NF) are built on.

Functional Dependencies, Formally

A functional dependency X → Y holds on a table when, for any two rows that share the same value of X, they must also share the same value of Y. Read it as “X determines Y.”

ProductID → ProductPrice is a functional dependency: given a product, its price is fixed. X is called the determinant.

Keys: Superkey, Candidate Key, Primary Key

• A superkey is any set of columns that uniquely identifies a row.

• A candidate key is a minimal superkey — remove any column from it and it stops being unique.

• A primary key is simply the candidate key a designer chooses as the table’s main identifier. A table can have several candidate keys but only one primary key.

Full vs. Partial Dependency

For a table with composite key (A, B), a non-key column C is:

• Fully dependent on (A, B) if it depends on the pair together, and not on A or B alone.

• Partially dependent if it actually only depends on A or only on B.

2NF is precisely the requirement that no partial dependencies exist.

Transitive Dependency, Formally

Given X → Y and Y → Z, where X is the key and Y is a non-key column, Z is transitively dependent on X through Y. Codd’s original definition of 3NF forbids this for any Z that isn’t itself part of some candidate key — in practice, that exception rarely matters for the tables most applications design.

Boyce-Codd Normal Form (BCNF)

Rule: for every functional dependency X → Y in the table, X must be a superkey. No exceptions.

BCNF is 3NF with the loophole closed. It’s needed when a table has more than one overlapping candidate key. Classic example — a table tracking which instructor teaches which course to which student, where:

• Each course can be taught by several instructors.

• Each instructor teaches only one course.

• A student can take a course from any of its instructors.

StudentCourseInstructor

Student	Course	Instructor
Alice Chen	Databases	Dr. Kim
Alice Chen	Databases	Dr. Okafor
Marcus Diaz	Databases	Dr. Kim

Here, (Student, Course) is a candidate key, but so is (Student, Instructor) — and Instructor → Course holds on its own (each instructor belongs to one course). That table is already in 3NF (Course is part of a candidate key, satisfying Codd’s exception), but it fails BCNF, because Instructor is a determinant and not a superkey by itself. The fix is the same decomposition pattern as before:

StudentInstructor

Student	Instructor
Alice Chen	Dr. Kim
Alice Chen	Dr. Okafor
Marcus Diaz	Dr. Kim

InstructorCourse

Instructor	Course
Dr. Kim	Databases
Dr. Okafor	Databases

Beyond BCNF: 4NF and 5NF

These come up rarely in ordinary application design, but are worth knowing by name:

• Fourth Normal Form (4NF) eliminates multivalued dependencies — cases where a table stores two independent multi-valued facts about the same entity (say, a person’s phone numbers and their email addresses) in one table, which forces every combination to be spelled out. The fix is to split the two facts into separate tables.

• Fifth Normal Form (5NF), also called Project-Join Normal Form, handles tables that can only be losslessly reconstructed by joining three or more smaller tables, not two. It mostly matters for ternary relationships like supplier–part–project associations, and is largely a theoretical concern for most schemas.

Denormalization: Breaking the Rules on Purpose

Once a schema is properly normalized, it’s common to deliberately reintroduce some redundancy — this is denormalization, and it’s a performance trade-off, not a shortcut around design:

• Read-heavy reporting workloads may pre-join or cache aggregates to avoid expensive joins on every query.

• Star-schema data warehouses keep wide, redundant fact and dimension tables because they’re optimized for analytical scans, not transactional integrity.

• Materialized views store the result of an expensive query so it doesn’t need to be recomputed every time.

The rule of thumb: normalize first, understand exactly what redundancy you’re reintroducing, and document why.

Summary Table of All Normal Forms

Normal Form	Requirement
1NF	Every column holds an atomic value; no repeating groups.
2NF	1NF, plus no non-key column partially depends on a composite key.
3NF	2NF, plus no non-key column transitively depends on another non-key column.
BCNF	3NF, plus every determinant is a candidate key (no exceptions).
4NF	BCNF, plus no multivalued dependencies.
5NF	4NF, plus no join dependencies beyond what the candidate keys already imply.

Tip

In practice, most well-designed application schemas stop at 3NF or BCNF. 4NF and 5NF are good to recognize when you see the symptoms, but rarely something you need to design toward from scratch.