Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2012
    Posts
    13

    Database Design Help

    Hi all,
    I'm pretty new to databases, although I have a good amount of experience with Excel and spreadsheets.
    I'm working to create a exercise log database. It will mostly be for myself, though I would like it to be expandable. I have already started creating some tables for it, but I'm having trouble with the relationships as well as if there are any other tables.

    Here is what I have and what I am thinking:

    tblUserInfo (UserID, FirstName, LastName, Weight)
    tblWorkout (WorkoutID, WorkoutName, Date)
    tblSet (SetID, SetNumber, WorkoutID)
    tblProgram (ProgramID, ProgramName, UserID)
    tblExerciseType (ExerciseTypeID, ExerciseType)
    tblExercise (ExerciseID, ExerciseName, ExerciseTypeID)

    Here are the things that I would like to track:
    1. User's weight
    2. exercise's performed for a given workout, including sets, reps and weight
    3. an exercise's average weight for a workout
    4. total volume for a workout (sets x reps x weight for all exercises)


    In my mind, I was coming up with the following relationships:
    • Many different users can use many different programs
    • Many different users can be different weights
    • A program can have many different workouts, and different workouts can belong to different programs
    • workouts can be performed on different dates
    • an exercise can have more than one exercise type
    • an exercise can have many sets
    • each set can have many reps
    • each rep can only have one rep


    I know it's pretty basic thinking, but I figured it is easier to keep it simple.

    I know that once I have the information (user weight, sets, reps, weights, etc.), I can manipulate it to figure out other info (averages, total volume, PR's, etc.).

    Any and all suggestions and help is welcome.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What are "programs" in this context? You don't seem to use that entity anywhere in your schema.

    The user's weight will almost certainly change over time. This schema doesn't allow for that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    13
    Quote Originally Posted by Pat Phelan View Post
    What are "programs" in this context? You don't seem to use that entity anywhere in your schema.

    The user's weight will almost certainly change over time. This schema doesn't allow for that.

    -PatP
    A program can contain multiple workouts.

    I understand that weight can change. I'm just not sure how to set that up in the schema.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you are wanting to track the users weight, then you'd need to push that down from the user table into a table of its own... say weightevents recording the user a date/time the weighing took place and the weight.

    unless you record detals of each type of expercide then your user is going to get very very bored indeed. I see no association of exercise

    dpending on on many tables you need for your assessment I'd expect there to be some sort of entity recording say
    excrcise 1, rowing machine, program 5, 15 minuites
    exercise 2, pushps, 5 repetitions of 10

    then optionally
    achieved on this date dd/mm/yyyy hh:mm
    excrcise 1, rowing machine, program 5, 10 minuites
    exercise 2, pushps, 5 repetitions of 10
    achieved on this date dd+x/mm/yyyy hh:mm
    excrcise 1, rowing machine, program 5, 15 minuites
    exercise 2, pushps, 5 repetitions of 10
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    13
    So I wanted to take a stab at it myself first and this is what I came up with. Please let me know what you think.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    rather than taking a stab, its best for you to work through your model and try to makje certain it meets the 'business' requirement

    start with a clear list of what the application must do. bear in mind its coursework so you don't neccessarily have to do every thing there's some stuff you could identify but explain away as not implemented in the first versions...

    a lot of tjhe requirements come from knowing what users want to do, or what you decide you want your system to do.

    so when you used the words
    Here are the things that I would like to track:
    User's weight
    exercise's performed for a given workout, including sets, reps and weight
    an exercise's average weight for a workout
    total volume for a workout (sets x reps x weight for all exercises)
    make certain that is the full scope of the project. ie it can truly represents an exercise program. if you are not certain what constitutes such a program why not go and talk to someone who does know the types and forms of exercise.

    lets stick within the gym with lots of equipment
    not all machines work, or are used, the same way
    some use weights to provide resistance, some use settings, some might have mulitples settings
    some are time based, some are cycle based
    some exercise don't use machines in any way shape or form
    some exercises use other forms of equipment (although arguably you could fold machines into equipment and be done with that problem. you may want to look at using the sub / supertype model to cater for that

    some are setting and time based (eg you might use a specific machine at a specific setting for a specific time, then use the same machine at different settings for another time and so on, let alone the concept of warm up & cool downs

    its your assignement. it really is up to you to do the work. if you feel the need to get your work validated talk to your lecturer and or your colleagues. personally I find talking through the model with someone with someone knowledgeable always helps.. especially if the can ask pertinent questions. also consider protoyping the model and putting in some some valid data and see if it can support what you need to achieve. that doesn't neccessarily means create a working scheme it may mean collecting sample data of various types of data and see if your schema can support that. ferinstance go down to the gym and observe which types of exercises people do and what metrics are relevant for each exercise type.

    look for common patterns of exercise, rather than look in detail at each and every device look to identify common types of device.


    asking other people to validate your model isn't doing you any favours in terms of completing this assignment but more importantly in terms of you learning from the process or just as importantly proving tot eh institution that their teaching techniques and material are appropriate
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2012
    Posts
    13
    Hi healdem,
    Thank for the reply, but you made a couple of wrong assumptions. If you don't' want to help, that's one thing. Just say so.
    First of all, I am not a student. This isn't coursework or an assignment. I don't think I ever said I was a student or that this was an assignment. This is a personal project that I am working on. For the most part, I will be the only one using this database, but if it's any good, I will share it with others.
    I have no instructors, colleagues or professors. I have no textbooks to work out of. I've read one book on databases and SQL, "Head First SQL". That is the extent of my studying.
    I don't need to go to the gym to observe. I go to the gym every morning and actually work out. I have my own data. I have 6 months worth of logs that I've written down. But I would like to put them into a database so that I can learn more about myself and my tendencies. I can see if I am making progress.
    Don't assume that just because I am asking a question on this forum that I am a student.
    I could put all of this information into a spreadsheet and get the same results, but I feel that a spreadsheet would be cumbersome.
    I have no formal training in any type of IT. Everything I have learned has been through reading, trial and error, and asking questions on forums like this one.
    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    sorry abut that matey
    this time of year we get inundated by students trying to pass off the assignments as genuine problems. it tends to make one hyper suspicious when you get problems posted such as yours.

    I'll have a re think
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2012
    Posts
    13
    Quote Originally Posted by healdem View Post
    sorry abut that matey
    this time of year we get inundated by students trying to pass off the assignments as genuine problems. it tends to make one hyper suspicious when you get problems posted such as yours.

    I'll have a re think
    I though I responded to this yesterday. Anyway...

    No problem. I understand how that could be very annoying.
    I would appreciate any and all help.
    Thanks

  10. #10
    Join Date
    Feb 2012
    Posts
    13
    So I re-worked what I had. I don't know if it's right or not, but in my mind it is. I am also including one of the exercise logs I use.

    Here are the things to notice about this:

    • Program Name: NRoL Abs
    • Workout Name: Phase 1, Workout A, #1
    • Date: self explanatory
    • I didn't start logging things like calories, start time, or duration at this point.
    • Exercises:
      • Front Plank: It has sets, but instead of reps, it has time duration. I need to add that to the schema.
      • Side Plank: I should probably figure a way to add left and right sides as well. Many exercises are unilateral and use both sides of the body separately.
      • Romanian Deadlift: This is a good example of a pretty basic exercise. It has a name, sets, reps and weights. As you can see, the weights go up with each set.


    The other thing I was thinking about was that there are instances where the weight will go up (usually down), within a set.

    The last piece that I couldn't figure out was how to store the derived data: things like total volume for an exercies (set x reps x weight) for a given day, total volume for the workout, and then other things like max reps and max weight or even a combination of the two.

    As I said before. Any help is appreciated.

    Thanks

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    does the detail need to be stored / searchable
    ferinsatnce would it be enough to include that sort of detail in a description / comments section rather than record it individually
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2012
    Posts
    13
    Quote Originally Posted by healdem View Post
    does the detail need to be stored / searchable
    ferinsatnce would it be enough to include that sort of detail in a description / comments section rather than record it individually
    Which detail are you talking about? The derived data? Yes, that would need to be stored and searchable. I would like to be able to see when I have set new PR's (personal records) for exercises, for volume, for sets and reps, etc.

    A comments field would also be helpful, but that will literally just be comments made on the days workout.

  13. #13
    Join Date
    Feb 2012
    Posts
    13
    I thought I had uploaded my most recent take on this, but it doesn't seem to be there.

    I'll try it again.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •