Modeling Practice: Library, Flight Booking and Ride Sharing

In one line

Three worked models that exercise every relationship type: a library (copies vs titles, loans over time), flight booking (flights vs scheduled departures, seats, passengers per booking), and ride sharing (riders, drivers, vehicles, trips with state).

0/4 · 0%

Think of it like this

An architect's sketchbook. Before designing a new building you study existing ones; each model below is a sketch you can adapt.

Key ideas

01
Library: separate the title (book, one per ISBN) from physical copy rows; a loan(copy_id, member_id, borrowed_at, due_at, returned_at) records history. "Is this copy available?" is "no loan with returned_at IS NULL", enforced by a partial unique index.
02
Flight booking: route (origin, destination) → flight (flight number) → departure (a flight on a date) → seat inventory per departure. A booking (PNR) has many passenger rows; each passenger gets at most one seat per departure: UNIQUE (departure_id, seat_no).
03
Ride sharing: rider, driver, vehicle (a driver can have several, one active), trip(rider_id, driver_id, vehicle_id, status, requested_at, pickup, dropoff, fare). Status changes form an append-only trip_event table for audit.
04
Pattern that recurs: separate the catalogue thing (title, flight, product) from the instance (copy, departure, stock unit), and separate current state from history (loan rows, trip events).
05
Time-bounded facts (loans, bookings, prices) need start and end timestamps; overlapping intervals are prevented with exclusion constraints (Phase 2.3).

Code & diagrams

library.sqlsql

CREATE TABLE book   (isbn text PRIMARY KEY, title text NOT NULL);
CREATE TABLE copy   (id bigint PRIMARY KEY, isbn text NOT NULL REFERENCES book(isbn),
                     shelf text);
CREATE TABLE member (id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE loan (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  copy_id     bigint NOT NULL REFERENCES copy(id),
  member_id   bigint NOT NULL REFERENCES member(id),
  borrowed_at timestamptz NOT NULL DEFAULT now(),
  due_at      timestamptz NOT NULL,
  returned_at timestamptz
);
-- a copy can have at most one open loan
CREATE UNIQUE INDEX one_open_loan_per_copy ON loan (copy_id) WHERE returned_at IS NULL;

-- available copies of a title
SELECT c.id FROM copy c
WHERE c.isbn = '978-0134685991'
  AND NOT EXISTS (SELECT 1 FROM loan l WHERE l.copy_id = c.id AND l.returned_at IS NULL);

flight-er.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Prevent double-lending and model reservations

Extend the library: members can reserve a title (not a copy) and are served first-come-first-served when a copy is returned. Prevent a copy being lent twice even under concurrent checkouts.

When it breaks

Availability stored as a boolean on copy

What you see

copy.available drifts from loan rows after a crash or bug between two updates; the catalogue shows copies that are actually out.

Fix & prevent

Derive availability from loans (or update both in one transaction and constrain it); the partial unique index is the real guard.

Explain it without notes

Why separate book from copy?

Practice

Model seat inventory so two passengers can never hold the same seat on the same departure.

Trade-offs

↔
History tables grow forever and need partitioning or archiving (Phase 13); in return you get audit and correct availability.

Done when you can

I can model catalogue vs instance and current state vs history for a real domain.

Command Palette