Fitness Gym ER Diagram

A gym schema links people to plans and classes: members subscribe to membership plans, trainers run classes that meet in scheduled sessions, and bookings connect members to those sessions. Check-ins record attendance at the door, independently of any class.

The schema separates the product from the person buying it. membership_plans holds the reusable offer — name, price, duration_days — while subscriptions records one member's concrete period with starts_on and ends_on. Keeping both tables means a price change updates the plan without rewriting existing subscriptions, and a member can renew or switch plans by inserting a new subscription row. The CHECK on ends_on being on or after starts_on is cheap insurance against a reversed date. members itself only stores identity and contact data, so plan history never distorts the person record.

Reading the diagram, members and membership_plans meet at subscriptions, the first of two join-shaped tables. The second is bookings: class_sessions sits under classes, trainers sit above classes, and bookings connects a member to a specific session with UNIQUE (session_id, member_id) preventing a double booking. class_sessions carries the actual starts_at timestamp and room, so a class can run every Tuesday without duplicating its description. check_ins is separate: a member may walk in without booking a class, so session_id is nullable and the row records the door time. That split lets front-desk traffic and class attendance be reported separately.

The weak point is capacity. classes.capacity states how many people fit, but neither class_sessions nor bookings enforces it; a full session needs either a trigger, a seat counter updated inside the booking transaction, or an application rule. Deletion rules are more protective: members cascade to their subscriptions, bookings and check-ins, while trainers are blocked from deletion while classes reference them. class_sessions cascades from classes, so cancelling a class removes its schedule, and bookings cascade from the session — meaning a cancelled session should be marked, not deleted, if refunds matter.

A waitlist table (session_id, member_id, position, created_at) is the first addition once popular classes fill, and it can promote members automatically when a booking is cancelled. payments or subscription_invoices under subscriptions would turn billing into a record instead of a status column. Equipment and access control come next: an equipment table with maintenance dates, or a door_log keyed by member that distinguishes gym entry from class attendance, both slot beside check_ins without changing the core. None of them requires changing the subscription or booking keys, which is the sign of a stable design.

The diagram

Tables (8)

Relationships (8)

• 1:Nmembers.id→subscriptions.member_idON DELETE CASCADE
• 1:Nmembership_plans.id→subscriptions.plan_id
• 1:Ntrainers.id→classes.trainer_id
• 1:Nclasses.id→class_sessions.class_idON DELETE CASCADE
• 1:Nclass_sessions.id→bookings.session_idON DELETE CASCADE
• 1:Nmembers.id→bookings.member_idON DELETE CASCADE
• 1:Nmembers.id→check_ins.member_idON DELETE CASCADE
• 1:Nclass_sessions.id→check_ins.session_idON DELETE SET NULL

The SQL

CREATE TABLE membership_plans (
  id SERIAL PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  price DECIMAL(8,2) NOT NULL CHECK (price >= 0),
  duration_days INTEGER NOT NULL CHECK (duration_days > 0)
);

CREATE TABLE members (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(30),
  joined_at DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  plan_id INTEGER NOT NULL REFERENCES membership_plans(id),
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  CHECK (ends_on >= starts_on)
);

CREATE TABLE trainers (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  specialty VARCHAR(80)
);

CREATE TABLE classes (
  id SERIAL PRIMARY KEY,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id),
  name VARCHAR(120) NOT NULL,
  description TEXT,
  capacity INTEGER NOT NULL CHECK (capacity > 0)
);

CREATE TABLE class_sessions (
  id SERIAL PRIMARY KEY,
  class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  starts_at TIMESTAMP NOT NULL,
  room VARCHAR(40),
  duration_minutes INTEGER NOT NULL DEFAULT 60 CHECK (duration_minutes > 0)
);

CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  session_id INTEGER NOT NULL REFERENCES class_sessions(id) ON DELETE CASCADE,
  member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  booked_at TIMESTAMP NOT NULL DEFAULT now(),
  status VARCHAR(20) NOT NULL DEFAULT 'confirmed',
  UNIQUE (session_id, member_id)
);

CREATE TABLE check_ins (
  id SERIAL PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
  session_id INTEGER REFERENCES class_sessions(id) ON DELETE SET NULL,
  checked_in_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides