How to Design a Database Schema for a Fitness Tracking App

Introduction

Fitness apps like Strava, Fitbod, and Strong are deceptively complex under the hood. On the surface, users just log sets and reps — but the data model has to handle hierarchical workout structures, flexible measurement types for both strength and cardio, personal records, goal tracking, body composition history, and a social feed. Get the schema wrong early and you'll be fighting migrations for years.

In this guide, we'll build a complete, production-grade PostgreSQL schema for a fitness tracking application from the ground up. We'll cover:

How to model the workout → exercise → set hierarchy
Why UUIDs are a better choice than serial integers for this domain
How to handle both strength (reps/weight) and cardio (duration/distance) in a single exercise_sets table
How to model a social follow graph with a self-referencing junction table
Five real-world SQL queries you'd need in a production app, including window functions and CTEs

Whether you're learning SQL, designing your first SaaS schema, or evaluating someone else's data model, this walkthrough gives you a solid reference point.

Step 1: Identify Your Entities

Before writing a single line of DDL, map out your entities — the distinct "things" your system needs to track. A fitness app needs to answer questions like:

Who worked out? → users
What did they do? → workouts containing workout_exercises containing exercise_sets
What exercises exist? → exercises (a shared library)
What's the plan? → workout_plans with plan_exercises
How is their body changing? → body_measurements
What are they working toward? → goals
Who are they following? → followers

Table	Description
`users`	Athlete profiles with privacy settings and physical baseline data
`exercises`	Shared library of exercises with muscle group and equipment metadata
`workouts`	Individual logged training sessions, linked to a user
`workout_exercises`	Junction table — which exercises appeared in a workout, in what order
`exercise_sets`	Individual sets within a workout exercise (reps, weight, duration, etc.)
`workout_plans`	Predefined training programs a user can follow
`plan_exercises`	Exercises prescribed within a plan, with target sets/reps/weight
`body_measurements`	Periodic snapshots of weight, body fat %, and tape measurements
`goals`	User-defined goals with a target value, current progress, and status
`followers`	Social follow graph — who follows whom

Step 2: Define the Relationships

Understanding how entities relate to each other before you write DDL prevents the most common design mistakes: missing foreign keys, incorrect cardinality, and tables that are impossible to query efficiently.

A user logs many workouts
A workout contains many workout_exercises, each referencing a shared exercise
A workout_exercise has many exercise_sets (sets, reps, weight per set)
A workout_plan has many plan_exercises, each targeting an exercise
A user has many body_measurements (one per measurement date)
A user has many goals
A user can follow many other users (and be followed by many) via the followers junction table

DBA Tip: The workout_exercises table is a classic junction table with payload — it's not just a many-to-many bridge, it also carries data of its own (sort_order, notes). When a junction table has its own attributes, give it a surrogate primary key rather than relying on a composite key.

Step 3: Build the Schema

We're using PostgreSQL throughout. A few deliberate choices you'll notice:

UUID primary keys via gen_random_uuid() — easier to merge data across environments, safer to expose in APIs, and no integer overflow risk on high-volume tables like exercise_sets
VARCHAR + CHECK instead of ENUM — PostgreSQL ENUMs are stored as types and are painful to extend without a migration; CHECK constraints on a VARCHAR column are easier to evolve
TIMESTAMPTZ everywhere — always store timestamps with time zone to avoid daylight-saving ambiguity
ON DELETE CASCADE on user-owned data — if a user deletes their account, their workouts, measurements, and goals go with them cleanly

