Dev Journal #8 - Exercise Log Data Strucutre



Exercise Data Structure

I am ready to tackle the more complicated structure of the app, the exercise logging system 😤
Recall this part of the ERD from my previous post:

I purposefully left the DefaultSettings entity to be incomplete because I had no idea 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. After a bit of head scratching, here is what I came up with.



The table organizes the fields that I want to capture for each type of exercise, and the individual table cells describe whether the particular field is logically applicable to the exercise. For example, the first row describes the allowable data entry fields for 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 allow using certain apparatus types such as the parallel bars, the parallettes and the floor. It also doesn’t allow variation of body angles or leverages and there is no straight arm strength training. The easiest, yet not the most elegant solution would be to create a giant table with columns representing each of these field.

ExerciseSettings

exercise_name allow_assisted allow_bodyweight ... allow_bent_arm allow_straight_arm
Pull upTrueTrue...TrueFalse
..................
Push upFalseTrue...TrueFalse
Pull upTrueTrue...TrueTrue

The problem with this implementation is that the settings will be all saved under a single hierarchy and this makes it hard to implement logics that require 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 user 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 and write simpler codes. In other words, I would rather have an entity that represents subgrouping of these fields that belong together. The pros are that the app will be less prone to bugs and it will be more straight forward to program the logics. 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 the 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 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 exercise. 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:

WeightProgressionTypes

id weight_progression_type
0assisted
1bodyweight
2weighted

GripWidthTypes

id grip_width_type
0narrow
1standard
1wide

PlyometricTypes

id plyometric_type
0strict
1plyometric

GripTypes

id grip_type
0pronated
1neutral
2supinated

MuscleContractionTypes

id muscle_contraction_type
0concentric
1eccentric
2isometric

UnilateralTypes

id unilateral_type
0one arm
1archer

ApparatusTypes

id apparatus_type
0ring
1bar
2parallel bars
3parallettes
4floor

AngleTypes

id angle_type
0incline
1neutral
2decline

LeverageProgressionTypes

id leverage_progression_type
0tuck
1advanced tuck
2single leg
3straddle
4full

ArmStrengthTypes

id arm_strength_type
0bent arm
1straight arm

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 table that are directly linked to the Exercises table:

ExerciseTypes

id exercise_type
0pull vertical
1pull horizontal front
2pull horizontal back
3push vertical up
4push vertical down
5push horizontal

SkillLevels

id skill_level
0beginner
1intermediate
2advanced
3elite

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 entities. 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 fillling 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 ExerciseType is defined as follows:

class ExerciseType(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'
    EXERCISES = ((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'))
    exercise_type = models.CharField(max_length=50, choices=EXERCISES)

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

    def __str__(self):
        return self.exercise_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_strength_type = models.ForeignKey(ArmStrengthType, on_delete=models.PROTECT, blank=True, null=True)
    grip_type = models.ForeignKey(GripType, 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)
    exercise_type = models.ForeignKey(ExerciseType, 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 actually 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(ExerciseType)
...
admin.site.register(DefaultSetting)

After inserting all the default values for Exercise, ExerciseType, 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 inital 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.exercisetype",
    "pk": 1,
    "fields": {
      "exercise_type": "PULL_VERTICAL"
    }
  },
  ...
  {
    "model": "exercises.weightprogressionoption",
    "pk": 26,
    "fields": {
      "exercise": 11,
      "weight_progression_type": 3
    }
  }
]
< Previous Post Next Post >

Comments