Personal Best

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
L4

Problem

We're building a fitness app where users log workouts. Each workout has exercises with sets and reps. We need to track progress over time, like 'how much can this user bench press now vs. 3 months ago?' Design the data model.

Reps, sets, streaks, and personal bests. Gym rats love their stats.

Worked solution and explanation

What this really is

This is a grain declaration dressed up as a gym app. Users, workouts, exercises and sets nest four levels deep, and "bench press now vs. 3 months ago" is a maximum over individual sets inside a date window. That answer only exists if one row is one set, carrying its own reps and weight, hung from a workout that carries the date. Anyone can draw users and workouts. What separates candidates is where they stop: at the workout, or at one summary line per exercise per workout. Fold the sets into either and the 225-pound top set averages away into the warm-ups, so the personal record the chart should show was never stored.

Building it

Step 1: Declare the grain out loud

One row in workout_sets is one set of one exercise in one workout. Anything coarser buries the heaviest set inside a summary you cannot take a MAX() over, because the max was never written down.

Step 2: Hang the hierarchy on foreign keys

workouts.user_id points at users, and workout_sets.workout_id points at workouts. The set does not carry user_id or a date: both are facts about the workout, and storing them twice invites the copies to disagree.

Step 3: Put the date where the session lives

started_at sits on workouts. A session logged the next morning still happened yesterday, so the progress window reads the workout's time, not when each set row was inserted.

Step 4: Give the exercise an identity

workout_sets.exercise_id references exercises, which owns canonical_name, muscle_group and equipment. A name typed on each set would still answer the question for one user; the catalog is what keeps 'Bench Press' and 'bench press' from splitting one history in two.

Step 5: Type the measures for math

reps is INT and weight_lbs is DECIMAL, so MAX and volume arithmetic work without casting. set_number keeps order within a workout.

One `workout_sets` row per set: the grain sits at the bottom, the date on `workouts`, the lift's identity in `exercises`.
  • users: user_id BIGINT PK, email TEXT, display_name TEXT, created_at TIMESTAMP
  • exercises: exercise_id INT PK, canonical_name TEXT, muscle_group TEXT, equipment TEXT
  • workouts: workout_id BIGINT PK, user_id BIGINT FK, started_at TIMESTAMP, ended_at TIMESTAMP, workout_type TEXT
  • workout_sets: set_id BIGINT PK, workout_id BIGINT FK, exercise_id INT FK, set_number INT, reps INT, weight_lbs DECIMAL
  • workouts.user_id references users.user_id
  • workout_sets.workout_id references workouts.workout_id
  • workout_sets.exercise_id references exercises.exercise_id
One line per exercise per workout
A row holds 'Bench Press, 4 sets, 8 reps, 185 lbs'. Which 185? The average, the last, the top? Whatever was chosen, the other sets are gone, and a set of 5 at 225 cannot be recovered.
One row per set
workout_sets holds one atomic set per row. A personal record is the MAX(weight_lbs) over one user's sets for one exercise in a date range, with nothing lost to summarizing.

Proving the model works

The progress question becomes a plain aggregate: start at the sets, join up to workouts for the user and the date, join out to exercises for the lift, and take the heaviest set per month.

Monthly bench press max per user
SELECT
    w.user_id,
    DATE_TRUNC('month', w.started_at) AS month,
    MAX(s.weight_lbs) AS max_weight
FROM workout_sets s
JOIN workouts w ON w.workout_id = s.workout_id
JOIN exercises e ON e.exercise_id = s.exercise_id
WHERE e.canonical_name = 'Bench Press'
GROUP BY w.user_id, DATE_TRUNC('month', w.started_at)
ORDER BY w.user_id, month

The MAX runs over individual sets; at a coarser grain there would be nothing to take the max of.

Common follow-up questions