If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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

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

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

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.

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

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.

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.