BrevFeed
Sign in

52. Capstone: design a normalized schema

📖 Reading · 6 min

The brief

You're building the database for a small community library system (deliberately not pagila — a fresh domain, to test the modeling skill itself, not memory of an existing schema). Requirements, gathered from stakeholders:

  • The library has multiple branches. Each branch has a name and an address.
  • The library owns books. A book has a title, an ISBN, and one or more authors (some books are co-authored; some authors have written many books — a genuine many-to-many relationship).
  • Each physical copy of a book lives at exactly one branch, and has its own condition status (new, good, worn, damaged). The library may own several physical copies of the same book, at the same or different branches.
  • Members can join at any branch, have a name, email, and a membership start date.
  • A member can check out a physical copy — the system needs to know who has which copy, when it was checked out, when it's due, and when (if) it was returned. A copy can't be checked out to two people at once.
  • The library wants to track late fees: a small charge per day overdue, applied when a copy is returned late.
  • Every checkout must be tied to a specific member, a specific physical copy, and (derivable) the branch that copy belongs to.

Your task

Before looking at the reference solution below, actually do the design work — this is the entire point of a capstone. Work through it in order:

  1. Identify the entities (Module 8's ER-modeling lesson) — what are the "nouns" this brief describes? Which ones need their own identity vs. are just attributes of another entity?
  2. Identify the relationships and their cardinality (Module 8's ER-modeling lesson) — which are 1:N, which are genuinely M:N (and therefore need a junction table)?
  3. Normalize (Module 8's normalization lesson) — check your design against 1NF/2NF/3NF. Is there anywhere you're tempted to store a list in one column, or duplicate a value that belongs somewhere else?
  4. Choose keys — surrogate vs. natural, where does a composite key genuinely make sense (hint: exactly one place in this brief)?
  5. Write the DDL (Module 2) — real CREATE TABLE statements, real data types, NOT NULL where appropriate.
  6. Add constraints (Module 2's constraints lesson) — what business rules from the brief can be enforced at the schema level? (Hint: "a copy can't be checked out to two people at once" is a real, enforceable constraint, not just application logic — think about what makes a checkout row "currently active.")

Reference solution

The entities: branch, author, book, book_copy, member, checkout. author↔book is the one genuine many-to-many relationship, needing a book_author junction table (Module 8) — everything else is 1:N.

CREATE TABLE branch (
    branch_id serial PRIMARY KEY,
    name      text NOT NULL,
    address   text NOT NULL
);

CREATE TABLE author (
    author_id serial PRIMARY KEY,
    name      text NOT NULL
);

CREATE TABLE book (
    book_id serial PRIMARY KEY,
    title   text NOT NULL,
    isbn    text UNIQUE NOT NULL
);

-- Pure junction table: composite key, no attributes of its own
-- (this is exactly the case where a composite key beats a
-- surrogate one).
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)
);

CREATE TABLE book_copy (
    copy_id   serial PRIMARY KEY,
    book_id   integer NOT NULL REFERENCES book(book_id),
    branch_id integer NOT NULL REFERENCES branch(branch_id),
    condition text NOT NULL CHECK (condition IN ('new', 'good', 'worn', 'damaged'))
);

CREATE TABLE member (
    member_id serial PRIMARY KEY,
    branch_id integer NOT NULL REFERENCES branch(branch_id),
    name      text NOT NULL,
    email     text UNIQUE NOT NULL,
    joined_on date NOT NULL DEFAULT current_date
);

CREATE TABLE checkout (
    checkout_id  serial PRIMARY KEY,
    copy_id      integer NOT NULL REFERENCES book_copy(copy_id),
    member_id    integer NOT NULL REFERENCES member(member_id),
    checked_out_on date NOT NULL DEFAULT current_date,
    due_on       date NOT NULL,
    returned_on  date,
    late_fee     numeric(6,2) NOT NULL DEFAULT 0 CHECK (late_fee >= 0),
    CHECK (returned_on IS NULL OR returned_on >= checked_out_on)
);

-- The "can't be checked out to two people at once" rule: a copy can
-- have at most ONE active (not yet returned) checkout at a time.
-- A partial UNIQUE index (Module 10's partial-index pattern, repurposed as a
-- constraint) enforces this directly at the schema level:
CREATE UNIQUE INDEX one_active_checkout_per_copy
    ON checkout (copy_id) WHERE returned_on IS NULL;

Notice branch_id on book_copy (where the physical item lives) is independent of anything on book (title/ISBN are properties of the work, not any specific copy) — this separation is exactly what lets the library own five copies of the same title across three different branches, each with its own condition and checkout history, without any duplication of the book's own metadata (1NF/3NF, Module 8).

Check yourself

  1. Why does book_author need a composite key rather than its own surrogate id, while checkout gets its own surrogate key instead of a composite one?
  2. What normal form violation would you be committing if you added an author_names text column directly to book instead of the book_author junction table?
  3. Explain, in your own words, how the partial unique index enforces "a copy can't be checked out to two people at once" — what makes it specifically a partial index rather than a plain UNIQUE (copy_id)?
← Previous Next →