Normalization: Bringing order to data

Imagine a university table where one cell lists all of a student’s courses separated by commas, and phone and address are mixed together. If someone changes their last name or moves, you would have to find and fix that chain in many places. Normalization is the deliberate splitting of data into tables and relationships to remove duplication, prevent contradictions, and simplify database maintenance.

Why bother? Three classes of anomalies
Open a card — what breaks without normalization
The normalization path: 1NF → 2NF → 3NF
Click the steps — decomposition order
1NF
Atomicity — remove lists inside cells
2NF
Whole key — remove partial dependencies
3NF
Only from the key — remove transitive dependencies
▶
First normal form (1NF)

The 1NF rule is simple: each cell holds one indivisible value from a domain. No “comma-separated lists” in one field — use separate rows or a separate table for that.

1NF fix — “before” version
sql
1
-- Bad: 1NF violation — multiple phones in one cell
▼
2
CREATE TABLE students_bad (
▼
3
    id          SERIAL PRIMARY KEY,
▼
4
    full_name   TEXT NOT NULL,
▼
5
    phones      TEXT  -- '+7999..., +7911...'
▼
6
);
▼
1NF fix — “after” version
sql
1
-- Good: 1NF — one phone per row
▼
2
CREATE TABLE students (
▼
3
    id        SERIAL PRIMARY KEY,
▼
4
    full_name TEXT NOT NULL
▼
5
);
▼
6
CREATE TABLE student_phones (
▼
7
    id         SERIAL PRIMARY KEY,
▼
8
    student_id INT NOT NULL REFERENCES students(id),
▼
9
    phone      TEXT NOT NULL
▼
10
);
▼
Second normal form (2NF)

2NF matters when the primary key is composite. Every non-key attribute must depend on the entire key, not part of it. If the key is the pair “student + course,” then course duration depends only on the course: storing it on the enrollment row means copying the same value and risking drift.

2NF fix — “before” version
sql
1
-- Bad: partial dependency on a composite key
▼
2
CREATE TABLE enrollments_bad (
▼
3
    student_id   INT NOT NULL,
▼
4
    course_id    INT NOT NULL,
▼
5
    enrolled_at  DATE NOT NULL,
▼
6
    course_duration_hours INT NOT NULL, -- duplicate per student
▼
7
    PRIMARY KEY (student_id, course_id)
▼
8
);
▼
2NF fix — “after” version
sql
1
-- Good: duration stored once, in the course catalog
▼
2
CREATE TABLE courses (
▼
3
    id          SERIAL PRIMARY KEY,
▼
4
    title       TEXT NOT NULL,
▼
5
    duration_hours INT NOT NULL
▼
6
);
▼
7
CREATE TABLE enrollments (
▼
8
    student_id  INT NOT NULL,
▼
9
    course_id   INT NOT NULL REFERENCES courses(id),
▼
10
    enrolled_at DATE NOT NULL,
▼
11
    PRIMARY KEY (student_id, course_id)
▼
12
);
▼
Third normal form (3NF)

3NF forbids transitive dependencies: a non-key column must not depend on another non-key column. If a student has `cityid` and `cityname` is duplicated next to it, the city name depends on the city identifier, not on the student’s primary key. The name should live in a separate lookup table.

3NF fix — “before” version
sql
1
-- Bad: city_name depends transitively on city_id, not on student id
▼
2
CREATE TABLE students_bad_city (
▼
3
    id         SERIAL PRIMARY KEY,
▼
4
    full_name  TEXT NOT NULL,
▼
5
    city_id    INT NOT NULL,
▼
6
    city_name  TEXT NOT NULL  -- duplicate lookup
▼
7
);
▼
3NF fix — “after” version
sql
1
-- Good: city lookup; student has only city_id
▼
2
CREATE TABLE cities (
▼
3
    id        SERIAL PRIMARY KEY,
▼
4
    name      TEXT NOT NULL
▼
5
);
▼
6
CREATE TABLE students (
▼
7
    id        SERIAL PRIMARY KEY,
▼
8
    full_name TEXT NOT NULL,
▼
9
    city_id   INT NOT NULL REFERENCES cities(id)
▼
10
);
▼
Cheat sheet

1NF • 2NF • 3NF at a glance

Normalization is neither dogma nor endless splitting: it removes redundancy until insert, delete, and update anomalies stop threatening the data. For teaching OLTP schemas, 3NF is often a reasonable starting point.
1NF: indivisible value in each cell
2NF: non-key attributes depend on the whole primary key
3NF: non-key attributes depend only on the key, not on each other
Fewer redundant copies — less drift and easier maintenance