该数据库表应该规范化吗？
我接管了一个存储健身信息的数据库，我们正在就某个表进行辩论，以及它是否应该保留为一张表或分成三个表。
今天，有一张名为：锻炼的表，其中包含以下字段
id、exercise_id、reps、weight、date、person_id
因此，如果我在 上做了 2 组 3 种不同的锻炼有一天，我会在该表中记录当天的 6 条记录。例如：
id、exercise_id、次数、体重、日期、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、2010 年 1 月 1 日、10
5、2、10、100、2010 年 1 月 1 日、10
6, 2, 10, 100, 1/1/2010, 10
那么问题是，考虑到多条记录中存在一些冗余数据（date、personid、exercise_id），是否应该将其归一化为三个表
<强>锻炼总结：
- 身份证号
- 日期
- person_id
锻炼锻炼：
- 身份证号
-锻炼_id（WorkoutSummary 中的外键）
-exercise_id
锻炼组：
- 身份证号
-workout_exercise_id（WorkoutExercise 的外键）
- 代表
- 权重
我猜缺点是重构后查询会变慢，因为现在我们需要连接 3 个表来执行之前没有连接的相同查询。重构的好处是允许将来在锻炼摘要级别或练习级别添加新字段，而无需添加更多重复。
对这次辩论有何反馈？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不要假设标准化后查询会变慢。如果表索引正确，则少量表的联接非常便宜。
另一方面，对非规范化表的查询很容易变得慢得多。例如，在您的原始架构中，简单地尝试查询完成锻炼的不同日期比使用规范化版本要昂贵得多。
此时一定要使其正常化。如果稍后遇到性能问题，那么除了已经规范化的架构之外，您还可以开始有选择地对数据的某些部分进行非规范化。但使用小型数据库很可能永远无法达到这一点。
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.
新的重构看起来不错，如果您在各个表上有适当的索引，性能不会受到太大影响。 （可以在所有外键上创建索引）
所以是，这似乎是完全正常的重构。
The new refactoring seems good, and performance will not be all that affected if you have the appropriate indexes on the various tables. (Indexes can be created on all foreign keys)
So YES, that seems like a perfectly normal refactoring.