Designing a database for a workout tracker-CodePudding

I'm designing a database for a workout tracker app. Each user should be able to track multiple workouts (routines). A workout can have multiple exercises an exercise can be used in many workouts. Each exercise will have a specific track type (weight and reps, distance and time, only reps).

My tables so far:

| User |       |
|------|-------|
| id   | name  |
| 1    | Ilka  |
| 2    | James |

| Exercise |                     |               |
|----------|---------------------|---------------|
| id       | name                | track_type_id |
| 1        | Barbell Bench Press | 1             |
| 2        | Squats              | 1             |
| 3        | Deadlifts           | 1             |
| 4        | Rowing Machine      | 3             |

| Workout |         |                 |
|---------|---------|-----------------|
| id      | user_id | name            |
| 1       | 1       | Chest & Triceps |
| 2       | 1       | Legs            |

| Workout_Exerice (Junction table)   |            |
|-----------------|------------------|------------|
| id              | exersice_id      | workout_id |
| 1               | 1                | 1          |
| 2               | 2                | 1          |
| 3               | 4                | 1          |

| Workout_Sets |                     |      |        |
|--------------|---------------------|------|--------|
| id           | workout_exersice_id | reps | weight |
| 1            | 1                   | 12   | 120    |
| 2            | 1                   | 10   | 120    |
| 3            | 1                   | 8    | 120    |
| 4            | 2                   | 10   | 220    |
| 5            | 3                   | null | null   |

| TrackType |                 |
|-----------|-----------------|
| id        | name            |
| 1         | Weight and Reps |
| 2         | Reps Only       |
| 3         | Distance Time   |

My issue is how to incorporate the TrackType table for each workout set, my first option was to create columns in the Workout_Sets table for each tracking type (weight and reps, distance and time, only reps) but that means for many rows I will have many nulls. Another option I thought was to use an EAV type table but I'm not sure. Also do you think my design is efficient (Over-normalization)?

CodePudding user response：

I would say that the most efficient way is to have nulls in your table. The alternative would require you to split many of the category's into separate tables. Also a recommendation is that you start factoring a User ID table into your database

CodePudding user response：

Your description states that “Each exercise will have a specific track type” suggesting a one-to-one relationship between Exercise and TrackType, and that the relationship is unchanging. As such, the exercise table should have a TrackType column.

I suspect, however, that your problem description may be lacking specificity, making it difficult to give you sound advice. For instance, if the TrackType can vary for any given exercise, your TrackType column may belong on the Workout_Sets table. If the relationship between TrackType and Exercise/Workout_Sets is many-to-many, then you will need another junction table.

Your question regarding “over-normalization” depends upon many factors that are specific to your solution. In general, I would say no - the degree of normalization appears to be appropriate.