应该将这个数据库表规范化吗？ [英] should this database table be normalized?
问题描述
我已经接管了一个存储健身信息的数据库，我们正在就某个表格进行辩论，以及是否应该保持为一张桌子或分成三张表。
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
所以如果我在一天做了2套3个不同的练习，那么那天我会有6个记录。例如：
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
所以问题是，考虑到有一些冗余数据（date，personid， exercise_id）在多个记录中，应该归一化为三个表
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
- 日期
- person_id
WorkoutSummary:
- id
- date
- person_id
WorkoutExercise ：
- id
- workout_id（外键到WorkoutSummary）
- exercise_id
WorkoutExercise:
- id
- workout_id (foreign key into WorkoutSummary)
- exercise_id
WorkoutSets ：
- id
- workout_exercise_id（外键进入WorkoutExercise）
- reps
- weight
WorkoutSets:
- id
- workout_exercise_id (foreign key into WorkoutExercise)
- reps
- weight
我猜测这个重构是因为这个重构之后的查询会比较慢，因为现在我们需要连接3个表来执行以前没有连接的相同的查询。重构的好处允许将来在训练总结级别或练习级别添加新的领域，并增加更多的重复。
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?
推荐答案
不要以为在规范化后查询会更慢。如果表正确索引，则连接在少数表上非常便宜。
Don't assume that queries will be slower after normalizing. Joins on a small number of tables are very cheap if the tables are indexed properly.
另一方面，对非归一化表的查询可以很容易地得到很多比较慢。例如，在您的原始模式中，只需尝试查询完成锻炼的不同日期比正常版本更贵。
On the other hand, queries on a non-normalized table can easily end up being much slower. For example, in your original schema, simply trying to query the distinct dates on which a workout was done is far more expensive than it would be with the normalized version.
绝对此时规范化。如果以后遇到性能问题，那么您可以开始选择性地将另外中的数据的某些部分反规范化到已经规范化的模式。但是很可能你不会用一个小数据库来达到这个目的。
Definitely normalize it at this point. If you run into performance issues later on, then you can start selectively denormalizing certain parts of the data in addition to the already-normalized schema. But in all likelihood you will never reach that point with a small database.
这篇关于应该将这个数据库表规范化吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
