Relational Modelling: From Entities to Normal Forms

BeforeBefore 1970, a database's internal storage layout and the way applications queried it were tightly coupled — change one and the other broke. Edgar Codd's proposal was to describe data purely as relations (mathematically, sets of tuples) and let applications interact with that logical structure through a query language, leaving the physical storage free to change underneath without breaking anything built on top¹. That separation — logical structure independent of physical storage — is still the reason a relational database feels stable to build against even as the engine underneath it changes.

Getting from "a real-world domain" to "a set of well-designed relations" is a two-stage process. The first stage is modelling — working out what the entities, attributes, and relationships actually are, usually as an Entity-Relationship (E-R) diagram². The second is normalization — mechanically checking that the relations derived from that model don't carry the specific structural flaws that cause data to become inconsistent as it's updated.

Entities, Attributes, and Relationships

AnAn entity is a distinguishable thing the database needs to store facts about — a book, a member, a loan. An attribute is a fact about an entity (a title, a name, a due date). A relationship connects two entities, and its cardinality states how many instances of each side can participate: one-to-one, one-to-many, or many-to-many.

Two cardinalities are doing real work in that diagram. MEMBER ||--o{ LOAN reads as "one member relates to zero-or-many loans" — a one-to-many relationship. BOOK }o--|| AUTHOR reads as "many books relate to exactly one author" here, a simplification that deliberately ignores co-authorship to keep the running example small — a real library catalogue would model authorship as many-to-many instead, exactly the case covered next.

From Diagram to Schema

TurningTurning entities and relationships into tables follows a small set of mechanical rules:

The one-to-many rule is visible directly in LOAN above: member_id and book_id are foreign keys sitting on the "many" side of each relationship, which is why a loan row can name exactly one member and one book, while a member or a book can appear in many loan rows. A many-to-many relationship — co-authorship, say, where a book can have several authors and an author can write several books — can't be expressed that way, because neither side has a single foreign key that would work; it needs a junction table of its own, holding one row per (book, author) pairing:

CREATE TABLE book (
    book_id    INTEGER PRIMARY KEY,
    isbn       TEXT NOT NULL,
    title      TEXT NOT NULL
);

CREATE TABLE author (
    author_id  INTEGER PRIMARY KEY,
    name       TEXT NOT NULL
);

-- Junction table: one row per (book, author) pairing
CREATE TABLE book_author (
    book_id    INTEGER NOT NULL REFERENCES book(book_id),
    author_id  INTEGER NOT NULL REFERENCES author(author_id),
    PRIMARY KEY (book_id, author_id)
);

The junction table's own primary key is the pair of foreign keys — nothing else about a co-authorship needs recording, so nothing else is there. This is the relational answer to a question that a class diagram answers differently: where a UML class diagram would draw a many-to-many association directly as a single line with multiplicities on each end, a relational schema always spells the same relationship out as its own table, because a foreign key column can only ever point at one row.

Normalization

AA schema can follow every rule above and still be badly designed, in a specific, checkable sense: it can force the same fact to be written down in more than one place, which creates the chance for two copies of that fact to disagree after an update. Normalization is a sequence of tests — normal forms — each one ruling out a particular way that can happen³.

Start from a single, unnormalized table recording library loans, with every fact about a loan crammed into one row:

-- Unnormalized: repeating groups and redundant facts
CREATE TABLE loan_unnormalized (
    loan_id       INTEGER,
    member_name   TEXT,
    member_email  TEXT,
    books         TEXT,   -- 'Dune, Foundation, Neuromancer' — a repeating group in one field
    due_dates     TEXT     -- '2026-10-01, 2026-10-01, 2026-10-15' — parallel, and just as fragile
);

First Normal Form (1NF) requires every column to hold a single, atomic value — no repeating groups, no comma-separated lists standing in for a table of their own. Splitting books and due_dates into one row per book fixes that, but introduces a composite key (loan_id, book_id) to identify a single row:

-- 1NF: atomic values, one fact per column
CREATE TABLE loan_1nf (
    loan_id       INTEGER,
    book_id       INTEGER,
    member_name   TEXT,
    member_email  TEXT,
    due_date      DATE,
    PRIMARY KEY (loan_id, book_id)
);

Second Normal Form (2NF) requires 1NF, plus: every non-key column must depend on the whole of a composite primary key, not just part of it. Here, member_name and member_email depend only on loan_id (which member placed the loan), not on book_id at all — a partial dependency, and the tell-tale sign of it is that the same member's name and email get repeated on every book-row of the same loan. Pulling the member's details out into their own table, keyed on something that identifies the member rather than the loan, removes the partial dependency:

-- 2NF: no non-key column depends on only part of the composite key
CREATE TABLE member (
    member_id    INTEGER PRIMARY KEY,
    member_name  TEXT,
    member_email TEXT
);

CREATE TABLE loan_2nf (
    loan_id    INTEGER,
    book_id    INTEGER,
    member_id  INTEGER REFERENCES member(member_id),
    due_date   DATE,
    PRIMARY KEY (loan_id, book_id)
);

Third Normal Form (3NF) requires 2NF, plus: no non-key column may depend on another non-key column instead of the key. Suppose loan_2nf also carried a late_fee_rate column that was really determined by which branch the loan was made at, not by the loan itself — that's a transitive dependency (loan_id → branch_id → late_fee_rate), and it means the same branch's fee rate is duplicated across every loan made there, with the same risk of the copies drifting apart after an update. The fix is the same move as before: give branch its own table, and reference it by key rather than repeating its facts everywhere it's relevant.

The Trade-Off

NormalizationNormalization isn't a virtue to maximise without limit — it's a specific answer to a specific problem (update anomalies), and it trades that safety for extra JOINs at read time, since a fact that used to sit in one row now has to be looked up from another table. For a system that's written to constantly and read occasionally — the library's own loan records — that trade is usually worth making. For a system that's read constantly and written rarely — a reporting dashboard querying last year's completed loans — deliberately denormalizing parts of the schema (accepting some redundancy back in exchange for fewer joins) is a legitimate, common design choice, not a mistake. The normal forms tell you what redundancy costs; they don't tell you it's never worth paying.

The PatLang SQL console runs a small hand-built relational engine — table creation, foreign keys, transactions with BEGIN/COMMIT/ROLLBACK — directly in the page, and is a reasonable place to try the schemas above against a real (if small) SQL implementation rather than just reading them.

References