Database Normalization Explained

A customer changes their email address, support updates it, and a week later the invoices are still going to the old one. You dig in and find the email stored in three places: the customers table, a column on orders, and a JSON blob someone added for the mailer. Only one of them got updated. That's the kind of bug database normalization exists to prevent: each fact lives in one place, so there's only one place to change it.

This post goes through the normal forms from 1NF up to 5NF using small tables and the SQL to fix each one. It also covers functional dependencies (the idea everything else is built on) and when it's reasonable to denormalize on purpose.

Why Normalize? Understanding Data Anomalies

Normal forms make more sense once you've seen what goes wrong without them. A badly structured table runs into three kinds of data anomalies, and each one leaves you with data that's missing, contradictory, or quietly gone.

Here's a typical orders table where everything got put in one place:

Understanding Functional Dependencies

Every normal form is defined in terms of functional dependencies, so it's worth getting comfortable with them first. If knowing the value of one column (or set of columns) tells you exactly the value of another, the second depends on the first.

1NF First Normal Form

Every other normal form assumes you've already got this one. A table is in 1NF when it follows these rules:

Violating 1NF: Multi-valued Attributes

Converting to 1NF

-- Option 1: Separate rows for each value
CREATE TABLE StudentCourses (
    StudentID    VARCHAR(10),
    Name         VARCHAR(100),
    Course       VARCHAR(50),
    PRIMARY KEY (StudentID, Course)
);

INSERT INTO StudentCourses VALUES
('S101', 'Alice', 'Math'),
('S101', 'Alice', 'Physics'),
('S101', 'Alice', 'Chemistry'),
('S102', 'Bob', 'History'),
('S102', 'Bob', 'English');

-- Option 2: Separate table for phone numbers
CREATE TABLE StudentPhones (
    StudentID    VARCHAR(10),
    PhoneNumber  VARCHAR(20),
    PRIMARY KEY (StudentID, PhoneNumber),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

The comma-separated column is the 1NF violation I run into most in real code, usually because it was the quickest way to ship a "tags" field. The pain shows up in queries. WHERE Courses LIKE '%Math%' can't use a normal index, so it scans the whole table, and it also matches "Applied Mathematics". MySQL's FIND_IN_SET() gets the matching right but still can't use an index, and removing one value means string surgery inside an UPDATE. Once you need to filter or join on the values, move them into their own table.

2NF Second Normal Form

A table is in Second Normal Form when it:

1. Is already in 1NF
2. Has no partial dependencies, meaning every non-key column depends on the entire primary key, not just part of it

2NF Violation Example

Converting to 2NF

-- Students table (StudentID → StudentName)
CREATE TABLE Students (
    StudentID    VARCHAR(10) PRIMARY KEY,
    StudentName  VARCHAR(100)
);

-- Courses table (CourseID → CourseName, InstructorName)
CREATE TABLE Courses (
    CourseID       VARCHAR(10) PRIMARY KEY,
    CourseName     VARCHAR(100),
    InstructorName VARCHAR(100)
);

-- Enrollments table (full key dependency)
CREATE TABLE Enrollments (
    StudentID  VARCHAR(10),
    CourseID   VARCHAR(10),
    Grade      VARCHAR(5),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

3NF Third Normal Form

3NF goes after transitive dependencies: columns that depend on the key only by way of some other column. A table is in 3NF when:

1. It's already in 2NF
2. No non-key attribute depends on another non-key attribute

3NF Violation Example

Converting to 3NF

-- Employees table (no transitive dependencies)
CREATE TABLE Employees (
    EmployeeID    VARCHAR(10) PRIMARY KEY,
    EmployeeName  VARCHAR(100),
    DepartmentID  VARCHAR(10),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Departments table (contains department-specific data)
CREATE TABLE Departments (
    DepartmentID       VARCHAR(10) PRIMARY KEY,
    DepartmentName     VARCHAR(100),
    DepartmentLocation VARCHAR(100)
);

-- Now querying with a JOIN:
SELECT 
    e.EmployeeID,
    e.EmployeeName,
    d.DepartmentName,
    d.DepartmentLocation
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Once you split things this way you'll write more JOINs. That's the normal trade, and with indexed foreign keys it's rarely the bottleneck people expect.

BCNF Boyce-Codd Normal Form

BCNF (sometimes called 3.5NF) closes a small gap that 3NF leaves open. A table is in BCNF when:

1. It's in 3NF
2. For every functional dependency X → Y, X must be a superkey

BCNF Violation Example

Consider a university scheduling system:

Converting to BCNF

-- Professor teaches one subject
CREATE TABLE ProfessorSubject (
    Professor  VARCHAR(100) PRIMARY KEY,
    Subject    VARCHAR(100)
);

-- Student enrolled with professor
CREATE TABLE StudentProfessor (
    Student    VARCHAR(100),
    Professor  VARCHAR(100),
    PRIMARY KEY (Student, Professor),
    FOREIGN KEY (Professor) REFERENCES ProfessorSubject(Professor)
);

One catch: this decomposition loses the rule that a student has only one professor per subject. Neither table can enforce it on its own, so you'd need application logic or a trigger. That's a known BCNF trade-off, and it's one reason some teams stop at 3NF for tables like this.

4NF Fourth Normal Form

4NF is about multi-valued dependencies: two independent lists crammed into the same table. A table is in 4NF when:

1. It's in BCNF
2. It has no multi-valued dependencies (or the multi-valued dependency is on a superkey)

4NF Violation Example

-- Separate independent multi-valued facts
CREATE TABLE EmployeeSkills (
    EmployeeID  VARCHAR(10),
    Skill       VARCHAR(50),
    PRIMARY KEY (EmployeeID, Skill)
);

CREATE TABLE EmployeeLanguages (
    EmployeeID  VARCHAR(10),
    Language    VARCHAR(50),
    PRIMARY KEY (EmployeeID, Language)
);

-- 2 + 2 rows here vs 2 x 2 combined; the gap grows with more skills or languages
INSERT INTO EmployeeSkills VALUES
('E001', 'Python'), ('E001', 'Java');

INSERT INTO EmployeeLanguages VALUES
('E001', 'English'), ('E001', 'Spanish');

5NF Fifth Normal Form

5NF (also called Project-Join Normal Form, or PJNF) deals with join dependencies. A table is in 5NF when:

1. It's in 4NF
2. It cannot be decomposed into smaller tables without losing information (no join dependencies except those implied by candidate keys)

Normal Forms Comparison

Denormalization: When to Break the Rules

Normalization protects integrity, but there are cases where copying data on purpose (denormalization) is the right call for performance. These are the usual ones:

-- Normalized: Calculate total on every query
SELECT 
    o.OrderID,
    SUM(oi.Quantity * oi.UnitPrice) AS Total
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.OrderID;

-- Denormalized: Store calculated total
CREATE TABLE Orders (
    OrderID     INT PRIMARY KEY,
    CustomerID  INT,
    OrderDate   DATETIME,
    TotalAmount DECIMAL(10,2)  -- Cached/denormalized
);

-- Update the cached total when items change
CREATE TRIGGER UpdateOrderTotal
AFTER INSERT ON OrderItems
FOR EACH ROW
BEGIN
    UPDATE Orders
    SET TotalAmount = (
        SELECT SUM(Quantity * UnitPrice)
        FROM OrderItems
        WHERE OrderID = NEW.OrderID
    )
    WHERE OrderID = NEW.OrderID;
END;

Notice the trigger above only fires AFTER INSERT. Update a quantity or delete a line item and TotalAmount is wrong, with no error anywhere. If you cache a value like this, you need matching AFTER UPDATE and AFTER DELETE triggers (or do it in one place in application code), plus a periodic job that recomputes totals and flags mismatches.

Practical Normalization Workflow

When I'm designing a schema from scratch, the process looks roughly like this:

How far I'd normalize (and when I wouldn't)

Where I wouldn't normalize: reporting tables and data warehouses, where data is loaded in batches and rarely updated, so update anomalies aren't really a risk and fewer JOINs make queries simpler. Event logs and audit tables, where you want a snapshot of the customer's address as it was at the time, not a foreign key to whatever it is today. And order line items, which should copy the product price at checkout. Pointing at products.price means old invoices change when you raise prices.

The mistake I see most often isn't under- or over-normalizing. It's denormalizing without a plan for keeping the copies in sync. If you copy a value, decide up front what updates it (a trigger, a model event, a queued job) and how you'd find out it drifted. And whichever way you go, index your foreign key columns. PostgreSQL doesn't create those indexes for you, and unindexed foreign keys are a common reason a normalized schema feels slow.