Documentation

Database Schema

Full reference of all 13 GymHub MySQL tables, columns, and relationships.

13 Tables

90 Total Records

21 Classes

14 Users

2 Bookings

Key Relationships

How the main tables connect to each other.

users → bookings (one user, many bookings)

users → user_memberships (one user, one active plan)

users → ratings (one user, one rating per class)

users → questions (one user, many questions)

users → instructors (one-to-one)

classes → class_schedule (one class, many sessions)

class_schedule → bookings (one session, many bookings)

questions → answers (one question, one answer)

users 14 memberships 3 user_memberships 1 instructors 4 classes 21 class_schedule 20 bookings 2 ratings 10 questions 4 answers 3 locations 3 progress 1 site_settings 4

users

Stores all registered users including members and administrators.

14 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique user identifier
`first_name`	VARCHAR(50)	NOT NULL	User's first name
`last_name`	VARCHAR(50)	NOT NULL	User's last name
`email`	VARCHAR(100)	NOT NULL UNIQUE	Login email — must be unique
`password`	VARCHAR(255)	NOT NULL	bcrypt hashed password — never plain text
`role`	ENUM	member \| admin	Access level — admin gets admin panel
`avatar`	VARCHAR(255)	DEFAULT NULL	Profile photo filename
`phone`	VARCHAR(20)	DEFAULT NULL	Optional phone number
`status`	ENUM	active \| disabled	Disabled accounts cannot log in
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	When account was registered

memberships

The three membership tier products available for purchase: Basic, Pro, and Elite.

3 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique plan ID
`name`	VARCHAR(50)	NOT NULL	Plan name (Basic / Pro / Elite)
`price`	DECIMAL(8,2)	NOT NULL	Monthly price in CAD dollars
`duration`	INT	NOT NULL	Plan length in days (30 = 1 month)
`features`	TEXT	NOT NULL	JSON array of feature strings
`status`	ENUM	active \| inactive	Inactive plans are hidden from members

user_memberships

Links a user to their purchased membership plan with start and expiry dates.

1 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique record ID
`user_id`	INT	FOREIGN KEY → users.id	The member who purchased
`membership_id`	INT	FOREIGN KEY → memberships.id	Which plan was purchased
`start_date`	DATE	NOT NULL	Date membership begins
`end_date`	DATE	NOT NULL	Date membership expires

instructors

Instructor profiles linked to user accounts. Stores bio, specialties, photo, and calculated rating.

4 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique instructor ID
`user_id`	INT	FOREIGN KEY → users.id	Linked user account (for name/email)
`bio`	TEXT	DEFAULT NULL	Instructor biography paragraph
`specialties`	VARCHAR(255)	DEFAULT NULL	Comma-separated specialty list
`photo`	VARCHAR(255)	DEFAULT NULL	Profile photo filename
`rating`	DECIMAL(3,2)	DEFAULT 0.00	Average star rating (0.00–5.00)

classes

The 20 fitness class products. Each class has two bookable format options (e.g. Beginner/Advanced).

21 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique class ID
`title`	VARCHAR(100)	NOT NULL	Class display name
`category`	VARCHAR(50)	NOT NULL	Category (Strength/Cardio/Yoga etc)
`description`	TEXT	NOT NULL	Full class description text
`instructor_id`	INT	FOREIGN KEY → instructors.id	Assigned instructor
`capacity`	INT	DEFAULT 20	Max participants per session
`duration_min`	INT	NOT NULL	Session length in minutes
`price`	DECIMAL(8,2)	NOT NULL	Price per session in dollars
`option_label`	VARCHAR(50)	NOT NULL	Option type label (e.g. Level)
`option_a`	VARCHAR(50)	NOT NULL	First option choice (e.g. Beginner)
`option_b`	VARCHAR(50)	NOT NULL	Second option choice (e.g. Advanced)
`image`	VARCHAR(255)	DEFAULT NULL	Class image filename
`status`	ENUM	active \| inactive	Inactive classes are hidden from catalog

class_schedule

Individual scheduled sessions for each class. Each row is one bookable time slot on a specific date.

20 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique session ID
`class_id`	INT	FOREIGN KEY → classes.id	Which class this session belongs to
`date`	DATE	NOT NULL	Session date (YYYY-MM-DD)
`time`	TIME	NOT NULL	Session start time (HH:MM:SS)
`room`	VARCHAR(50)	DEFAULT Main Floor	Room or area name within the gym
`spots_left`	INT	NOT NULL	Remaining bookable spots (decremented on booking)

bookings

Records each member booking a scheduled class session. Tracks option chosen and booking status.

2 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique booking ID
`user_id`	INT	FOREIGN KEY → users.id	Member who made the booking
`schedule_id`	INT	FOREIGN KEY → class_schedule.id	Specific session booked
`option_choice`	VARCHAR(50)	NOT NULL	Which option the member chose
`status`	ENUM	confirmed \| cancelled \| completed	Booking status
`booked_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	When booking was made

ratings

Member star ratings and reviews for classes. UNIQUE KEY on (user_id, class_id) — one rating per member per class.

10 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique rating ID
`user_id`	INT	FOREIGN KEY → users.id	Member who submitted the rating
`class_id`	INT	FOREIGN KEY → classes.id	Class being rated
`stars`	TINYINT	1–5 CHECK constraint	Star rating (1=Terrible, 5=Excellent)
`comment`	TEXT	DEFAULT NULL	Optional written review text
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	When rating was submitted

questions

Questions submitted by members via the Ask a Trainer page. Supports optional file attachments.

4 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique question ID
`user_id`	INT	FOREIGN KEY → users.id	Member who submitted the question
`title`	VARCHAR(200)	NOT NULL	Question subject line
`body`	TEXT	NOT NULL	Full question text
`attachment`	VARCHAR(255)	DEFAULT NULL	Optional uploaded file path
`status`	ENUM	open \| answered \| closed	open = awaiting response
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	When question was submitted

answers

Admin or trainer responses to member questions. One answer per question (admins can update it).

3 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique answer ID
`question_id`	INT	FOREIGN KEY → questions.id	The question being answered
`admin_id`	INT	FOREIGN KEY → users.id	Admin/trainer who wrote the answer
`body`	TEXT	NOT NULL	The answer text
`created_at`	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	When the answer was submitted

locations

Physical GymHub gym locations. Lat/lng coordinates are used by the Leaflet.js interactive map.

3 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique location ID
`name`	VARCHAR(100)	NOT NULL	Location display name
`address`	VARCHAR(255)	NOT NULL	Street address
`city`	VARCHAR(100)	NOT NULL	City name
`lat`	DECIMAL(10,7)	NOT NULL	GPS latitude coordinate
`lng`	DECIMAL(10,7)	NOT NULL	GPS longitude coordinate
`phone`	VARCHAR(20)	DEFAULT NULL	Location phone number
`hours`	VARCHAR(255)	DEFAULT NULL	Opening hours description string
`photo`	VARCHAR(255)	DEFAULT NULL	Location photo filename

progress

Member fitness progress log entries. UNIQUE KEY on (user_id, log_date) — one entry per member per day.

1 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique entry ID
`user_id`	INT	FOREIGN KEY → users.id	Member who logged the entry
`log_date`	DATE	NOT NULL	Date of the log entry
`weight_kg`	DECIMAL(5,2)	DEFAULT NULL	Logged weight in kilograms
`sessions_attended`	INT	DEFAULT 0	Auto-counted from bookings table
`notes`	TEXT	DEFAULT NULL	Member's personal notes

site_settings

Key-value store for site-wide configuration. Currently used for active theme and maintenance mode.

4 records

Column	Type	Constraint	Description
`id`	INT	AUTO_INCREMENT PRIMARY KEY	Unique setting ID
`setting_key`	VARCHAR(50)	NOT NULL UNIQUE	Setting name (e.g. active_theme)
`setting_value`	VARCHAR(255)	NOT NULL	Setting value (e.g. powerzone)
`updated_at`	TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP	Automatically updated on change

Full SQL Schema File

The complete CREATE TABLE statements and seed data are in sql/gymhub.sql in the GitHub repository. See the Installation Guide for import instructions.

View Installation Guide