Workout Admin Application

Software + Skills

  • Python
  • MySQL
  • DML & DDL
  • Flask
  • HTML/CSS/JS
  • Jinja2

Team Members

  • Jun Kim
  • Michael Stearns

Concept

This project captures the major needs for a workout application for an end User. The application serves the User by creating, editing, and deleting various details of their workouts stored in a MySQL database, including: The Users, Exercises that can be done by a user, Users associated with each of the various Exercises, Muscle Groups chosen for the workout by the User, the Muscles contained in each Muscle Group, Exercises associated with each Muscle Group, and Equipment used by each of the exercises, if applicable.

ENTITY RELATIONSHIP DIAGRAM

SCHEMA

USERS

USERS: records the details of users that use the database

Columns in table:

  • user_id: INT, auto_increment, unique, NOT NULL, Primary Key (PK)
  • username: VARCHAR(255)

Relationships:

  • A M:M relationship between USERS and MUSCLE_GROUPS. USERS can choose to target one or many MUSCLE_GROUPS, and MUSCLE_GROUPS can be attributed to zero or many USERS. When USERS accounts are created, they will choose which MUSCLE_GROUPS to target using the MUSCLE_GROUPS table, therefore, USERS must have at least one MUSCLE GROUP. See the USERS_MUSCLEGROUPS table.

EXERCISES

EXERCISES: records the different exercises that target different muscles. Each exercise will have at most one equipment that is associated with it.

Columns in table:

  • exercise_id: INT, auto_increment, unique, NOT NULL, PK
  • exercise_name: VARCHAR(255), NOT NULL
  • weight: DECIMAL
  • set_count: INT, NOT NULL
  • rep_count: INT, NOT NULL
  • equipment_required: INT, FK to EQUIPMENT.equipment_id

Relationships:

  • A M:M relationship between EXERCISES and MUSCLE_GROUPS is implemented. EXERCISES can be targeted by one or many MUSCLE_GROUPS, and MUSCLE_GROUPS can have one or many EXERCISES. See MUSCLEGROUPS_EXERCISES table.
  • A M:1 relationship between EXERCISES and EQUIPMENT. EXERCISES will be associated with zero or one piece of EQUIPMENT. EQUIPMENT can be used for one or more EXERCISES. Foreign Key for this relationship (EQUIPMENT.equipment_id) is stored within the EXERCISES table.

MUSCLE GROUP

MUSCLE_GROUPS: records the different muscle groups chosne by the USERS for a workout.

Columns in table:

  • muscle_group_id: INT, auto_increment not NULL, PK
  • muscle_group_name: TEXT, NOT NULL

Relationships:

  • A M:M relationship between MUSCLE_GROUPS and USERS is implemented. MUSCLE_GROUPS can be attributed to one or many USERS, and USERS can target one or many MUSCLE_GROUPS. See the USERS_MUSCLEGROUPS table.
  • A M:M relationship between MUSCLE_GROUPS and EXERCISES is implemented. MUSCLE_GROUPS can be worked by one or multiple EXERCISES, and EXERCISES can target one or multiple MUSCLE_GROUPS (such as compound exercises). See MUSCLEGROUPS_EXERCISES table.
  • A 1:M relationship between MUSCLE_GROUPS and MUSCLES is implemented. MUSCLE_GROUPS can be attributed to one or multiple MUSCLES, however MUSCLES are always attributed to just one MUSCLE_GROUP. Foreign Key for this relationship (MUSCLE_GROUPS.muscle_group_id) is stored within the MUSCLES table.

EQUIPMENTS

EQUIPMENT: a table to store the names of different equipment used for each type of exercise (barbell, single kettlebell, dual kettlebell, curl bar, bands etc.)

Columns in table:

  • equipment_id: INT, auto_increment, unique, not NULL, PK
  • equipment_name: VARCHAR(255), NOT NULL

Relationships:

  • A 1:M relationship between EQUIPMENT and EXERCISES. EQUIPMENT can be used for one or many EXERCISES, while EXERCISES will use zero or one EQUIPMENT. Foreign Key for this relationship (EQUIPMENT.equipment_id) is stored within the EXERCISES table.

Many-to-Many Composite / Association Tables

USERS EXERCISES

USERS_EXERCISES: stores the many-to-many relationship between USERS and EXERCISES.

Columns in table:

  • user_id: INT, FK to USERS.user_id
  • exercise_id: INT, FK to EXERCISES.exercise_id

MUSCLE GROUP EXERCISES

MUSCLEGROUPS_EXERCISES: stores the many-to-many relationship between MUSCLE_GROUPS and EXERCISES.

Columns in table:

  • muscle_group_id: INT, FK to MUSCLE_GROUPS.muscle_group_id
  • exercise_id: INT, FK to EXERCISES.exercise_id