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.
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):
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).
exerciseParameters
.exerciseParameters
row containing a NULL value.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.
So names (and values) are stored only once, making the repetitions cheaper. For example:
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).
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).