Skip to content
Advertisement

Make a SQL insert statement inside another [closed]

I’m creating a a workout app for a local gym, but I’m having a difficult time figuring out how to populate the workouts and exercises tables.

Right now, I have a ‘Add Workout’ button that displays a modal. and once the modal appears, you may begin adding exercises with additional weight and reps columns. But I don’t understand how to properly insert/update these tables if I’m creating the workout/adding exercises all inside one form essentially.

My gut says the initial ‘Add Workout’ button performs an insert query and returns the id of the workout, so the exercises that are added can reference this workout, but I’m not 100% sure. Can anyone confirm this or provide some insight into how should be configured? Any help is greatly appreciated.

Advertisement

Answer

Personally, it seems like it’s open to a lot of duplication to have Exercises created under a Workout when logistically, Exercises exist and sometimes are part of a Workout, and sometimes not. This would free up your Exercises to exist within multiple Workouts without recreating them.

I think stepping back for a moment to design this with paper and pen would be a good start. Write out the logic you think should happen when you Add Exercise, or Add Workout; how do you store them? How do you connect them?

  • A table of Exercises, with whatever goes under them.
  • A table of Workouts, which then have a list or multiple rows of exerciseID.

Then when you go to Add Workout, you create a new Workout and give it a name; hit Save; grab that workoutID, and ask the user “Want to add some Exercises?”, which then allows the user to select the Exercises & # of reps or weight or time, etc… Based on the DB and UI, you may be able to retrieve the last created ID automatically with something like SCOPE_IDENTITY(), or you could have a trigger that returns the newest ID with that WorkoutName back to the calling statement; maybe even add a check against the CreatedBy username if concurrency is a concern.

Having a UI is nice, but from a design perspective, you really need to have a plan of attack on the data layout, logic flow, and all dependent connections before you create a UI to lay on top of it.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement