A Marathon Training App Data Model

Do you dream of running a marathon? Let’s look at the data model for an app that could take you from lazy couch potato to marathoner.

What do you need to run a marathon? You’ll need enthusiasm and determination. A good pair of running shoes. And lots of physical training! Let’s say you have an app that helps you go from novice runner to marathon finisher. What would the data model look like?

In this article, we’ll examine the data model behind a marathon training app.

What Should a Marathon Training App Do?

Any training app usually comes with some options. In this case, we’d expect the app to support training for different types of runs (a full marathon, a half marathon, a 5k) and for different training schedules (anywhere from eight to 24 weeks). The app would capture your basic details, including age, gender, and current running status. It should also allow you to set a goal and a start date. The app will use this information to create a training plan for your upcoming running event. The more you progress with your plan, the closer you’d be to your goal.

Let’s go over the key requirements of this app. It should:

• Capture a user’s name, age, gender, etc. during the registration process.
• Display of a list of goals (e.g. walking, running, cycling, etc.) with an associated target distance.
• Allow users to set a goal, target distance, and start date.
• Create a detailed personal training plan for individual users that considers their age, gender, and current fitness level. This training plan includes:
• Activities, such as running.
• Dates for activities to be started and completed.
• Distances (e.g. running for 5 kilometers)
• Suggested pace (e.g. 5 km/h) and approximate completion times (1 hour).
• Rest days. It is important to build these into a physical fitness plan.
• Goal end date, e.g. when the user would be ready to run their chosen event.
• Capture the progress of training plan activities, including when (or if) each activity was started, how close the user is to completing it, and when it was finished.
• Adjust training plans as needed. For example, a runner might get sick or injured and may not follow their original schedule; in that case, the original plan will need to be extended or modified.
• Capture titles earned by the user. In running, these are based on events successfully completed, e.g. 5K runner, 10K runner, half marathon runner, or full marathon runner. These titles are earned as runners progress with their training.

The Data Model

The data model supporting such an app consists of three subject areas:

1. Users and Titles
2. Goals and Activities
3. User Goals and Transitions

We’ll discuss each subject area in the order it is listed.

Subject Area 1: Users and Titles

This app will be used by more than just novice runners. Running events are very demanding and strenuous; even seasoned marathoners need to train for upcoming marathons. No one runs a full marathon overnight or after a single training course. It is a gradual process.

As we mentioned earlier, runners earn various titles based on events of different lengths. As a runner progresses in their training, they will be able to run longer events and earn more titles. The tables in this subject area are defined with that in mind.

The “registered_user” table holds basic details about users. These details are captured during the registration process. This includes two key factors that influence training plan design: age (derived from date_of_birth) and gender. These are important because different genders and age groups train differently, even if they are competing in the same event. A 19-year-old boy will need a different training plan than a 45-year-old woman.

The “running_event” table stores a list of all official running events. This could include international events. All the fields are self-explanatory.

The “title” table primarily stores the “credentials” of runners: the distance they cover and the time it took during an official event. Key points about titles and their distributions are:

• Each marathon event has its own list of titles.
• Usually these titles are given to runners at the end of a milestone (on a track) or when they finish (e.g.. cross the finish line of a marathon).
• The same title can be given to multiple of runners, provided they all meet its conditions. These include (1) the minimum distance to be covered, and (2) the maximum time to cover this distance.
• If titles are defined at intermediate milestones on a track, the runner keeps the only highest title they have earned.

With this understanding of titles, the columns in the “title” table should be self-explanatory. ☺

The “user_title” table stores all the titles that users have earned. The only difference is that here we capture the runner’s time in seconds instead of minutes.

Subject Area 2: Goals and Activities

No one can motivate you to run a marathon if you don’t want to. You have to work up your own zeal. One way to stay motivated is to set and reach goals. The next two subject areas deal with setting and meeting goals.

First, we’ll look at the Goals and Activities subject area. It contains three tables:

1. “goal” holds details about the goals defined in the app.
2. “activity” stores information about various kinds of training activities, like walking, speed walking, running, swimming, cycling, etc.
3. “goal_activity” stores details about activities needed to achieve a goal.

It’s important to understand that the same goal is reached differently by different users. Again, a 15-year-old girl will have a training plan and set of activities that are different than a 40-year-old man. Considering these facts, we have put the following columns in the “goal” table:

