Database Normalization — 1NF to 5NF Explained

Overview

Normalization takes a raw table — one where the same fact lives in twenty rows and updating one place breaks another — and splits it into smaller tables that each describe one thing. The normal forms (1NF through 5NF) are a ladder of rules that remove a specific kind of redundancy at each rung. Most production schemas stop at 3NF or BCNF; the higher forms matter for interviews, weird edge cases, and knowing exactly what you’re giving up when you denormalize on purpose.

When to Use

• Designing new relational schemas from scratch
• Refactoring legacy databases with duplicate data
• Preparing schemas for transactional workloads (OLTP)
• Before deciding what to denormalize for reporting (OLAP) — see the companion Database Denormalization Guide for the flip side

1NF — Atomic Values

Rule: Every column contains only atomic (indivisible) values. No repeating groups.

Before — a repeating group in one column:

order_id	customer	products
1	Alice	Apple, Banana, Cherry

After — atomic values only:

order_id	customer	product
1	Alice	Apple
1	Alice	Banana
1	Alice	Cherry

2NF — No Partial Dependencies

Rule: All non-key attributes depend on the entire primary key (relevant for composite keys).

Before — depends on part of the key:

course_id	student_id	course_name	student_name	grade
CS101	S1	Intro to CS	Alice	A

course_name depends only on course_id; student_name only on student_id.

After — everything depends on the whole key:

Enrollments:

course_id	student_id	grade
CS101	S1	A

Courses:

course_id	course_name
CS101	Intro to CS

Students:

student_id	student_name
S1	Alice

3NF — No Transitive Dependencies

Rule: Non-key attributes depend only on the primary key, not on other non-key attributes.

Before — a non-key column determines another:

employee_id	name	department_id	department_name	department_head
E1	Bob	D1	Engineering	Carol

department_name and department_head depend on department_id, not employee_id.

After — facts about the key, nothing else:

Employees:

employee_id	name	department_id
E1	Bob	D1

Departments:

department_id	department_name	department_head
D1	Engineering	Carol

BCNF — Boyce-Codd Normal Form

Rule: For every functional dependency X → Y, X must be a superkey.

Before — a non-key determinant:

student	course	professor
Alice	CS101	Prof. Smith
Bob	CS101	Prof. Smith

course → professor, but course is not a superkey.

After — every determinant is a key:

Enrollments:

student	course
Alice	CS101
Bob	CS101

CourseAssignments:

course	professor
CS101	Prof. Smith

4NF — No Multi-Valued Dependencies

Rule: No multi-valued dependencies except those on a superkey.

Before — two independent facts per row:

employee	skill	language
Alice	Java	English
Alice	Java	Spanish
Alice	Python	English
Alice	Python	Spanish

Skills and languages are independent multi-valued facts.

After — independent facts live apart:

EmployeeSkills:

employee	skill
Alice	Java
Alice	Python

EmployeeLanguages:

employee	language
Alice	English
Alice	Spanish

5NF — Join Dependency / Projected Join

Rule: Every join dependency is implied by the candidate keys.

Before — a joinable triplet forced into pairs:

agent	company	product
Smith	Ford	Truck
Smith	Ford	Car
Smith	Toyota	Car
Jones	Toyota	Car

After — decomposed along the join dependency:

AgentCompany:

agent	company
Smith	Ford
Smith	Toyota
Jones	Toyota

AgentProduct:

agent	product
Smith	Truck
Smith	Car
Jones	Car

CompanyProduct:

company	product
Ford	Truck
Ford	Car
Toyota	Car

Normalization Summary

Form	Rule	Eliminates
1NF	Atomic values	Repeating groups
2NF	Full key dependency	Partial dependencies
3NF	Key-only dependency	Transitive dependencies
BCNF	Superkey determinant	Remaining anomalies
4NF	No multi-valued deps	Independent multi-values
5NF	Join dependencies	Reconstructable joins

Each step removes one class of redundancy — stop where your workload stops needing it.

When to Stop Normalizing

• 3NF/BCNF is the practical stopping point for most OLTP systems
• 4NF matters when you have true multi-valued attributes (rare)
• 5NF is mostly theoretical for production applications
• Denormalize intentionally when read performance matters more than write integrity — the denormalization guide covers the patterns and sync strategies

Common Mistakes

• Over-normalizing to 5NF — adds complexity with minimal practical benefit
• Under-normalizing to 1NF — leads to update anomalies and data inconsistency
• Normalizing before understanding queries — the schema should serve the workload
• Ignoring BCNF — 3NF does not handle all anomalies; BCNF is the stricter standard

Example: Normalization Steps

-- 1NF: Remove repeating groups
-- Unnormalized: orders(id, customer_name, items_csv)
-- 1NF:         orders(id, customer_name, item_name, qty)

-- 2NF: Remove partial dependencies (composite key)
-- 1NF:  order_items(order_id, product_id, product_name, qty)
-- 2NF:  orders(order_id, customer_id)
--       products(product_id, product_name)
--       order_items(order_id, product_id, qty)

-- 3NF: Remove transitive dependencies
-- 2NF:  orders(order_id, customer_id, customer_name, customer_city)
-- 3NF:  orders(order_id, customer_id)
--       customers(customer_id, customer_name, customer_city)

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(200) NOT NULL,
  customer_city VARCHAR(100)
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE order_items (
  order_id INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  qty INT NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);