Scoring system database design for gym

[aoliver]

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

[r937]

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

[aoliver]

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.

[r937]

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

[itmitică]

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

[K. Wolfe]

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.