The No-Show

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
L5

Problem

We run a chain of fitness studios where members sign up under a pricing tier that fixes their monthly fee and how many classes it includes. Each session on the calendar is a single dated occurrence of a class type, held at one studio at a specific date and time with one instructor, and members reserve a spot in the session they want. When a popular session fills, a shut-out member joins the waitlist and is promoted when someone cancels; members who get in are scanned at the door, so a confirmed reservation that was never checked in stands apart as a no-show. Design the tables to run memberships, the session calendar, and the full reservation lifecycle, where a booking is waitlisted, confirmed, cancelled, or a no-show, and a confirmed spot only counts as attended once it is scanned at the door.

Every reserved seat ends one of five ways. Build the model that can tell them apart.

Worked solution and explanation

Why this problem exists in real interviews

This is a template-vs-instance separation dressed up as a booking calendar. The skill being probed: can you model a repeating class type separately from its scheduled occurrences, so membership tiers, pricing, and instructor routing all land in the right table? Anyone merges class_types and class_schedule into one 'classes' table and it looks fine on day one. The trap is that the template rarely changes while the instances explode with volume, so a merged table duplicates name, duration, and instructor on every occurrence and leaves recurrence with nowhere to live. Get that wrong and renaming yoga becomes a bulk UPDATE across every historical row, weekly schedules become impossible to represent, and the no-show question has no clean answer because attendance is not tracked apart from the booking.

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. The status carries the reservation through its whole life; the timestamp is what promotes a confirmed booking to attended.

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.

Bookings sit between members and `class_schedule` instances, with status covering the no-show case
  • members: member_id BIGINT PK, membership_id INT FK, email TEXT, joined_at TIMESTAMP
  • memberships: membership_id INT PK, tier_name TEXT, monthly_price DECIMAL, class_limit INT
  • studios: studio_id INT PK, name TEXT, city TEXT, capacity INT
  • instructors: instructor_id INT PK, full_name TEXT, speciality TEXT
  • class_types: class_type_id INT PK, name TEXT, duration_minutes INT
  • class_schedule: class_schedule_id BIGINT PK, class_type_id INT FK, studio_id INT FK, instructor_id INT FK, scheduled_start TIMESTAMP, max_seats INT
  • bookings: booking_id BIGINT PK, member_id BIGINT FK, class_schedule_id BIGINT FK, status TEXT, check_in_time TIMESTAMP
  • memberships references members
  • class_types references class_schedule
  • studios references class_schedule
  • instructors references class_schedule
  • members references bookings
  • class_schedule references bookings

The analysis pattern

No-show rate by instructor
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