• distance_to_run – The distance a runner should be able to run at the end of this goal.
• target_time_in_min – The maximum time needed to cover this distance.
• gender – Which gender this goal is for.

A goal can be created for an age group, say 15-20 or 35-40. How we train changes a bit as we age, so we’ve added two more columns to “goal”:

• starting_age – The minimum age for this goal.
• closing_age – The maximum age for this goal.

People can dream big, but the only way to really make things happen is to progress gradually. This app restricts how users make goals; they must complete smaller, achievable goals before trying for the bigger ones. A couch potato can dream of running a full 26.2 mile\42.2km marathon, but they should start working towards a 5K run first.

The “goal” table handles restrictions by means of the following columns:

• current_run_distance_per_week – The minimum running distance achieved before a user can set a certain goal; and
• current_min_title_id – The minimum title users must hold to set this goal.

If these prerequisites are not met, that goal will not be available to the user. However, both of these columns are nullable; there will be some goals that have no prerequisite fitness requirements.

Let’s move on to the “goal_activity” table. Most of these columns serve an obvious purpose. I will just comment on two, starting with the seq_of_day column. This is a number column that holds values that signify the day when an activity is to be performed. Obviously, this sequence starts from 1 for any goal. It can never be ZERO or NULL. Numbers may not be consecutive for a goal; this would mean rest days have been set. Days for which there are no records in this table are actually rest days.

Next, we have the distance_to_cover column. This is nullable, as there are activities (like yoga, stretching, and weightlifting) in which distance does not matter. Having said this, notice that the min_pace and max_pace columns in the “activity” table are also nullable.

Subject Area #3: User Goals and Transitions

This subject area is all about user-created goals and app-created activity plans. Actual dates are important here, and the seq_of_day column in the “goal_activity” table plays a major role in rendering plan dates, as does the start_date chosen by users for their goals.

The “user_goal” and “transition_plan” tables are both mostly self-explanatory. There are just a few columns that we should highlight.

In the “user_goal” table:

• is_active – Shows if a user is still progressing on this goal. All in-progress goals would have a ‘Y’ in this column. This column enables the app to restrict users to setting one goal at a time.
• create_date – The date when a goal was created.
• start_date – The date when a goal was actually started. It may not be the same as the create_date.
• expected_end_date – An end date, computed by the app after it makes a transition plan for the user.
• actual_end_date – When the goal was actually completed. There can be deviations from the training plan, so we need this column to capture the actual end date. The app may give an option to skip a day or to advance the training schedule by a day or so. In such cases, the actual_end_date will certainly differ from the expected_end_date.

In the “transition_plan” table:

• is_complete – Indicates whether an activity was skipped, hasn’t been started yet, or is finished. It will hold a ‘Y’, ‘N’, or a blank.
• start_timestamp – The timestamp when an activity was started.
• end_timestamp – The timestamp when the activity was completed.

Since we understand there can be gaps in training (due to illness, injury, or lack of motivation), this table contains three different dates:

• original_calendar_date – A calendar date denoting when an activity needs to be performed. This value is populated when the app generates a training plan.
• planned_calendar_date – Initially, this column remains blank. A date is populated as and when a change is made in the training plan.
• actual_calendar_date – This column is populated as soon as the user marks an activity as complete. This is the date when the activity is actually finished.

The planned_calendar_date and actual_calendar_date columns are nullable; they are not populated during the initial plan generation.

Another three columns in this table are nullable so that this data model can handle all possible scenarios for an activity in progress. Here are some examples:

• An activity that is not started yet –
• is_complete – NULL
• start_timestamp – NULL
• end_timestamp - NULL
• An activity that was started but not completed –
• is_complete – NULL
• start_timestamp – VALUE
• end_timestamp - NULL
• An activity that was skipped –
• is_complete – ‘N’
• start_timestamp – NULL
• end_timestamp - NULL
• An activity that was completed –
• is_complete – ‘Y’
• start_timestamp –VALUE
• end_timestamp - VALUE

What Would You Change About This Data Model?

Training for a marathon is about more than just exercise. Marathoners have to tweak every aspect of their lifestyles, starting from their daily food intake, their mental strength, and even the amount of sleep they get.

An effective app must be able to organize, plan, and track all aspects of training. Does our data model cater to all these aspects? What changes are required to make it a full-fledged training app?

Please share your views and suggestions in the comments section.