Design issue for a complex many-to-many database.

[only1abhi]

Hello All,

Back after many many years. Starting out at access again so I am struggling on how to design the tables & relationships for a system I have volunteered to help out with. Your feedback would be really valuable.

Task:
To design a workout system for personal trainer.

Problem:
Can't seem to get my head around how many (and which) tables I need to create at the start.

Requirement:
1. Each trainee to have a seperate training file (create a tblTrainee. fine.)
2. Each trainee could do a 6 or 8 week cycle of either: Weight Training, Fitness or Yoga.
3. Each training cycle will have a workout every week. Each workout will consist of various exercises like: Squats, Bench Press, etc (if doing Weight Training).
4. Each exercise will have attributes (like WeightLevel, NoOfReps, Sets, RestPeriod), etc. But these attributes will be different for each cycle type, e.g. Yoga would not have these attributes, it would have: Sunsalutation, breathing level, etc.
5. Create Graphs to monitor the progress.

Effectively, what I am trying to achieve is this:

WEIGHT TRAINING (6 weeks)
WEEK 1

DAVID | WEIGHT | REPS | SETS | REST PERIOD


------------------------------------------------------
SQUATS | 40kg | 10 | 3 | 90 secs
BENCH PRESS | 50kg | 12 | 4 | 60 secs

This would then be replicated for WEEK 2, 3, 4, 5, 6.


THE PROBLEM AGAIN:
What tables do I create? What do I create a many-to-many relationship with?

MY ATTEMPT SO FAR:
tblTrainee
tblProgramme <--- Either Yoga, Weight Training, Fitness, etc.
tblWorkout <---- Do I need a table for a weekly workout? or would this just be a report? I'm confused.
tbl Exercise <-- Squats, Bench Press, etc
tblAttributes <--- Do I need a separate table for attributes such as Weight, Reps, Sets, Rest Period, etc or could these be attributes in tblExercise?

Thank you all.

Warm regards,
Abhi

[only1abhi]

17 views, but no response.

Thank you.