DBMS

Database Design

From ER to Relational Schema

Apply them in order and the schema falls out.

JrCodex·7 min read

Jr Codex DBMS Notes

Level: Intermediate Prerequisites: Chapter 1: Entity-Relationship Modelling Time to complete: ~20 minutes

Table of Contents

  1. A Mechanical Translation
  2. Rule 1 — Strong Entities
  3. Rule 2 — One-to-Many
  4. Rule 3 — Many-to-Many
  5. Rule 4 — One-to-One
  6. Rules 5-7 — The Remaining Cases
  7. The Library Schema, Complete
  8. Summary & Next Steps

1. A Mechanical Translation

Seven Rules
─────────────────────────────────────────
  1. Strong entity      ──►  a table
  2. 1:N relationship   ──►  a foreign key on the
                             MANY side
  3. M:N relationship   ──►  a junction table
  4. 1:1 relationship   ──►  a foreign key on
                             either side, UNIQUE
  5. Weak entity        ──►  a table with a
                             composite key
  6. Multivalued attr   ──►  its own table
  7. Composite attr     ──►  flatten into columns

  Apply them in order and the schema falls out.
  The design thinking happened in Chapter 1; this
  part is procedure.
─────────────────────────────────────────

2. Rule 1 — Strong Entities

Each becomes a table. Simple attributes become columns; the key attribute becomes the primary key.

CREATE TABLE books (
    id    INTEGER PRIMARY KEY,           -- surrogate (Module 2, Ch.2)
    isbn  TEXT NOT NULL UNIQUE,          -- the natural key, still enforced
    title TEXT NOT NULL
);
 
CREATE TABLE members (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    email     TEXT NOT NULL UNIQUE,
    joined_on TEXT NOT NULL DEFAULT (date('now'))
);

3. Rule 2 — One-to-Many

The foreign key goes on the many side. Always.

Why the Many Side
─────────────────────────────────────────
  One library has many books.

  Put library_id on BOOKS: one value per row. ✓

  Put book_ids on LIBRARIES: you would need a list
  in one column — a multivalued attribute, which
  violates atomicity (Module 2, Chapter 1).

  There is only one workable direction, and this
  is it.
─────────────────────────────────────────

CREATE TABLE books (
    id         INTEGER PRIMARY KEY,
    isbn       TEXT NOT NULL UNIQUE,
    title      TEXT NOT NULL,
    library_id INTEGER NOT NULL REFERENCES libraries(id)   -- FK on the MANY side
);
Participation Sets Nullability
─────────────────────────────────────────
  TOTAL participation   ──► NOT NULL
    every book belongs to a library

  PARTIAL participation ──► nullable
    an order may not yet have a courier assigned

  This is Chapter 1's participation decision
  becoming a column definition.
─────────────────────────────────────────

4. Rule 3 — Many-to-Many

A many-to-many relationship always becomes its own table. There is no alternative.

CREATE TABLE borrowings (
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    copy_id     INTEGER NOT NULL REFERENCES copies(id)  ON DELETE RESTRICT,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    returned_on TEXT,                                   -- NULL = still out
    PRIMARY KEY (member_id, copy_id, borrowed_on),      -- allows RE-borrowing later
    CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);
The Junction Table
─────────────────────────────────────────
  Also called a bridge, link, or associative table.

  - Its primary key is COMPOSITE, built from the
    two foreign keys
  - Relationship attributes live HERE — borrowed_on
    and returned_on describe the pairing
  - It turns one M:N into two 1:N relationships,
    which is the only shape tables can express
─────────────────────────────────────────

The Key Decision Worth Thinking About
─────────────────────────────────────────
  PRIMARY KEY (member_id, copy_id)
    A member may borrow a given copy ONCE, ever.
    Re-borrowing next year is impossible.

  PRIMARY KEY (member_id, copy_id, borrowed_on)
    The same pair may recur on different dates.
    ── correct for a library

  The composite key encodes a real business rule.
  Choose it deliberately rather than by habit.
─────────────────────────────────────────

5. Rule 4 — One-to-One

