Dev Journal #8 - Exercise Log Data Strucutre

Exercise Data Structure

It’s time to tackle the exercise logging system 😤 Recall the ERD diagram from the previous post:

I purposefully left the DefaultSettings entity to be incomplete because I didn’t have a full understanding of what was going to be required when I initially mapped the entity. Since then, I have done some categorization exercises to understand my data better. Here is what I came up with:

This boolean table organizes the fields that I want to capture for each type of exercise. The individual table cells describe whether that particular field is logically applicable to each exercise. For example, the first row describes the allowable data entry fields for the pull up exercise. Pull up can be logged as assisted pull up, bodyweight pull up or weighted pull up. It can also have three different kinds of grip width, plyometric variation, etc. It doesn’t make sense to enter data with certain apparatus types such as the parallel bars, the parallettes and the floor, therefore, those selections are indicated as disabled or N/A. Variation of body angles or leverages and straight arm strength training is also not applicable to the pull up exercise so they are indicated as disabled as well.

The easiest, yet, not the most elegant solution would be to create a giant table with columns representing each of these fields - transforming the above spreadsheet literally into a single table with multiple boolean columns.

ExerciseSettings

The problem with this implementation is that the settings will be all saved under a single hierarchy and this makes it hard to implement logic that requires certain settings to behave together as a group. For example, allow_assisted, allow_bodyweight, and allow_weighted should belong to the common settings group called weight_progression. If I want to create a UI dropdown that allows users to choose a weight progression for their exercise, I will have to programmatically declare that these three columns are to be used together. Although this is not incorrect, I would prefer to have an explicitly defined database structure that allows me to write simpler codes. In other words, I would rather have an entity that represents the subgrouping of these fields that belong together. The pros are that the app will be less prone to bugs and it will be more straightforward to program the logic. However, the cons are that the application will become less flexible for new implementations because it has been overengineered to function for a specific use case. In this case, I think it seems more correct to refactor these fields into smaller entities. With this new design in mind, I reorganized the Exercises and its related entities.

The settings are now broken down into two layers. The bottom layer holds a distinct set of constants that populates the settings field. The tables that store these constants are named *Types tables. These hold the list of named values with an identifiable primary key. The upper layers are named *Options tables and they link the Exercises entities to *Types entities. The *Options entities define whether certain field types should be applicable for different exercises. The DefaultSettings table holds the default field settings each exercise should initially provide to the users. The reason I made this table is because the user may be overwhelmed with all the fields they need to enter in order to simply log a set of exercises. If the UI initially provides the default values that people will most likely use, users can scroll through it quickly and edit only the fields that require changes from those default values. Later on, I will also be implementing user-defined settings where users can save their custom settings and load them up with ease.

I will list these *Types tables below:

AngleTypes

ApparatusTypes

ArmMotionTypes

GripDirectionTypes

GripWidthTypes

LeverageProgressionTypes

MuscleContractionTypes

PlyometricTypes

WeightProgressionTypes

UnilateralTypes

Each *Types and *Options tables are added with an autoincrementing integer column to be used as the primary key. Even though the string keys are unique and also qualify as the primary key, I prefer using numeric IDs because they allow more flexibility to value changes and are more optimal for performance.

There are also two other constants tables that are directly linked to the Exercises table:

MotionTypes

SkillLevels

Django Exercise Models

Now that I have a clear blueprint of my data structure, I just need to code the model class definition of each entity. Unlike the UserProfile model, these entities will be read-only. They will be used to populate the selectable dropdown values that users can choose when filling out their exercise logs. Since these fields are not editable, I will provide the values through ChoiceField. I also want to make sure that each choice field is unique. Regular users won’t be able to edit this model in the first place but I want to prevent even the administrators from making mistakes as well. I also define a simple __str__() function so that I can easily recognize the model object in the admin UI. An example of the model MotionType is defined as follows:

class MotionType(models.Model):
    PULL_VERTICAL = 'PULL_VERTICAL'
    PULL_HORIZONTAL_FRONT = 'PULL_HORIZONTAL_FRONT'
    PULL_HORIZONTAL_BACK = 'PULL_HORIZONTAL_BACK'
    PUSH_VERTICAL_UP = 'PUSH_VERTICAL_UP'
    PUSH_VERTICAL_DOWN = 'PUSH_VERTICAL_DOWN'
    PUSH_HORIZONTAL = 'PUSH_HORIZONTAL'
    MOTION_TYPES = ((PULL_VERTICAL, 'Vertical Pull'),
                    (PULL_HORIZONTAL_FRONT, 'Horizontal Front Pull'),
                    (PULL_HORIZONTAL_BACK, 'Horizontal Back Pull'),
                    (PUSH_VERTICAL_UP, 'Vertical Upward Push'),
                    (PUSH_VERTICAL_DOWN, 'Vertical Downward Push'),
                    (PUSH_HORIZONTAL, 'Horizontal Push'))
    motion_type = models.CharField(max_length=50, choices=MOTION_TYPES)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['motion_type'], name='unique_motion_type')
        ]

    def __str__(self):
        return self.motion_type

