Fitness & Nutrition Tracker DB

IT 547 Database Design · Walsh College · Summer 2025

Fitness & Nutrition Tracker DB

A normalized eight-table relational database for workouts, meals and body progress, loaded into a real SQLite engine running in your browser so you can run her queries or write your own.

SQLRelational modelingERDIndexingWindow functionsSQLite (WebAssembly)
…tables in the schema
…rows loaded from her data script
…secondary indexes from D2
…foreign keys enforced

Live demo

A real SQLite database is running in this page. Pick one of her queries or edit the SQL and press Run (Ctrl+Enter).

Loading SQLite engine…

Starting the database…

Entity-relationship diagram

Eight entities: six core tables plus two associative tables (WorkoutExercise, MealItem) that resolve the many-to-many links. Click a table to query it.

Schema browser

Read live from sqlite_master and PRAGMA table_info.

Results

Both charts are SQL queries against the live database, so they update if you insert, update or delete rows above.

Meal calories: stored total vs sum of food items

Computed from MealItem × FoodItemStored in Meal.TotalCalories

Exercise frequency across all workouts

How it works

  1. Modeled the domain (D1). Drafted an ERD with User, Workout, Exercise, Meal, FoodItem and Progress, and added WorkoutExercise and MealItem as associative tables so a workout can hold many exercises and a meal many foods.
  2. Implemented the schema (D2). Wrote CREATE TABLE scripts with auto-increment keys, typed columns, a unique email and ON DELETE CASCADE foreign keys.
  3. Indexed for the workload. Added 14 secondary indexes on foreign keys, date columns and filter fields (MuscleGroup, MealType), and avoided indexing every column to keep writes cheap.
  4. Populated sample data (D3). Two example users, five exercises, four workouts, five foods, six meals and seven progress entries, linked through the associative tables.
  5. Wrote analytical queries. Daily calories, top exercises, progress averages, a ROW_NUMBER() window for each user's biggest meal and a muscle-group filter. This page adds weekly volume, a calorie consistency check, daily macros and an index plan check.

Porting note: her scripts target MySQL. To run them in SQLite the page makes two mechanical edits before executing: INT AUTO_INCREMENT PRIMARY KEY becomes INTEGER PRIMARY KEY AUTOINCREMENT, and ENGINE=InnoDB is removed. Foreign keys are switched on with PRAGMA foreign_keys = ON. Her data and query scripts run unchanged.

Data

Sample (synthetic) records she wrote for the population script: two example users with @example.com emails, covering 1 to 4 August 2025. They are not real people or measurements.

Tables

Preview: meal log (first 10 MealItem rows, joined)

Source files: D2_component_1.sql (schema), D2_component_2.sql (indexes), D3_component_1.sql (data), D3_component_2.sql (queries). D3_combined.sql is the data and query scripts concatenated.