Database Design & Normalisation

Data often arrives as one wide spreadsheet that repeats the same facts on many rows — the club name and the teacher's phone on every member's row. Repetition causes anomalies: change the teacher's phone in one row and the others are now wrong; delete the last member and you lose the club itself.

Normalisation fixes this by giving each kind of thing its own table. First normal form: one value per cell, no lists in a column. Second and third normal form: every column depends on the whole key and nothing but the key — a teacher's phone belongs in teachers, not in the membership row.

Many-to-many relationships (students ↔ clubs) need a junction table holding pairs of foreign keys. Normalise first; add carefully chosen shortcuts (denormalisation, materialized views) later, only when measurements show you need them.

design.sqlSQL

-- The spreadsheet as it arrived: club and teacher details repeat on every row
CREATE TABLE club_signups_raw (
    student_name  text,
    club_name     text,
    club_teacher  text,
    teacher_phone text,
    joined_on     date
);
INSERT INTO club_signups_raw VALUES
    ('Amina Hassan',  'Debate',  'Mrs. Mrema',   '0688000003', '2026-01-20'),
    ('Neema Kimaro',  'Debate',  'Mrs. Mrema',   '0688000003', '2026-01-22'),
    ('Amina Hassan',  'Science', 'Ms. Lyimo',    '0754000002', '2026-02-01'),
    ('Ali Mohamed',   'Science', 'Ms. Lyimo',    '0754000002', '2026-02-03'),
    ('Zawadi Njau',   'Science', 'Ms. Lyimo',    '0754000002', '2026-02-03');

-- Normalised design: each fact stored once
ALTER TABLE teachers ADD COLUMN phone text;
UPDATE teachers t SET phone = r.teacher_phone
FROM (SELECT DISTINCT club_teacher, teacher_phone FROM club_signups_raw) r
WHERE t.full_name = r.club_teacher;

CREATE TABLE clubs (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text   NOT NULL UNIQUE,
    teacher_id bigint REFERENCES teachers (id)
);

CREATE TABLE club_members (                        -- junction table: many-to-many
    student_id bigint NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    club_id    bigint NOT NULL REFERENCES clubs (id) ON DELETE CASCADE,
    joined_on  date   NOT NULL,
    PRIMARY KEY (student_id, club_id)
);

INSERT INTO clubs (name, teacher_id)
SELECT DISTINCT r.club_name, t.id
FROM club_signups_raw r
JOIN teachers t ON t.full_name = r.club_teacher;

INSERT INTO club_members (student_id, club_id, joined_on)
SELECT s.id, c.id, r.joined_on
FROM club_signups_raw r
JOIN students s ON s.full_name = r.student_name
JOIN clubs c    ON c.name = r.club_name;

-- The original view of the data, rebuilt with joins
SELECT c.name AS club, t.full_name AS teacher, t.phone, count(m.student_id) AS members
FROM clubs c
JOIN teachers t          ON t.id = c.teacher_id
LEFT JOIN club_members m ON m.club_id = c.id
GROUP BY c.name, t.full_name, t.phone
ORDER BY c.name;

-- Changing a phone number is now one update, not one per row
UPDATE teachers SET phone = '0755111222' WHERE full_name = 'Ms. Lyimo';

Runs in your browser · PostgreSQL

Key points

Store each fact once; repetition leads to update, insert and delete anomalies.
Columns must depend on the key of their own table — move the rest to the right table.
Model many-to-many relationships with a junction table of foreign-key pairs.

Exercise

A messy book_loans_raw sheet has columns: student_name, student_form, book_title, book_author, borrowed_on, returned_on. Design normalised tables (books, loans) with keys and constraints, create them, and write the INSERT ... SELECT statements to fill them from a few sample rows.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Books get their own table (title and author stored once), and each loan is a row linking a student to a book, with its own dates. A CHECK makes sure a book can't be returned before it was borrowed.

loans-solution.sqlSQL

CREATE TABLE book_loans_raw (
    student_name text, student_form smallint, book_title text, book_author text,
    borrowed_on date, returned_on date
);
INSERT INTO book_loans_raw VALUES
    ('Amina Hassan', 4, 'Things Fall Apart', 'Chinua Achebe',   '2026-02-02', '2026-02-16'),
    ('Juma Said',    4, 'Things Fall Apart', 'Chinua Achebe',   '2026-02-20', NULL),
    ('Amina Hassan', 4, 'Kinjeketile',       'Ebrahim Hussein', '2026-03-01', NULL);

CREATE TABLE books (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title  text NOT NULL,
    author text NOT NULL,
    UNIQUE (title, author)
);

CREATE TABLE loans (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  bigint NOT NULL REFERENCES students (id),
    book_id     bigint NOT NULL REFERENCES books (id),
    borrowed_on date   NOT NULL,
    returned_on date,
    CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);

INSERT INTO books (title, author)
SELECT DISTINCT book_title, book_author FROM book_loans_raw;

INSERT INTO loans (student_id, book_id, borrowed_on, returned_on)
SELECT s.id, b.id, r.borrowed_on, r.returned_on
FROM book_loans_raw r
JOIN students s ON s.full_name = r.student_name
JOIN books b    ON b.title = r.book_title AND b.author = r.book_author;

-- Books currently out, and who has them
SELECT b.title, s.full_name, l.borrowed_on
FROM loans l
JOIN books b    ON b.id = l.book_id
JOIN students s ON s.id = l.student_id
WHERE l.returned_on IS NULL
ORDER BY l.borrowed_on;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding