# Fitness Studio Membership Schema > Classes fill up. Members no-show. Billing continues. Canonical URL: Domain: Data Modeling · Difficulty: easy · Seniority: L5 ## Problem We run a chain of fitness studios. Members book classes online and check in when they arrive. We need a database to manage memberships, class schedules, and attendance. Can you design the tables? ## Worked solution and explanation ### Why this problem exists in real interviews This probes whether a candidate can separate a **class template** (yoga, spinning) from a **class instance** (Tuesday 7pm yoga in Studio B) and model a many-to-many booking relationship as its own entity. The follow-up question, no-show rate, only has a clean answer if attendance is tracked independently of bookings. > **Trick to Solving** > > Any time a prompt says “members book classes,” the trick is to notice two distinct time concepts: the class definition and the scheduled occurrence. Before drawing tables, a strong candidate asks: can the same class type repeat weekly, and do we need to track who showed up separately from who booked? > > 1. Separate class_types (the template) from class_schedule (the instance) > 2. Bookings become the junction between members and class_schedule > 3. Attendance (check-in) lives on bookings, not as a parallel table --- ### Break down the requirements #### Step 1: Split template from instance `class_types` holds “yoga 60min,” `class_schedule` holds the Tuesday 7pm occurrence at a specific studio with a specific instructor. Conflating these makes the weekly schedule impossible to represent. #### Step 2: Model memberships as their own entity `memberships` describes tiers (price, class limit). `members` FK into it. This lets pricing evolve without touching member rows. #### Step 3: Bookings as a junction `bookings` links `members` to `class_schedule` with a status enum (confirmed, waitlisted, cancelled, no_show) and a nullable `check_in_time`. #### Step 4: Track studios and instructors as dimensions `studios` and `instructors` are conformed dimensions, referenced from `class_schedule` so that utilization and instructor performance are one GROUP BY away. --- ### The solution Below is one defensible model. The conceptual anchor is the separation of `class_types` from `class_schedule`, which cascades into how bookings and attendance behave. > **Why this works** > > Separating `class_types` from `class_schedule` is the trade-off that pays for itself. The template rarely changes; the instances explode with volume. Keeping them apart means template edits are O(1) and instance generation is a straightforward cron. > **Interviewers watch for** > > Strong candidates ask whether a member can book a class for a friend and whether waitlist promotions should create or update a row. They treat `bookings.status` as the full lifecycle and avoid a parallel `attendance` table. > **Common pitfall** > > Collapsing `class_types` and `class_schedule` into a single “classes” table. The weekly recurring pattern forces duplication of name, duration, and instructor on every row, and a rename turns into a bulk UPDATE across every historical instance. --- ### The analysis pattern **No-show rate by instructor** ```sql SELECT i.full_name, COUNT(*) FILTER (WHERE b.status = 'no_show') * 1.0 / COUNT(*) AS no_show_rate, COUNT(*) AS total_bookings FROM bookings b JOIN class_schedule cs ON cs.class_schedule_id = b.class_schedule_id JOIN instructors i ON i.instructor_id = cs.instructor_id WHERE cs.scheduled_start >= NOW() - INTERVAL '30 days' AND b.status IN ('confirmed', 'no_show') GROUP BY i.full_name ORDER BY no_show_rate DESC ``` --- ### Trade-offs and alternatives **Template + instance split** class_types is the template, class_schedule is the occurrence. * Template edits touch one row * Clean home for recurrence rules * Two joins to answer “which yoga class” questions **Single classes table** One classes table with name, time, studio, instructor inline. * Simpler to read * Renaming a class touches every historical row * No natural place to express recurrence --- ## Common follow-up questions - How would you express a class that repeats every Tuesday and Thursday for 8 weeks? _(Tests whether the candidate adds a recurrence_rule on class_types or generates class_schedule rows via a job.)_ - Members on the hot tier get unlimited classes, standard tier gets 10 per month. Where is that enforced? _(Tests whether quotas live on the membership dimension and are checked at booking time.)_ - A studio closes for maintenance and all bookings must be cancelled with credit. What changes? _(Tests whether bookings.status supports the lifecycle and whether a credit ledger exists.)_ - How would you handle waitlist promotion when a confirmed member cancels? _(Tests whether waitlist ordering is an attribute on bookings or a separate queue table.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/fitness_studio_membership_schema) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.