3

I have a large list of exercises that I am currently storing in a SQL table with a unique index value (int) in one column and the exercise name in the other column. I am trying to determine an efficient way of storing sets of exercises as workouts.

As of now, I have two ideas in mind but am not sure what the most efficient (both from a storage and a speed standpoint). These are the ideas I am considering:

Solution 1 Create a new table that has a column for every exercise that would have a 1 or 0 depending on whether or not it was within a workout. The workout would be an index column, with a unique int id.

Solution 2 Create a new table that has a the index column representing the unique workouts. A second column that has an array of the numbers that would correspond to the set of workouts. What I prefer about this option is that it would allow me to preserve the order of exercises within the workout.

While I currently only have a list of ~800 exercises and ~400 workouts making (1) an array of size 800 x 400 whereas (2) would be 400 x 2. However, with the option to create new workout (lets just say that the exercise list will be fixed) table (1) would grow significantly faster.

Does anyone have suggestions as to how to minimize size and maintain speed in such a context?

wroller
  • 53
  • 5
  • 2
    phpMyAdmin is not a database, it's only a GUI for MySQL. MySQL is your SQL database. – deceze Jan 20 '14 at 16:22
  • Table for `set` with `id` and `set name`; table for `setexercises` with `id`, `setId` and `exerciseId` (and possibly an `orderId`) – Mark Baker Jan 20 '14 at 16:23

1 Answers1

0

I like #2 but you could also, if you wanted a more robust solution, have 3 tables:

  1. exercises
  2. workouts
  3. workout_exercises

In addition, I generally would not store the exercises per work out as an array. It would be difficult to use sql to search for a specific exercise within that field, eg:

What workouts contain exercise XYZ?

Your table sizes are actually tiny. When you have 10,000 records or 100,000 records you can start to think about size issues.

ssaltman
  • 3,623
  • 1
  • 18
  • 21