-- Option A: foreign key on either side, with UNIQUE to enforce the "one".
CREATE TABLE members (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE member_cards (
    id        INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL UNIQUE REFERENCES members(id) ON DELETE CASCADE,
    issued_on TEXT NOT NULL
);
The UNIQUE Is What Makes It 1:1
─────────────────────────────────────────
  Without UNIQUE on member_id, this is 1:N — a
  member could have several cards.

  A one-to-one relationship is a one-to-many
  relationship with a uniqueness constraint. That
  is the entire difference.
─────────────────────────────────────────

When to Merge Instead
─────────────────────────────────────────
  A genuine 1:1 with TOTAL participation on both
  sides is usually just one table with more
  columns.

  Keep them separate when:
    - one side is OPTIONAL (not every member has a
      card)
    - the columns are large and rarely read (split
      a blob out so the main table's rows stay
      small — Module 5, Chapter 1)
    - access permissions differ (salary in a
      separate table)
─────────────────────────────────────────

6. Rules 5-7 — The Remaining Cases

-- Rule 5: WEAK ENTITY — key includes the parent's key.
CREATE TABLE copies (
    book_id  INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    copy_no  INTEGER NOT NULL,                      -- the local discriminator
    barcode  TEXT    NOT NULL UNIQUE,
    condition TEXT   NOT NULL DEFAULT 'good'
                     CHECK (condition IN ('good','fair','poor','lost')),
    PRIMARY KEY (book_id, copy_no)                  -- composite: parent + discriminator
);
-- Rule 6: MULTIVALUED ATTRIBUTE — its own table.
CREATE TABLE member_phones (
    member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    phone     TEXT    NOT NULL,
    kind      TEXT    NOT NULL DEFAULT 'mobile'
                      CHECK (kind IN ('mobile','home','work')),
    PRIMARY KEY (member_id, phone)
);
-- Rule 7: COMPOSITE ATTRIBUTE — flatten into columns.
-- address(street, city, postcode) becomes:
ALTER TABLE members ADD COLUMN street   TEXT;
ALTER TABLE members ADD COLUMN city     TEXT;
ALTER TABLE members ADD COLUMN postcode TEXT;
Flatten or Extract?
─────────────────────────────────────────
  FLATTEN into columns when there is exactly ONE
  address per member and you query the parts.

  EXTRACT into an addresses table when a member can
  have SEVERAL (billing, delivery) — at which point
  it was multivalued, and Rule 6 applies instead.

  Ask "how many?" before "what shape?"
─────────────────────────────────────────

7. The Library Schema, Complete

CREATE TABLE libraries (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);
 
CREATE TABLE books (                                    -- Rule 1 + Rule 2
    id         INTEGER PRIMARY KEY,
    isbn       TEXT    NOT NULL UNIQUE,
    title      TEXT    NOT NULL,
    author     TEXT    NOT NULL,
    library_id INTEGER NOT NULL REFERENCES libraries(id) ON DELETE RESTRICT
);
 
CREATE TABLE copies (                                   -- Rule 5: weak entity
    book_id   INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    copy_no   INTEGER NOT NULL,
    barcode   TEXT    NOT NULL UNIQUE,
    condition TEXT    NOT NULL DEFAULT 'good',
    PRIMARY KEY (book_id, copy_no)
);
 
CREATE TABLE members (                                  -- Rule 1 + Rule 7
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    email     TEXT NOT NULL UNIQUE,
    street    TEXT,
    city      TEXT,
    postcode  TEXT,
    joined_on TEXT NOT NULL DEFAULT (date('now'))
);
 
CREATE TABLE member_phones (                            -- Rule 6: multivalued
    member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    phone     TEXT    NOT NULL,
    PRIMARY KEY (member_id, phone)
);
 
CREATE TABLE borrowings (                               -- Rule 3: M:N junction
    member_id   INTEGER NOT NULL REFERENCES members(id) ON DELETE RESTRICT,
    book_id     INTEGER NOT NULL,
    copy_no     INTEGER NOT NULL,
    borrowed_on TEXT    NOT NULL DEFAULT (date('now')),
    returned_on TEXT,
    PRIMARY KEY (member_id, book_id, copy_no, borrowed_on),
    FOREIGN KEY (book_id, copy_no) REFERENCES copies(book_id, copy_no)
);
Note the Composite Foreign Key
─────────────────────────────────────────
  copies has a COMPOSITE primary key
  (book_id, copy_no), so anything referencing a
  copy must carry BOTH columns.

  This is the practical cost of weak entities with
  composite keys, and the reason many teams give
  copies its own surrogate id instead — a smaller,
  simpler reference everywhere else.

  Both designs are defensible. Know which cost you
  are paying.
─────────────────────────────────────────

8. Summary & Next Steps

Key Takeaways

  • Seven mechanical rules turn an ER diagram into tables; the design thinking belongs to the diagram, and this step is procedure.
  • A one-to-many foreign key must go on the many side, because the alternative requires a list in a column.
  • Many-to-many always becomes a junction table, whose composite primary key encodes a real business rule about repetition.
  • A one-to-one relationship is one-to-many plus a UNIQUE constraint, and is often better merged into a single table unless one side is optional or large.

Concept Check

  1. Why can a one-to-many relationship's foreign key only go on the many side?
  2. What changes about the library's behaviour if borrowings is keyed on (member_id, copy_id) instead of including borrowed_on?
  3. What makes a one-to-one relationship different from one-to-many at the schema level?

Next Chapter

→ Chapter 3: Functional Dependencies

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index