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?

It is a matter of interpretation of your application if an exercise is cardio or strength, but not an explicit datum. Therefore I'd rather have only one table for all attributes of the cardio and strength tables and compute from the actual data if a row is a cardio or a strength exercise or something in between.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.