Design a database schema from requirements

Turn feature requirements into a normalized schema with keys, constraints, indexes and the tradeoffs explained.

Change note: Initial version

Prompt

Design a {{database}} schema for the following application.

What the application does: {{app}}
Core requirements and rules:
{{requirements}}
Expected scale and main query patterns: {{scale}}

Deliver:
1. Entities and relationships: a short list in plain words, including cardinality (one-to-many, many-to-many) and anything optional.
2. Schema as runnable DDL, with primary keys, foreign keys and their ON DELETE behavior, NOT NULL and CHECK constraints, unique constraints, sensible types (for money, timestamps with time zones, identifiers, enums) and default values.
3. Indexes: justified by the query patterns I gave. Do not add indexes without naming the query they serve.
4. Decisions and tradeoffs: normalization choices, where denormalization is worth it, soft delete versus hard delete, audit history, multi-tenancy handling.
5. Five example queries for the most important access patterns.
6. Evolution: which likely future requirements would be painful with this design, and how to prepare for them.
7. Questions: ambiguities in my requirements that would change the design.

Use clear snake_case names. Enforce rules in the database rather than relying on application code whenever the database can express them.

Variables

• {{database}}Databaseselect

  Options: PostgreSQL, MySQL, SQLite, SQL Server, MongoDB

  Default: PostgreSQL

• {{app}}Applicationtext

  Default: A booking system for a small chain of climbing gyms

• {{requirements}}Requirementslong

  Default: Members buy passes (single entry, 10-visit, monthly). Classes have capacity limits and instructors. A member can book a class if they have a valid pass. Cancellations up to 2 hours before are free. Each gym has its own opening hours.

• {{scale}}Scale and queriestext

  Default: 5 gyms, 20k members; most common queries are today's class roster and a member's remaining visits

Example output

**Entities:** gym 1-N class_session; instructor 1-N class_session (optional); member 1-N pass; member N-N class_session via booking; each booking is paid by one pass. Gym opening hours are 1-N rows per gym.

```sql
CREATE TABLE gym (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE, timezone text NOT NULL);
CREATE TABLE gym_hours (gym_id bigint NOT NULL REFERENCES gym(id) ON DELETE CASCADE, weekday smallint NOT NULL CHECK (weekday BETWEEN 0 AND 6), opens time NOT NULL, closes time NOT NULL, PRIMARY KEY (gym_id, weekday), CHECK (closes > opens));
CREATE TABLE member (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, full_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE instructor (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name text NOT NULL);
CREATE TABLE class_session (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  gym_id bigint NOT NULL REFERENCES gym(id),
  instructor_id bigint REFERENCES instructor(id) ON DELETE SET NULL,
  starts_at timestamptz NOT NULL,
  capacity int NOT NULL CHECK (capacity > 0));
CREATE TABLE pass (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_id bigint NOT NULL REFERENCES member(id),
  kind text NOT NULL CHECK (kind IN ('single','ten_visit','monthly')),
  visits_left int CHECK (visits_left >= 0),
  valid_until date,
  CHECK ((kind = 'monthly') = (valid_until IS NOT NULL AND visits_left IS NULL)));
CREATE TABLE booking (
  class_session_id bigint NOT NULL REFERENCES class_session(id),
  member_id bigint NOT NULL REFERENCES member(id),
  pass_id bigint NOT NULL REFERENCES pass(id),
  booked_at timestamptz NOT NULL DEFAULT now(),
  cancelled_at timestamptz,
  PRIMARY KEY (class_session_id, member_id));
CREATE INDEX idx_session_gym_start ON class_session (gym_id, starts_at); -- today's roster
CREATE INDEX idx_pass_member ON pass (member_id); -- remaining visits
```

**Tradeoffs:** capacity is enforced in a transaction using `SELECT ... FOR UPDATE` on the session row. Cancellations set `cancelled_at` (kept for audit); refunds are allowed only if `starts_at - now() >= interval '2 hours'`.

**Key queries:** roster = join booking and member where `class_session_id = $1 AND cancelled_at IS NULL`; remaining visits = `SELECT sum(visits_left) FROM pass WHERE member_id = $1`.

**Questions:** can one pass cover several gyms? Is there a waitlist?

Notes

Requiring every index to name the query it serves prevents speculative indexing. The final questions surface the unclear requirements that most often cause redesigns.