0

I am wondering what thoughts are on the following table structures for MySQL.

I have a relationship between exercises and exercise parameters, where a single exercise can have mutliple parameters.

For example, the exercise 'sit-ups' could have the parameters 'sets' & 'reps'.

All exercises start with a default set of parameters. For example: sets, reps, weight, hold & rest.

This list is fully customizable. Users can add parameters, remove parameters, or rename them, for each exercise in the database.

To express this relationship, I have the following one-to-many structure:

TABLE exercises
ID
Name

Table exerciseParameters
ID
exerciseID -> exercises(ID)
Name

What is concerning me, is that I am noticing that even though users have the option to rename / customize parameters, a lot of the time they dont. So my exerciseParameters table is filling up with repeat words like "Sets" & "Reps" quite a bit.

Is there a better way something like this should be organized, to avoid so much repetition? (Bearing in mind that the names of the parameters have to be user-customizable. For example "Reps" might get changed to "Hard Reps" by the user.) (Or am I making a big deal out of nothing, and this is ok as is?)

Thanks, in advance, for your help.

Improve this question

2 Answers 2

Reset to default
3

Unless you are dealing with millions of rows, I'd leave the structure as it is. It is straightforward and easy to query.

If you are dealing with millions of rows and you have measured the storage impact and deem it unacceptable, then you have couple of options (not necessarily mutually exclusive):

Don't store the defaults

If a parameter is not present in exerciseParameters simply assume it has a default value. The actual defaults can be stored in a separate table or outside the database altogether (depending on your querying needs).

  • If user changes the default parameter, store it in exerciseParameters.
  • If user deletes the default parameter, represent it as an exerciseParameters row containing a NULL value.
  • If user restores the default parameter to its original value, remove it from exerciseParameters.

This exploits the assumption that there will be many more unchanged than either edited or deleted defaults. The cost is in increased complexity (in both modification and querying) and potentially performance.

Reorganize you data model

So names (and values) are stored only once, making the repetitions cheaper. For example:

enter image description here

ParameterNameID and ParameterValueID are integers, so each repetition in exerciseParameters is much cheaper (storage-wise) than if they were strings. OTOH, you loose simplicity and potentially pay a price in querying performance (more JOINs needed).

Use a different DBMS

A one that supports clustering and leading-edge index compression (for example, Oracle's ORGANIZATION INDEX COMPRESS table can greatly diminish storage impact of repeated values).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank-you for the thoughtful response. I'm going to leave things as is for now, but this gives me a number of routes to consider down the road.
0

You could add up another table defaultExerciseParams with the default parameters and values. Whenever a user decides to override any of those - remove the param from this table and push it into the Table exerciseParameters

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.