Should this database table be normalized?

Question

I took a database that stores fitness information, and we discussed a specific table and whether it should remain a single table or split into three tables.

Today there is one table: training , which has the following fields

id, exercise_id, reps, weight, date, person_id

So, if I made 2 sets of 3 different exercises in one day, I would have 6 entries in this table for this day. eg:

id, exercise_id, reps, weight, date, person_id
1, 1, 10, 100, 1/1/2010, 10
2, 1, 10, 100, 01/01/2010, 10
3, 1, 10, 100, 1/1/2010, 10
4, 2, 10, 100, 1/1/2010, 10
5, 2, 10, 100, 1/1/2010, 10
6, 2, 10, 100, 1/1/2010, 10

So the question is that in several records there is some redundant data (date, personid, exercise_id), if this should be normalized to three tables

WorkoutSummary :
- id
- date
- person_id

WorkoutExercise :
- id
- workout_id (foreign key in WorkoutSummary)
- exercise_id

WorkoutSets :
- id
- workout_exercise_id (foreign key in WorkoutExercise)
- reps
- weight

I would suggest that the disadvantage is that after this refactoring, the queries will be slower, since now we will need to join 3 tables to make the same query that did not have joins before. The advantage of refactoring allows you to add new fields to the final training level or exercise level in the future without additional duplication.

any feedback on this debate?

Answer 1

Do not assume that after normalization queries will be slower. Joins on a small number of tables are very cheap if the tables are properly indexed.

On the other hand, queries in an irregular table may end up being much slower. For example, in your original scheme, just trying to request different dates on which the training was performed is much more expensive than in the normalized version.

Definitely normalize it at this point. If you later encounter performance issues, you can begin to selectively denormalize some pieces of data in addition to an already normalized schema. But in all likelihood, you will never reach this point with a small database.

Answer 2

The new refactoring seems good, and performance will not be affected if you have the corresponding indexes in different tables. (Indexes can be created on all foreign keys)

So, YES , that sounds like completely normal refactoring.