Designing the schema, and justifying every choice

Now the schema, with every choice defended — because a capstone is not about producing tables, it is about being able to say why each one is shaped as it is. This is the design that the build lesson creates and runs; read it against your own sketch from the brief.

The core entities

Straight from the nouns, one table each:

CREATE TABLE movies (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  language text NOT NULL,
  duration_min int NOT NULL CHECK (duration_min > 0),
  released_on date,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE screens (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  total_seats int NOT NULL CHECK (total_seats > 0)
);

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,                 -- nullable: a walk-up customer may have none
  phone text,
  created_at timestamptz NOT NULL DEFAULT now()
);

Already several module-8 decisions are in here, made deliberately: bigint identity keys (never int — the expensive migration), timestamptz not timestamp, created_at on every table, CHECK constraints stating the rules the data must obey, and NOT NULL as the default with nullability (email) the justified exception. None of this is decoration; each is a mistake from module 8 pre-empted.

Seats as rows, not a number — the first real decision

screens.total_seats is a count, but that is only for display. The seats themselves are rows:

CREATE TABLE seats (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  screen_id bigint NOT NULL REFERENCES screens(id),
  row_label text NOT NULL,
  seat_num int NOT NULL,
  UNIQUE (screen_id, row_label, seat_num)      -- no two "A5"s on one screen
);

Why a row per seat and not just a count? Because the brief says customers pick specific seats ("A3"), and you must record which seat is booked to prevent double-booking a particular seat. A count cannot express "A3 is taken but A4 is free". This is module 8's lesson — a boolean or a number that throws away the information the business actually needs — applied: the requirement is per-seat, so the model must be per-seat. Screen 1 gets 100 seat rows, and that is correct.

Shows — a movie on a screen at a time

CREATE TABLE shows (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  movie_id bigint NOT NULL REFERENCES movies(id),
  screen_id bigint NOT NULL REFERENCES screens(id),
  starts_at timestamptz NOT NULL,
  price_paise int NOT NULL CHECK (price_paise > 0)
);

A show joins a movie to a screen at a time, with its own price (matinees differ from evenings). price_paise — integer paise, not float, module 8's first expensive mistake refused. The price lives on the show, not the movie, because it is a fact about this screening.

Bookings and the seat link — where double-booking is defeated

The two tables that carry the whole point of the system:

