should this database table be normalized?
- by oo
i have taken over a database that stores fitness information and we were having a debate about a certain table and whether it should stay as one table or get broken up into three tables.
Today, there is one table called: workouts that has the following fields
id, exercise_id, reps, weight, date, person_id
So if i did 2 sets of 3 different exercises on one day, i would have 6 records in that table for that day. for example:
id, exercise_id, reps, weight, date, person_id
1, 1, 10, 100, 1/1/2010, 10
2, 1, 10, 100, 1/1/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, given that there is some redundant data (date, personid, exercise_id) in multiple records, should this be normalized to three tables
WorkoutSummary:
- id
- date
- person_id
WorkoutExercise:
- id
- workout_id (foreign key into WorkoutSummary)
- exercise_id
WorkoutSets:
- id
- workout_exercise_id (foreign key into WorkoutExercise)
- reps
- weight
I would guess the downside is that the queries would be slower after this refactoring as now we would need to join 3 tables to do the same query that had no joins before. The benefit of the refactoring allows up in the future to add new fields at the workout summary level or the exercise level with out adding in more duplication.
any feedback on this debate?