-- Enable UUID generation (required once per database)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE users (
  user_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username      VARCHAR(50)  UNIQUE NOT NULL,
  email         VARCHAR(255) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  full_name     VARCHAR(100),
  gender        VARCHAR(20),
  height_cm     NUMERIC(5, 2),
  date_of_birth DATE,
  is_public     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- EXERCISES  (shared library, not user-owned)
-- ============================================================
CREATE TABLE exercises (
  exercise_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(100) UNIQUE NOT NULL,
  muscle_group  VARCHAR(50),   -- e.g. 'Chest', 'Back', 'Legs', 'Core'
  equipment     VARCHAR(50),   -- e.g. 'Barbell', 'Dumbbell', 'Bodyweight', 'Machine'
  exercise_type VARCHAR(30) NOT NULL
    CHECK (exercise_type IN ('Strength', 'Cardio', 'Flexibility', 'Balance')),
  instructions  TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- WORKOUTS
-- ============================================================
CREATE TABLE workouts (
  workout_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  title           VARCHAR(150),
  notes           TEXT,
  started_at      TIMESTAMPTZ NOT NULL,
  ended_at        TIMESTAMPTZ,
  duration_min    INT,           -- stored explicitly; avoids recomputing from timestamps
  calories_burned INT,
  mood            VARCHAR(20)
    CHECK (mood IN ('Great', 'Good', 'Okay', 'Bad', 'Terrible')),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- WORKOUT EXERCISES  (junction with payload)
-- ============================================================
CREATE TABLE workout_exercises (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workout_id  UUID NOT NULL REFERENCES workouts(workout_id) ON DELETE CASCADE,
  exercise_id UUID NOT NULL REFERENCES exercises(exercise_id),
  sort_order  INT NOT NULL DEFAULT 0,
  notes       TEXT
);

-- ============================================================
-- EXERCISE SETS  (the actual logged data)
-- ============================================================
CREATE TABLE exercise_sets (
  set_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workout_exercise_id UUID NOT NULL REFERENCES workout_exercises(id) ON DELETE CASCADE,
  set_number          INT NOT NULL,
  reps                INT,             -- NULL for pure cardio sets
  weight_kg           NUMERIC(6, 2),   -- NULL for bodyweight / cardio
  duration_sec        INT,             -- NULL for pure strength sets
  distance_m          NUMERIC(8, 2),   -- NULL for strength sets
  set_type            VARCHAR(20) NOT NULL DEFAULT 'Normal'
    CHECK (set_type IN ('Normal', 'Warmup', 'Drop Set', 'Failure')),
  rpe                 NUMERIC(3, 1)
    CHECK (rpe BETWEEN 1 AND 10)       -- Rate of Perceived Exertion
);

-- ============================================================
-- WORKOUT PLANS
-- ============================================================
CREATE TABLE workout_plans (
  plan_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id        UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  name           VARCHAR(150) NOT NULL,
  description    TEXT,
  difficulty     VARCHAR(20)
    CHECK (difficulty IN ('Beginner', 'Intermediate', 'Advanced')),
  duration_weeks INT,
  is_public      BOOLEAN NOT NULL DEFAULT FALSE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- PLAN EXERCISES
-- ============================================================
CREATE TABLE plan_exercises (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id          UUID NOT NULL REFERENCES workout_plans(plan_id) ON DELETE CASCADE,
  exercise_id      UUID NOT NULL REFERENCES exercises(exercise_id),
  week_number      INT,
  day_number       INT,       -- 1 = Monday … 7 = Sunday
  sort_order       INT NOT NULL DEFAULT 0,
  target_sets      INT,
  target_reps      INT,
  target_weight_kg NUMERIC(6, 2),
  rest_sec         INT DEFAULT 60
);

-- ============================================================
-- BODY MEASUREMENTS
-- ============================================================
CREATE TABLE body_measurements (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  measured_at  DATE NOT NULL,
  weight_kg    NUMERIC(5, 2),
  body_fat_pct NUMERIC(4, 1),
  chest_cm     NUMERIC(5, 1),
  waist_cm     NUMERIC(5, 1),
  hips_cm      NUMERIC(5, 1),
  bicep_cm     NUMERIC(5, 1),
  thigh_cm     NUMERIC(5, 1),
  notes        TEXT,
  UNIQUE (user_id, measured_at)  -- one snapshot per user per day
);

-- ============================================================
-- GOALS
-- ============================================================
CREATE TABLE goals (
  goal_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  goal_type     VARCHAR(50) NOT NULL
    CHECK (goal_type IN (
      'Weight Loss', 'Muscle Gain', 'Strength PR',
      'Cardio Endurance', 'Body Fat', 'Custom'
    )),
  title         VARCHAR(200) NOT NULL,
  target_value  NUMERIC(10, 2),
  current_value NUMERIC(10, 2),
  unit          VARCHAR(30),       -- e.g. 'kg', 'km', 'minutes'
  deadline_date DATE,
  status        VARCHAR(20) NOT NULL DEFAULT 'Active'
    CHECK (status IN ('Active', 'Achieved', 'Abandoned')),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- FOLLOWERS  (social graph — self-referencing junction)
-- ============================================================
CREATE TABLE followers (
  follower_id  UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  following_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  followed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (follower_id, following_id),
  CHECK (follower_id <> following_id)   -- can't follow yourself
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_workouts_user_started    ON workouts(user_id, started_at DESC);
CREATE INDEX idx_workout_exercises_wid    ON workout_exercises(workout_id);
CREATE INDEX idx_exercise_sets_weid       ON exercise_sets(workout_exercise_id);
CREATE INDEX idx_body_measurements_user   ON body_measurements(user_id, measured_at DESC);
CREATE INDEX idx_goals_user_status        ON goals(user_id, status);
CREATE INDEX idx_followers_following      ON followers(following_id);

Step 4: ER Diagram

The entity-relationship diagram for this schema looks like this at a high level:

Scroll or pinch to zoom · drag to pan · double-click to zoom in

Entity-relationship diagram for the fitness tracking app

Entity-relationship diagram — fitness tracking app

The workout_exercises table is the heart of the model — it sits between a concrete logged session (workouts) and the reusable exercise library, while exercise_sets hang off it to record the actual performance data set by set.

Step 5: Example Queries

Query 1 — Recent Workouts with Total Volume

"Volume" in strength training is total weight moved: sets × reps × weight. This query aggregates across all sets in each workout.

SELECT
  w.workout_id,
  w.title,
  w.started_at,
  w.duration_min,
  COUNT(DISTINCT we.exercise_id)               AS exercises_performed,
  COALESCE(SUM(es.reps * es.weight_kg), 0)     AS total_volume_kg
FROM workouts w
JOIN workout_exercises we ON w.workout_id = we.workout_id
LEFT JOIN exercise_sets es ON we.id = es.workout_exercise_id
WHERE w.user_id = 'your-user-uuid-here'
GROUP BY w.workout_id, w.title, w.started_at, w.duration_min
ORDER BY w.started_at DESC
LIMIT 10;

Query 2 — Personal Records Per Exercise

A personal record (PR) is the heaviest single-rep (or max weight for any rep count) ever logged for each exercise. DISTINCT ON is a PostgreSQL-specific shorthand for "give me the first row per group after ordering."

SELECT DISTINCT ON (e.exercise_id)
  e.name              AS exercise_name,
  e.muscle_group,
  es.weight_kg        AS pr_weight_kg,
  es.reps             AS reps_at_pr,
  w.started_at        AS achieved_on
FROM exercise_sets es
JOIN workout_exercises we ON es.workout_exercise_id = we.id
JOIN workouts w           ON we.workout_id = w.workout_id
JOIN exercises e          ON we.exercise_id = e.exercise_id
WHERE w.user_id    = 'your-user-uuid-here'
  AND es.set_type  = 'Normal'
  AND es.weight_kg IS NOT NULL
ORDER BY e.exercise_id, es.weight_kg DESC;

Query 3 — Body Weight Progress with Week-over-Week Change

Window functions let us compare each measurement to the previous one without a self-join. LAG() looks back one row within the ordered partition.

SELECT
  measured_at,
  weight_kg,
  body_fat_pct,
  weight_kg - LAG(weight_kg) OVER (ORDER BY measured_at)  AS change_kg,
  ROUND(
    (weight_kg - LAG(weight_kg) OVER (ORDER BY measured_at))
    / NULLIF(LAG(weight_kg) OVER (ORDER BY measured_at), 0) * 100,
    2
  )                                                        AS change_pct
FROM body_measurements
WHERE user_id   = 'your-user-uuid-here'
  AND measured_at >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY measured_at;

Analyst Tip: NULLIF(..., 0) prevents a division-by-zero error if a previous weight measurement happens to be 0. Always guard divisions with NULLIF when the denominator comes from data.

Query 4 — Workout Frequency and Volume Per Week

This query is useful for building a progress dashboard — it shows consistency week by week.

SELECT
  DATE_TRUNC('week', started_at)::DATE          AS week_starting,
  COUNT(DISTINCT workout_id)                     AS workouts_completed,
  ROUND(SUM(duration_min) / 60.0, 1)            AS total_hours,
  COALESCE(SUM(es.reps * es.weight_kg), 0)      AS total_volume_kg
FROM workouts w
LEFT JOIN workout_exercises we ON w.workout_id = we.workout_id
LEFT JOIN exercise_sets es     ON we.id = es.workout_exercise_id
WHERE w.user_id   = 'your-user-uuid-here'
  AND w.started_at >= NOW() - INTERVAL '12 weeks'
GROUP BY week_starting
ORDER BY week_starting DESC;

Query 5 — Social Activity Feed from Followed Users

This mirrors what you'd see in a "Friends Feed" — recent public workouts from people you follow, sorted by recency.

SELECT
  u.username,
  u.full_name,
  w.title        AS workout_title,
  w.started_at,
  w.duration_min,
  w.calories_burned,
  COUNT(DISTINCT we.exercise_id) AS exercises_count
FROM followers f
JOIN users u              ON f.following_id = u.user_id
JOIN workouts w           ON u.user_id = w.user_id
LEFT JOIN workout_exercises we ON w.workout_id = we.workout_id
WHERE f.follower_id = 'your-user-uuid-here'
  AND u.is_public   = TRUE
  AND w.started_at  >= NOW() - INTERVAL '7 days'
GROUP BY u.user_id, u.username, u.full_name,
         w.workout_id, w.title, w.started_at, w.duration_min, w.calories_burned
ORDER BY w.started_at DESC
LIMIT 20;

Extensibility

This schema is a solid foundation, but a production fitness app will keep growing. Here's how to extend it without breaking what's already there:

Feature	Extension
Streaks & Badges	Add a `streaks` table (current/longest streak per user) and an `achievements` table with a `user_achievements` junction
Nutrition Logging	Add `food_items`, `meals`, and `meal_items` tables; join to `workouts` via date
Wearable Integration	Add a `wearable_syncs` table storing raw HR/GPS/step data keyed to a `workout_id`
Exercise Media	Add an `exercise_media` table (video/image URLs keyed to `exercise_id`)
Workout Templates	Promote `workout_plans` to a first-class "template" concept with community sharing
Leaderboards	Materialize weekly volume totals into a `leaderboard_snapshots` table via a scheduled job to avoid expensive real-time aggregation

DBA Tip: For high-volume tables like exercise_sets (which can accumulate millions of rows for active users), consider PostgreSQL table partitioning by user_id range or by date. This keeps index sizes manageable and allows you to archive old data without downtime.

Summary

We've built a complete fitness tracking schema in PostgreSQL covering:

A hierarchical workout model — session → exercise → individual sets — that supports both strength and cardio in a single exercise_sets table
A shared exercise library decoupled from user data, so a "Bench Press" is defined once and referenced everywhere
Flexible goal and measurement tracking for body composition changes over time
A self-referencing social graph in the followers table, with a composite primary key and a CHECK constraint preventing self-follows
Production-ready details — UUID keys, CHECK constraints instead of ENUMs, TIMESTAMPTZ, cascading deletes, and a targeted index strategy

From here, you can try running these queries on your own PostgreSQL instance, sketching the full ER diagram, or loading in some sample data and experimenting with the window function queries. Understanding how the pieces connect — and why each design decision was made — is what separates a database that barely works from one you can confidently build a product on.

How to Design a Database Schema for a Fitness Tracking App in PostgreSQL