CREATE TABLE bookings (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  show_id bigint NOT NULL REFERENCES shows(id),
  customer_id bigint NOT NULL REFERENCES customers(id),
  status text NOT NULL DEFAULT 'confirmed' CHECK (status IN ('confirmed','cancelled')),
  amount_paise int NOT NULL CHECK (amount_paise >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE booking_seats (
  booking_id bigint NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  show_id bigint NOT NULL REFERENCES shows(id),
  seat_id bigint NOT NULL REFERENCES seats(id),
  PRIMARY KEY (booking_id, seat_id),
  UNIQUE (show_id, seat_id)          -- ← the line that makes double-booking impossible
);

booking_seats is a join table (module 5) — a booking covers many seats, a seat can be booked across many shows, so the relationship is many-to-many. But it carries the single most important line in the whole schema:

UNIQUE (show_id, seat_id)

This constraint is what enforces the brief's non-negotiable requirement. For any given show, each seat can appear in booking_seats once. A second attempt to book the same seat for the same show violates the constraint and the database rejects it — not the application, the database, atomically, even under perfectly concurrent requests. The build lesson proves this: two transactions racing for seat A3 resolve to one success and one clean duplicate key error.

Why enforce it in the schema and not in application code? Because "check if the seat is free, then book it" in application code is the lost-update race from module 6 — two requests both check, both see it free, both book. Only a database constraint (or explicit locking) closes that window. The UNIQUE constraint is not an optimisation; it is the correctness guarantee, and putting it in the database means every code path — the app, an admin script, a future service — gets it for free.

Note also status as text with a CHECK rather than an enum (module 8's reasoning: adding a value later is cheaper), and ON DELETE CASCADE so cancelling a booking cleanly removes its seat links.

What is not in PostgreSQL

Deliberately absent from the schema:

• Seat holds. A hold is transient and self-expiring — a Redis key with a TTL (the cache lesson), not a table. Putting holds in PostgreSQL would mean a cleanup job and rows that are not really bookings cluttering the booking logic.
• The availability cache. Derived from booking_seats, recomputable, disposable — Redis.
• Rate-limit counters. Ephemeral — Redis.

This is the source-of-truth boundary from module 13, drawn concretely: durable, authoritative, must-not-be-lost data is in PostgreSQL; transient, derived, rebuildable data is in Redis.

The indexes

Driven by the queries the next lesson writes, and by module 7's rules:

CREATE INDEX ON shows (movie_id);        -- foreign keys: index them (module 7)
CREATE INDEX ON shows (screen_id);
CREATE INDEX ON shows (starts_at);       -- "shows on this day", ordered listings
CREATE INDEX ON seats (screen_id);
CREATE INDEX ON bookings (show_id);
CREATE INDEX ON bookings (customer_id);
CREATE INDEX ON booking_seats (seat_id);

The UNIQUE (show_id, seat_id) constraint already creates a usable index on (show_id, seat_id), so the availability query ("seats booked for this show") is covered without an extra index — the leftmost-prefix rule from module 7. Foreign keys are indexed because an unindexed foreign key means slow lookups and a parent-delete that scans the child (module 7).

The design, defended in one paragraph

Every entity is a table; every relationship a foreign key; money is integer paise; times are timestamptz; seats are rows because the requirement is per-seat; the many-to-many booking-to-seat link is a join table; and the one UNIQUE (show_id, seat_id) constraint turns "no double-booking" from a hope in application code into a guarantee in the database. The transient, derived data — holds, availability, rate limits — is kept out of PostgreSQL and given to Redis, so the source of truth stays clean and every non-authoritative store is disposable. That is the whole design, and you can defend each line from a specific lesson in this course.

Check your work

Why seats are rows, not a count. The requirement is per-seat ("book A3"); a count cannot say which seat is taken — module 8's throw-away-the-information mistake.

The single most important line in the schema. UNIQUE (show_id, seat_id) on booking_seats.

What it guarantees, and at what level. No seat booked twice for a show — enforced by the database, atomically, under concurrency.

Why enforce it in the schema, not application code. "Check then book" in code is the lost-update race; only a constraint (or explicit lock) closes it, and the constraint protects every code path.

Why price_paise and amount_paise are integers. Money as float is module 8's first expensive mistake — broken equality and drift.

Why the price is on the show, not the movie. It is a fact about the screening, and it varies (matinee versus evening).

Why booking_seats is a join table. Booking-to-seat is many-to-many.

Why status is text + CHECK not enum. Adding a value later is cheaper (module 8).

What is kept out of PostgreSQL, and why. Seat holds, availability cache, rate limits — transient and derived, so Redis; the source-of-truth boundary from module 13.

Why foreign keys are indexed. Unindexed foreign keys mean slow lookups and parent-deletes that scan the child (module 7).

Practice

1. Compare this schema with the one you sketched after the brief. List every difference and decide who is right.
2. Justify each CHECK constraint. What bad data does each one refuse?
3. Explain, to someone who would put it in application code, why UNIQUE (show_id, seat_id) belongs in the database.
4. Design the "count of seats" alternative to per-seat rows and show a query it cannot answer.
5. Decide where a "seat type" (premium/regular) with different prices would go, and how it changes shows.price_paise.
6. List every foreign key and confirm each has an index (or is covered by a constraint's index).
7. For each table, state whether it is source-of-truth or could be derived, and confirm none of the Redis-bound data crept in.
8. Add a hypothetical requirement ("a customer can cancel up to 2 hours before the show") and decide what it changes in the schema.

Official documentation

• PostgreSQL — CREATE TABLE — Constraints, defaults, identity columns.
• PostgreSQL — Constraints — UNIQUE, CHECK, foreign keys and ON DELETE CASCADE.
• PostgreSQL — Data types — bigint, timestamptz, and why integer money.
• PostgreSQL — Indexes — Indexing foreign keys and the constraint-index overlap.

Next: building it and loading real data.