SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Scoring system database design for gym

    Looking for some help on how I might design a database for scoring.

    The database is to hold exercises. Each exercise is recorded in a different way - eg. running is stored as distance and time, weights would be stored as weight and repetitions.

    I have the tables as shown below but I've just got the scores (distance, time, weight, reps) stored in 4 available columns in the logs table. I know there must be a better way to do this. Anyone have any ideas?

    exercises
    ----------------------
    id
    title
    description
    exercise_type

    exercise_types
    ----------------------
    id
    title

    logs
    ----------------------
    id
    time
    exercise
    distance
    time
    weight
    reps

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aoliver View Post
    I know there must be a better way to do this.
    not without a lot of hassle

    your design is good

    your logs table has two "time" columns, but other than that, it will work just fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops! Well spotted.

    What I feel is wrong with the design is that without any log created there is no way of knowing which type of measurement is required for a given exercise. So ideally I would have a way of knowing that when a log is to be created for an exercise the chosen exercise requires distance and time to be set but does not require weight or reps to be set.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aoliver View Post
    So ideally I would have a way of knowing that when a log is to be created for an exercise the chosen exercise requires distance and time to be set but does not require weight or reps to be set.
    two comments about this

    first, if you try to put this knowledge into the database, you will end up with a lengthy discussion thread that examines all sorts of arcane and complex design strategies, with type tables and so forth, in a manner very similar to this thread from today -- http://www.sitepoint.com/forums/show...dexes-in-MySQL

    but second, and more importantly, your front end app is probably going to have to have some of these smarts built into it, in order to perform consistency edits on the form fields that the users will be entering their exercise data into

    in my opinion you can proceed with the current design, and just decide in your front end logic which log type you're creating
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    I'd go for separate log tables for each type of exercise. The log datatypes are many and fundamentally different, and so you can't pull any type conversion strategy.



    The database is to hold exercises.
    So create the EXERCISES table.



    Each exercise is recorded in a different way - eg. running is stored as distance and time, weights would be stored as weight and repetitions.
    So create the RUNNING_LOGS and WEIGHTS_LOGS tables.



    So far, there is no connection. We create a lookup table: WORKOUTS.



    For each new exercise session:
    - get the exercise id from EXERCISES
    - create a new workout in the WORKOUTS table for it, with proper id data retrieved above
    - create a new log in the proper log table: RUNNING or WEIGHTS, with the workout foreign key from WORKOUTS tables, that links back to EXERCISES table

    http://i1054.photobucket.com/albums/...exercisess.png

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    I try to stay away from splitting tables if it can be done in one. I agree with r937, and your original design. It will allow for more ease of reporting if you had any need to build counts of types of exercises / frequencies, etc.


Tags for this Thread

Bookmarks

Posting Permissions

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