How to design a scalable data model for a personalized workout tracking app

Samuel Virgo

Ahmad Taleb

7th November 2024

16min read

Every app is built around data.

Data is stored inside of a database & how that database is designed is a major determinant of how fast, secure & scalable your final app will be.

In this article we are going to show you how to design a flexible data model for a personalized workout tracking app using SQL. We will design the workout data model so that your workout tracking app will remain fast, secure & scalable – even when you scale to millions of records.

We will cover:

Product requirements for a personalized workout tracking app
What data do workout apps track
The final workout tracking app data model
Questions about the data model

If you want to use the workout tracking app data model (or workout database schema) that we create in this article, you can login to Dittofi & install this database schema from a list of prebuilt templates. Before you read this article you should already know what is a relational database & some of the key terms used in database design.

Architected For Scale

Use Dittofi’s Personalized Fitness App data model template for free inside Dittofi.

Okay – so without further ado, let’s take a look at exactly what we expect our personalized workout app to do.

Product requirements for a personalized workout tracking app

Before we can start designing the data model for our personalized workout tracking app, we first need to list out what we want our product to do.

Below are some features that a workout tracking app should support:

Users can create an account & store user data such as first name, last name, email, passwords & other bits of biometric data such as the users weight & height.
Users can be different roles, for example admin, trainer & the customer.
Users can click to see a list of training programs. Each training program falls into a customizable category, such as weight loss.
Users can select & view a category of training programs. From here they will see different related training programs such. An example of this is shown below. As you can see there are two types of training program: Video Trainers & Workout Programs. Under each program category there are different Training Programs such as 12 Week Lean Muscle, 8 week Hardcore etc.

Each training program will include a name, a description, an image & a video URL to explain what the training program is about.
Inside each of the training programs, there will be a list of individual workouts such as Legs Calves & Abs, Back, Chest & Abs & so on. An image of this hierarchy of programs & workouts is shown below.

Each individual workout will have a list of exercises associated with it. Each exercise will have a description of the exercise, short videos of the exercise being carried out by trainers, a list of images that depict the different exercises. We will also include an option to give feedback to the workout so that we can personalize future sessions. An example of exercises that make up a Legs & Calves workout is shown below. As you can see the workout is made up of Leg Extensions, Machine Squats, Leg Presses etc.

We will also include:

The option to set the number of reps, sets & duration on a particular workout, if this is required for the particular exercise.
The ability for users to create training programs from a program template that admin users & trainers can publish for other users of the app to see & follow.
The ability to create questionnaires within your app that will capture information at different stages of your users journey. For example, questions asked during your user onboarding vs questions on why your users are looking to cancel their subscription.

Now that we have our product requirements, let’s move onto the database design for our personalized workout tracking app.

What data do workout apps track

Now that we have our product requirements, the next step is to work out what bits of data we need to keep track of inside of our workout tracking app & how these bits of data should relate with each other.

The goal is to create a data model where data is easily accessible, there is no duplication of data & where data that is dependent on other bits of data cannot be accidentally deleted, for example, it should not be possible to accidentally delete an exercise that is used within several workouts.

To build our data model we need to do three things:

Define our entities
Set our entity properties & constraints
Design our entity relationships

Defining our entities

For every app that you build, you need to track bits of data about certain things. In our case, because we are building a fitness app, we will be tracking bits of data like the Users of our system, the different Training Programs, Training Program Categories & so on. Each of these items (Users, Training Programs, Training Program Categories etc.) is known as an entity.

To come up with a list of entities, you need to look at the app product requirements. A shortened version of these are shown below. Note that the words highlighted in red are the bits of the data that our app needs to track & store information on. These words will be the entities for your app.

You can see from this that breaking your app down into requirements allows you to easily see what information you will need to track for your workout tracking app.

Action —

Before continuing:

You can try writing down a list of the entities will be included in your personalized workout tracking app

Set our entity properties & constraints

Each entity has a set of properties that define what is contained within the entity. For instance, you might have a User entity & this user will have a set of properties such as first name, last name, email, password, weight, height etc. This might look something like the image below.

To determine what properties each of your entities should have, you need to look at the product requirements. For instance, we said that each Training Program will need a name, description, a video URL & an image.

Action —

Before continuing:

You can try writing down a list of properties that you want to associate with each of your different entities.

In database design, entities & their properties are normally shown in what is called an Entity Relationship Diagram or ER Diagram. You can see an example of the User & Training Program entities shown below in an ER Diagram.

You can also think about entities & their properties like tables & columns that are used to store multiple instances of each entity. For example, below is an example of the Training Program table that stores several instances of the Training Programs & their entities.

Designing our entity relationships

Every data record that is stored inside each of our Data Models comes with a unique ID. For example, let’s take a look at our Training Program Entity.

As you can see, each row of data stored inside of the Training Program Entity has an ID column. This is a unique auto incrementing ID. The ID will increase by one for each new record added to the Training Program table.

Now, if I want to say that the Training Program has a set of Workouts that are included in the Training Program, I can create what is called a foreign key in my Workouts table. I can then reference the Workouts that I want to include inside of my Training Programs. An example of this is shown below.

We will create relationships between tables in this way in order to ensure that there is no duplicated data & that data that is related to certain workouts cannot be accidently deleted. In the next section, we will first show you the final data model for the personalized workout tracking app. We will then ask & answer questions about the data model to explain how the data model works.

The final workout tracking app data model

With all of this in mind, the final data model for our fitness app will look like the following. As you can see, there are several entities with different properties. Entities are related to others in such a way as to ensure that data is (A) not repeated unnecessarily & (B) that data integrity is maintained.

Questions about the data model

Rather than simply explaining how all of the tables interrelate, let’s go through & answer some questions about the data model. The function of the various tables within the data model will become clear as we go along.

How can we create different user roles?

Our User entity has we have the following core properties:

first_name: to store the users first name. This is a mandatory text field that is not allowed to be null.
last_name: to store the users last name. This is a mandatory text field that is not allowed to be null.
email: to store the users email. This is a mandatory text field that is not allowed to be null.
password: to store the users password. This is a mandatory password field that is not allowed to be null.
role: to store the users role. This is a mandatory password field that is not allowed to be null.

The role property can store the user role as any text value. When you build your apps user experience, you can check a users role so as to give users different functionality depending on their different role. For instance, you can allow users with the role “trainer” to be able to create training programs vs customers who are just using the app to access training programs that are created by trainers.

Also included in the users table are the option to add biometric data. This information can be collected at sign up & can be used to give the app users a more personalized experience. For instance, you can use this information to recommend training programs base on weight, height, age etc.

Why do we break apart training programs from training categories?

In our fitness app data model we have both training_programs & training_program_category entities. The training_program entity stores a list of training programs with their name, description & images. These training programs are categorized by referencing the training_program_categories table which stores a customizable list of training program categories.

Training programs are grouped into training program categories by joining the foriegn key property, training_program_category_id in the training_programs entity to the id field in the training_program table. For instance, in the screenshot below, you can see that we have a “Muscle Building” category with an id of 1. This is linked to the training programs with the training_program_category_id of 1.

As a side note, the information architecture that you choose for your app is going to be very important to help your users navigate through your app & for you to be able to provide a more personalized experience for your app users.

You will notice also that training the training_programs entity has a user_id property. This links back to the data inside of the users table, so that we can know which user created which workout plan.

How do workouts relate to individual training programs?

Training programs are made up of different workouts that are stored in our workouts table. Workouts typically follow some sort of schedule. For example, there might be schedules of workouts for Week 1, Week 2, Week 3, Week 4 & so on. An example of this is shown below.

To achieve this feature, we have a Workouts entity that stores individual workouts such as Chest Day, Leg Day, Back Day & so on. Each workout has a week_number & day_number property which can be used to say which day of the week the workout relates to. This is shown below.