I apply similar definition to SkillLevel model and all the *Type models.

The DefaultSetting model consists of optional foreign key fields for each type of setting.

class DefaultSetting(models.Model):
    angle_type = models.ForeignKey(AngleType, on_delete=models.PROTECT, blank=True, null=True)
    apparatus_type = models.ForeignKey(ApparatusType, on_delete=models.PROTECT, blank=True, null=True)
    arm_motion_type = models.ForeignKey(ArmMotionType, on_delete=models.PROTECT, blank=True, null=True)
    grip_direction_type = models.ForeignKey(GripDirectionType, on_delete=models.PROTECT, blank=True, null=True)
    grip_width_type = models.ForeignKey(GripWidthType, on_delete=models.PROTECT, blank=True, null=True)
    leverage_progression_type = models.ForeignKey(LeverageProgressionType, on_delete=models.PROTECT, blank=True,
                                                  null=True)
    muscle_contraction_type = models.ForeignKey(MuscleContractionType, on_delete=models.PROTECT, blank=True, null=True)
    plyometric_type = models.ForeignKey(PlyometricType, on_delete=models.PROTECT, blank=True, null=True)
    unilateral_type = models.ForeignKey(UnilateralType, on_delete=models.PROTECT, blank=True, null=True)
    weight_progression_type = models.ForeignKey(WeightProgressionType, on_delete=models.PROTECT, blank=True, null=True)

The Exercise model will have its unique name field with three other foreign key fields as I have planned out in the entity-relationship diagram above.

class Exercise(models.Model):
    PULL_UP = 'PULL_UP'
    MUSCLE_UP = 'MUSCLE_UP'
    INVERTED_ROW = 'INVERTED_ROW'
    FRONT_LEVER = 'FRONT_LEVER'
    BACK_LEVER = 'BACK_LEVER'
    HANDSTAND = 'HANDSTAND'
    HANDSTAND_PUSH_UP = 'HANDSTAND_PUSH_UP'
    V_SIT = 'V_SIT'
    DIPS = 'DIPS'
    PUSH_UP = 'PUSH_UP'
    PLANCHE = 'PLANCHE'
    EXERCISES = ((PULL_UP, 'Pull Up'),
                 (MUSCLE_UP, 'Muscle Up'),
                 (INVERTED_ROW, 'Inverted Row'),
                 (FRONT_LEVER, 'Front Lever'),
                 (BACK_LEVER, 'Back Lever'),
                 (HANDSTAND, 'Handstand'),
                 (HANDSTAND_PUSH_UP, 'Handstand Push Up'),
                 (V_SIT, 'V-Sit'),
                 (DIPS, 'Dips'),
                 (PUSH_UP, 'Push Up'),
                 (PLANCHE, 'Planche'))
    exercise_name = models.CharField(max_length=50, choices=EXERCISES)
    motion_type = models.ForeignKey(MotionType, on_delete=models.PROTECT)
    skill_level = models.ForeignKey(SkillLevel, on_delete=models.PROTECT)
    default_setting = models.ForeignKey(DefaultSetting, on_delete=models.PROTECT)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['exercise_name'], name='unique_exercise_name')
        ]

    def __str__(self):
        return self.exercise_name

Lastly, the *Option models are uniquely defined by the combination of the exercise and the setting type.

class MuscleContractionOption(models.Model):
    exercise = models.ForeignKey(Exercise, on_delete=models.CASCADE)
    muscle_contraction_type = models.ForeignKey(MuscleContractionType, on_delete=models.CASCADE)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['exercise', 'muscle_contraction_type'],
                                    name='unique_exercise_muscle_contraction_type')
        ]

    def __str__(self):
        return '%s - %s' % (self.exercise, self.muscle_contraction_type)

After defining the definitions, I ran the makemigrations and migrate command to create the tables in the PostgreSQL database. I then registered each of these models in the admin.py so that I can use the admin UI to insert the values.

admin.site.register(Exercise)
admin.site.register(MotionType)
...
admin.site.register(DefaultSetting)

After inserting all the default values for Exercise, MotionType, SkillLevel, DefaultSetting, *Option, and *Type models, I made a backup of these inserted values as JSON file by using the dumpdata command. I can always load the initial data back from this backup after dropping and recreating the database so that I don’t have to go through the tedious process of re-inserting all the default values.

[
  {
    "model": "exercises.motiontype",
    "pk": 1,
    "fields": {
      "motion_type": "PULL_VERTICAL"
    }
  },
  ...
  {
    "model": "exercises.weightprogressionoption",
    "pk": 26,
    "fields": {
      "exercise": 11,
      "weight_progression_type": 3
    }
  }
]