So here's the design:
Workout_TBL(Workout_ID, Type, Notes, Date);
Exercise_TBL(Exercise_ID, Exercise_Name, Type)
Cardio_Entry_TBL(Cardio_Entry_ID, Workout_ID, Exercise_ID, Duration, Pace, Distance, Calories, Notes) - FK Exercise_ID and Workout_ID
Strength_Entry_TBL(Strength_Entry_ID, Workout_ID, Exercise_ID, Weight, Repetitions, Sets, Notes) - FK Exercise_ID and Workout_ID
A workout has many exercises associated with it of a certain type, which works, when a user selects cycling for example from the exercise table I know it's cardio (defined by type) and will place it in the appropriate table - and the correct details get filled in, i.e. duration is and weight isn't an attribute of a cardio exercise (in this case anyway).
The background to the problem here is that I don't want to pre-populate the Exercise_TBL ... I want to store them as the user enters them, so if it exists in the database then the ID is used, if it doesn't then a new exercise will be created for that workout and future use. This is so I can have a comprehensive list of exercises accounting for name variations and what not.
The actual problem is, when the exercise doesn't exist and a new one is created, I don't know what type it is without manual verification. The user could enter a weight exercise when adding exercises to a cardio workout and then the weight exercise will have cardio attributes associated with it.
No matter how many warnings I give the user, they will always have the chance to enter some dodgy data.
It's either I could employ strict validation before anything gets input to the database, like have them input exercise attributes to make sure they have selected the right exercise type or ... have a better database design ... a design idea one of you guys have that I haven't thought of?
