First, Second and Third Normal Form

Concept

Normalisation is the process of restructuring tables so each fact is stored exactly once. Each normal form removes one specific kind of duplication, and they build on one another: a table in 3NF is already in 2NF and 1NF.

The starting point

-- Everything in one table. Every anomaly lives here.
CREATE TABLE enrolment_flat (
    student_id   INT,
    student_name VARCHAR(60),
    courses      VARCHAR(200),   -- 'maths,physics,chemistry'
    dept_id      INT,
    dept_name    VARCHAR(60),
    dept_head    VARCHAR(60)
);

First normal form (1NF)

Every column holds a single, atomic value. No repeating groups, no multi valued columns, and every row is unique.

-- The courses column violates 1NF: three values in one cell
-- Fix: one row per student per course
CREATE TABLE students_1nf (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(60) NOT NULL,
    dept_id      INT,
    dept_name    VARCHAR(60),
    dept_head    VARCHAR(60)
);

CREATE TABLE enrolments_1nf (
    student_id INT,
    course     VARCHAR(50),
    PRIMARY KEY (student_id, course)
);

Why it matters: with the comma separated column, how many students take physics? requires string matching that cannot use an index and breaks on any name containing another name. After the split it is a one line GROUP BY.

Second normal form (2NF)

In 1NF, and every non key column depends on the whole primary key - not on part of it. Only composite keys can violate this.

-- Suppose we had put the grade AND the student name in the junction table
CREATE TABLE enrolments_bad (
    student_id   INT,
    course       VARCHAR(50),
    grade        CHAR(2),
    student_name VARCHAR(60),      -- depends on student_id ONLY: partial dependency
    PRIMARY KEY (student_id, course)
);

-- 2NF: the name belongs with the student, the grade with the pairing
CREATE TABLE enrolments_2nf (
    student_id INT,
    course     VARCHAR(50),
    grade      CHAR(2),
    PRIMARY KEY (student_id, course)
);

grade genuinely depends on both halves of the key - a grade is for this student in this course - so it stays. student_name depends only on student_id, so it is repeated once per enrolment and can be updated inconsistently. It belongs in the students table.

Third normal form (3NF)

In 2NF, and no non key column depends on another non key column. No transitive dependencies.

-- students_1nf still violates 3NF:
--   student_id -> dept_id -> dept_name, dept_head
-- The department name depends on the department, not on the student.

CREATE TABLE departments_3nf (
    id   INT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    head VARCHAR(60)
);

CREATE TABLE students_3nf (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(60) NOT NULL,
    dept_id      INT NULL,
    CONSTRAINT fk_students_dept FOREIGN KEY (dept_id) REFERENCES departments_3nf(id)
);

Explanation

Follow one change through the design. The head of the Engineering department leaves, and a new one is appointed.

• Before: update every student row in Engineering. Miss one and the database now claims two people run the same department.
• After: one UPDATE of one row in departments_3nf. It is impossible to be inconsistent, because there is nowhere else to be inconsistent.

That is the entire benefit of normalisation: one fact, one place, one update.

A shortcut for exams and interviews

Every non key attribute must depend on the key (1NF and entity integrity), the whole key (2NF), and nothing but the key (3NF).

Important rules

• The normal forms are cumulative: 3NF implies 2NF implies 1NF.
• 2NF can only be violated by a table with a composite primary key.
• 3NF violations show up as a column that describes something other than the row's subject.
• Normalisation is about functional dependencies, not about table count. Splitting arbitrarily does not normalise anything.
• A normalised design needs more joins. That is the trade, and it is usually the right one.

Common mistakes

• Calling a table normalised because it has few columns.
• Leaving a comma separated list because "the application splits it anyway".
• Copying a descriptive name onto the child table to avoid a join, then letting the two drift apart.
• Normalising past 3NF in a transactional system without a concrete reason.
• Confusing normalisation with splitting a table for performance - they are different activities with different justifications.

Best practices

• Design to 3NF first, then measure, then denormalise deliberately where measurement justifies it.
• Write the functional dependencies out as sentences before deciding where a column belongs.
• Ask of every column: what does this describe? If the answer is not the row's subject, it is in the wrong table.
• Use foreign keys to enforce the structure the normalisation created.

Practice

1. Normalise invoice(id, date, customer_name, customer_city, product_name, product_price, qty) to 3NF.
2. Which normal form does a table with primary key (order_id, product_id) and a customer_name column violate?
3. Explain why order_items.unit_price is not a 3NF violation, even though products.price exists.