Each workout belongs to a training program. This relationship is achieved by using the foreign key training_program_id in the Workouts entity to reference the Training Program to which the Workout belongs. This is shown below.

The Workouts entity also has a week number & day number properties. This allows us to say, for example, that the workout “Chest & Abs” belongs to the Training Program “12 week lean muscle” with a week number 1 & day number 2.

What is the Exercise entity for?

The Exercise entity stores a master list of all of the exercises.

Each workout then draws from the master list of exercises in order to develop a specific workout. For instance, let’s say that we have a Workout called “Chest & Abs”. On this workout, users are going to be focusing on building strong chest & abdominal muscles. In this case, the workout draws on a set of workouts that are related to building strong chest & abdominal muscles.

How do I create a customised workout from different exercises?

To associate a particular exercise with a workout, we have a workout_items table. This table references the original workout with the use of a workout_id. Then there is an exercise_id that references the exercise stored inside of the exercise entity. This combination allows you to create a list of exercises that are associated with a single or multiple workouts. An example of this is shown below.

The rank property in the workout_items table will control the order that the workouts are displayed in. For example, for the Check & Abs workout shown in the above example the “Decline chest press” has a rank of 1 so this exercise comes first & then the Decline dumbell flyes have a rank of 2, so these are listed second.

Finally, the workout_items table is joined to the sets table. The sets table comes with the option to input sets, reps & duration for each exercise within your workout. In this case, the sets table contains a reference to the workout which the sets, reps &/or duration apply. For instance, below there is an example of the entities table that references three sets of 8 reps for the workout item with an ID of 5 which, in our case is is the item with exercise ID 1 or decline bench press. Also in this table are three sets of 10 reps for workout item with an ID of 6 which, in our case is the workout item with an exercise ID of 2 or dumbbell flyes.

Why do we need an workout_items table, why can we not just just list out the items in the workouts table?

Let’s assume that we don’t have a workout_items table & we just want to add a new column to our workouts table that will list out all of the exercises that will be included in this table. In this case, if a workout has many items, we will need to list out a reference to all of those exercises in a single column.

This would work however, this can lead to difficulties down the road, since if we wanted to get all of the exercises associated with a particular workout, we would need to get this list of workouts & then loop through it to get out the individual exercises. In database design it is better practice to store this list of exercises in a separate table & create a many to one relationship i.e. many exercises to one workout. This can save a lot of time down the road when running data analytics to see, for instance, how many times a particular exercise is used in different workouts across the entire platform. This type of information can be really useful to try & better understand how your app is being used. For instance, if you’re developing a workout tracking app to help people overcome shoulder injuries & then you find out that the majority of workouts developed on your app are for building back muscles, this information can help you pivot quickly & double down on where people are finding success within your app.

Why do we have a Questionnaire entity that is joined to a Question entity?

Asking users questions about why they have signed up to our app, what they are looking to achieve with the app & so on are very important to identify the problem that you’re trying to solve with your product. This will ultimately help you find product-market fit much faster, as opposed to just throwing spaghetti at the wall & seeing what sticks.

Rather than trying to throw every question into the onboarding & prolonging the users time to actually experience our app, as we iterate on our app, we can look for different places to present different questions. For example, maybe before the user cancels their subscription, we can ask them why they are cancelling their subscription.

The most effective way to do this is to have a Questionnaire entity where we create categories of questions. Categories might be for instance, Onboarding questions, Customer Feedback questions & so on. We can then store lists of questions inside of our Questions entity & have these categorized into different Questionnaires. You can see an example of this below.

What Would You Add to This Data Model?

The data model that we have designed in this article lets people create accounts & sign up, create training programs & follow training programs. It has been designed to allow users to give feedback on the exercises that they do, set goals & answer customizable lists of questions about how they are experiencing the workout app.

Should we consider building these features into it? What changes need to be made to add these features?

Let us know your ideas!

If you would like to use this data model, you can install the data schema as a template from inside Dittofi’s hybrid no-code platform & immediately incorporate the design into your app development.