Chapter 25: Database Design Patterns

Relational design patterns help you express business rules as tables, keys, and constraints. This chapter focuses on the most common relationship shapes and the practical details that keep data consistent (and queries fast). You will see how to model one to many and many to many, how to implement junction tables for flexibility, and how to plan safe schema changes with versioned migrations.

One-to-many relationships

In a one to many relationship, a single parent row is associated with zero or more child rows. Typical examples are authors to books, or departments to employees. The usual implementation is a primary key on the parent and a foreign key from the child to that parent.

Canonical layout for 1:N

-- Parent
CREATE TABLE authors (
  author_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Child (many side)
CREATE TABLE books (
  book_id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   INTEGER NOT NULL,
  title       VARCHAR(300) NOT NULL,
  published_on DATE,
  CONSTRAINT fk_books_author
    FOREIGN KEY (author_id)
    REFERENCES authors(author_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

This pattern keeps the relationship in the child, which simplifies inserts for new children and avoids nullable columns in the parent.

Choosing ON DELETE behavior

Choose a deletion rule that matches your business rules. The following table summarizes common options.

Indexing FOREIGN KEY columns

Always index the child foreign key column for fast joins and to avoid table scans during parent deletes or updates.

CREATE INDEX ix_books_author_id ON books(author_id);

The parent primary key is already indexed by the primary key. The child index helps both JOIN performance and referential checks.

Enforcing optional vs required relationships

Use NOT NULL on the child foreign key to make the relationship required. Allow NULL for optional relationships.

-- Required relationship
ALTER TABLE books
  ALTER COLUMN author_id SET NOT NULL;  -- Dialect may vary …

Many-to-many relationships

Many to many arises when multiple rows on each side can relate to multiple rows on the other side. Examples include students and courses, or posts and tags. Implement this with a dedicated junction table that holds pairs of foreign keys.

Canonical layout for M:N

CREATE TABLE students (
  student_id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   VARCHAR(200) NOT NULL
);

CREATE TABLE courses (
  course_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code        VARCHAR(20) NOT NULL UNIQUE,
  title       VARCHAR(200) NOT NULL
);

-- Junction table (no own identity needed unless you add attributes)
CREATE TABLE student_course (
  student_id  INTEGER NOT NULL,
  course_id   INTEGER NOT NULL,
  enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
  CONSTRAINT pk_student_course PRIMARY KEY (student_id, course_id),
  CONSTRAINT fk_sc_student FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_sc_course FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
    ON DELETE CASCADE
);

CREATE INDEX ix_sc_course ON student_course(course_id);

The composite primary key prevents duplicate pairs. Index both sides for symmetric join performance.

Adding attributes to relationships

Relationship attributes (for example role, grade, position) belong in the junction table because they describe the pairing.

ALTER TABLE student_course
  ADD COLUMN grade VARCHAR(2);  -- e.g., 'A', 'B', …

Querying across M:N

-- All courses for a given student
SELECT c.course_id, c.code, c.title
FROM student_course sc
JOIN courses c ON c.course_id = sc.course_id
WHERE sc.student_id = 42;

-- All students in a course
SELECT s.student_id, s.full_name
FROM student_course sc
JOIN students s ON s.student_id = sc.student_id
WHERE sc.course_id = 7;

Anti-patterns to avoid

Avoid storing multiple ids in a single column (comma separated values) or variable numbers of columns (for example tag1_id, tag2_id, tag3_id). These break normalization and make queries and constraints unreliable.

Junction tables and referential integrity

Junction tables are the workhorse for flexible relationships. Keep them narrow and well indexed, then enforce integrity with keys and constraints.

Composite keys vs surrogate keys

Use a composite primary key of the two foreign keys when the pair must be unique and you rarely reference the relationship row directly. Add a surrogate key (for example id) only when the relationship itself is a first-class entity (for example you attach many child rows to the relationship or you reference it from other tables).

-- Surrogate key variant for richer relationship entities
CREATE TABLE membership (
  membership_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id       INTEGER NOT NULL,
  team_id       INTEGER NOT NULL,
  role          VARCHAR(40) NOT NULL,
  started_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_membership UNIQUE (user_id, team_id),
  CONSTRAINT fk_m_user FOREIGN KEY (user_id) REFERENCES users(user_id),
  CONSTRAINT fk_m_team FOREIGN KEY (team_id) REFERENCES teams(team_id)
);

Ensuring uniqueness and preventing duplicates

Provide a uniqueness guarantee either through a composite primary key or a separate UNIQUE constraint. This avoids accidental duplicate relationships.

-- If you chose a surrogate key, add an explicit UNIQUE pair
CREATE UNIQUE INDEX ux_membership_user_team
  ON membership(user_id, team_id);

Maintaining integrity with CHECK constraints

When additional rules apply (for example role values, valid date ranges, non overlapping intervals), enforce them with CHECK constraints so the database guards your data consistently.

ALTER TABLE membership
  ADD CONSTRAINT ck_membership_role
  CHECK (role IN ('owner','admin','member'));

Cascade rules that fit reality

In junction tables, ON DELETE CASCADE on both foreign keys is often appropriate because the relationship has no meaning if either side disappears. If junction rows carry historical facts that must remain, prefer RESTRICT with application policies that retire records instead of deleting them.

Schema versioning and migration strategies

Databases evolve. Treat schema changes as versioned, repeatable steps that can be applied forward (and sometimes backward) across environments. Your goal is reliability, auditability, and minimal downtime.

Versioning with a migrations ledger

Maintain a dedicated table that records applied migrations. Each migration has an ordered id, a description, a checksum, and timestamps.

CREATE TABLE schema_migrations (
  version      VARCHAR(50) PRIMARY KEY,  -- e.g., 20251103_1400_add_index
  description  VARCHAR(200) NOT NULL,
  checksum     VARCHAR(64) NOT NULL,
  applied_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Store migration scripts in version control with filenames such as V20251103_1400__add_index.sql. The double underscore separates the version and description; the rest is free text.

Forward-only vs reversible migrations

Forward only favors simplicity (roll forward with new scripts to fix issues). Reversible migrations include an UP and DOWN step that can revert changes when safe. Use reversible for non destructive changes; use forward only when data loss or complex transformations make reversal unsafe.

Zero downtime patterns

When your application must remain available, use additive, compatible changes first, followed by code rollout, then cleanup.

-- Phase 1: additive
ALTER TABLE orders ADD COLUMN order_total_cents BIGINT;

-- Backfill in batches (pseudo code shown in SQL for brevity)
UPDATE orders
SET order_total_cents = (SELECT SUM(quantity * unit_price_cents) FROM order_lines ol
                         WHERE ol.order_id = orders.order_id)
WHERE order_total_cents IS NULL
  AND order_id BETWEEN :lo AND :hi;

-- Phase 2: deploy code reading the new column
-- Phase 3: make non-nullable once all rows are backfilled
ALTER TABLE orders ALTER COLUMN order_total_cents SET NOT NULL;  -- Dialect may vary …

Idempotent and environment aware scripts

Migrations should be idempotent where possible. Use IF NOT EXISTS guards or detect state before applying changes, then record the version in schema_migrations.

-- Example guard (dialect specific)
CREATE TABLE IF NOT EXISTS audit_log (
  audit_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  actor       VARCHAR(100) NOT NULL,
  action      VARCHAR(100) NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Data migrations with validation

Structural changes often require moving data. Pair transformation steps with validation queries so you can prove correctness before cleanup.

-- Transform
UPDATE membership SET role = 'member' WHERE role = 'basic';

-- Validate (counts must match)
SELECT COUNT(*) FROM membership WHERE role = 'basic';  -- Expect 0

Tooling and workflow

Adopt a migration tool that suits your stack (for example pure SQL runners or code based tools). Integrate migrations into continuous integration so every deployment applies the exact same steps. Gate releases on successful migration application and verification queries.

Keep every migration small, isolated, and named for its purpose. Avoid modifying old migration files after they have shipped. If you must change history, create a new migration that corrects the state.