SpunkArt
Data, text and encoding

Designing a relational database schema

A schema is the plan for your tables: what they are, which columns they hold, and how they point at each other. A good one makes wrong data hard to store. This guide designs a small library-loans database from a messy spreadsheet, one decision at a time.

Start from the flat spreadsheet

Most schemas begin as one wide sheet. Here is a library’s loan log:

membermember_emailbook_titleauthorsloaned_onreturned_on
R. Okaforr.okafor@example.orgPatterns of LightA. Moss, T. Brandt2030-03-022030-03-16
R. Okaforr.okafor@example.orgSalt RoadsJ. Ferro2030-03-20
M. Lindm.lind@example.orgPatterns of LightA. Moss, T. Brandt2030-03-21

(All names and data here are invented for the example.) Three problems show up at once. The member’s email is repeated, so changing it means editing many rows and missing one. The authors column holds a list, so you cannot easily ask “which books did T. Brandt write?”. And a book that has never been borrowed cannot exist in this table at all.

Step 1: name the entities

An entity is a thing you store facts about. Read the sheet and underline the nouns that have their own attributes: member, book, author, and loan. Each becomes a table. Attributes that describe only one entity move into that table: the email belongs to the member, the title to the book.

Step 2: give each table a primary key

A primary key identifies one row and never changes. Names and emails are poor keys because people change them. Use a surrogate key instead: an auto-incrementing integer, or a UUID if ids are created on many machines (the UUID guide covers the trade-offs). Keep real-world identifiers such as an email as ordinary columns with a UNIQUE constraint.

Step 3: model the relationships

RelationshipExampleHow to store it
One-to-manyOne member has many loansA foreign key member_id on the many side (loans)
Many-to-manyA book has many authors; an author writes many booksA junction table book_authors(book_id, author_id)
One-to-oneA member has one library card recordA foreign key with a UNIQUE constraint, or simply extra columns on the same table

The many-to-many case is the one people get wrong. A comma-separated authors column or columns named author1, author2, author3 both break as soon as a book has a fourth author.

Step 4: check the normal forms

  • First normal form (1NF): every cell holds one value. The authors list fails this; the junction table fixes it.
  • Second normal form (2NF): in a table with a composite key, every column depends on the whole key. A book_title column inside book_authors would depend only on book_id, so it belongs in books.
  • Third normal form (3NF): no column depends on another non-key column. If loans stored the member’s email, the email would depend on member_id, not on the loan.

A short memory aid: every column should depend on the key, the whole key, and nothing but the key.

Step 5: write it down with constraints

CREATE TABLE members (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE
);

CREATE TABLE books (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title      TEXT NOT NULL
);

CREATE TABLE authors (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       TEXT NOT NULL
);

CREATE TABLE book_authors (
    book_id    BIGINT NOT NULL REFERENCES books(id),
    author_id  BIGINT NOT NULL REFERENCES authors(id),
    PRIMARY KEY (book_id, author_id)
);

CREATE TABLE loans (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_id   BIGINT NOT NULL REFERENCES members(id),
    book_id     BIGINT NOT NULL REFERENCES books(id),
    loaned_on   DATE NOT NULL,
    returned_on DATE,
    CHECK (returned_on IS NULL OR returned_on >= loaned_on)
);

CREATE INDEX loans_member_idx ON loans (member_id);

The syntax shown is PostgreSQL; other databases differ slightly. Each constraint blocks a class of bad data: NOT NULL stops missing values, UNIQUE stops two members sharing an email, REFERENCES stops a loan for a book that does not exist, and the CHECK stops a return dated before the loan. An empty returned_on now has one clear meaning: the book is still out.

Naming and type habits

  • Choose plural or singular table names and stick to it; use snake_case to avoid quoting.
  • Name foreign keys after the table they point to: member_id, book_id.
  • Store dates as DATE or timestamps with a time zone, never as text.
  • Store money as a fixed-point NUMERIC or as whole cents, never as a floating-point type.
  • Index every foreign key you filter or join on; many databases do not do this for you.

Questions

Should I ever denormalise?

Sometimes, for read-heavy reporting, you copy data into a wider table on purpose. Do it as a deliberate, documented step after the normalised design works, not as the starting point.

Is there an interactive designer on this page?

No. Sketch the entities and arrows on paper or in any diagram tool first; the thinking above is the hard part, and the CREATE TABLE statements follow from it.