I'm struggling to find how to correctly design a database in order to store and retrieve data for my software. Basically, I'm designing an application for a gym (C++, MySQL with PhpMyAdmin):
- each user (unique ID, name, surname) can have one or more training routines
- a training routine is divided in training schedules (from A to E)
- each training schedule contains a series of exercises (name, set, reps) divided in muscolar groups (i.e. CHEST, SHOULDER, BICEPS and so on).
The training routines can be created or retrieved from the database by looking if they are associated to a specific user; the combination of exercies is not unique and can be shared from one or more users.
For example:
-- Start of training routine #1 -- EXERCISES SET REP Training schedule A - CHEST Wide chest hammer 4 10 Incline press hammer 3 15 Pek Fly 4 10 - BICEPS Scott Machine 4 10 Curls 4 12 - LEGS Calf press 4 16 Training schedule B - SHOULDER Should press hammer 3 10 Reverse pek fly 3 10 Push Press 3 10 - QUADRICEPS Leg Curls 4 10 Leg Press 5 12 Training schedule C .. so on.. -- End of Training routine #1 -- I though to create a table for the users, then a table for each training routine with a unique ID where I will store the schedules (A,B,C..) associated with the same ID if they are included in the same training routine; then to create a muscolar group table where I store the exercises. But I do not know if this is the correct solution because I still have to handle the SET and REPs.
What do you suggest? May be the training routine table is redundant because I can retrieve the same information by looking at all the schedules which report the same ID.
Should I use a single table to store the whole training routine or it would be better to use multiple tables?
