MySQL - 从第一范式转向第二范式和第三范式
这个问题直接与之前的主题“MySQL - 从平面表移动到第一范式”(http:// bit.ly/9pvS0Y) - 当我现在问一个关于转向第二和第三范式的问题时，我认为最好开始一个新主题。
下面是我的第一个范式模式，我确信它对于我的目的来说足够可靠，但如果我错了，请纠正我。
我想知道如何将其转移到第二种和第三种形式，任何关于我的表如何受到 2NF 和 3NF 规则影响的指示都会非常有用，谢谢。
关系
-活动和位置关系 = 一对多 - 一项活动可以有一个位置，一个位置可以有许多活动（LocationID 作为活动中的 FK）
-活动和周关系 = 一对多 - 一项活动可以有一周，一周可以有多项活动（WeekID 作为活动中的 FK）
-用户和活动 = 多对多 - 一个用户可以有多个活动，一个活动可以有多个用户
User Table - UserID PK
+------------+-----------+
| UserID | Username |
+------------+-----------+
| | |
+------------+-----------+
Activity Table - ActivityID PK / WeekID FK / LocationID FK
+------------+-----------+------------+-----------+------------+-------------+-----------+
| ActivityID | UserID | WeekID | Day | Minutes | LocationID | Miles |
+------------+-----------+------------+-----------+------------+-------------+-----------+
| | | | | | | |
+------------+-----------+------------+-----------+------------+-------------+-----------+
Location Table - LocationID PK
+------------+---------------+
| LocationID | Location_Name |
+------------+---------------+
| | |
+------------+---------------+
Weeks Table - Week ID PK
+------------+-----------+
|WeekID | Week_No |
+------------+-----------+
| | |
+------------+-----------+
User_Activity Table
+------------+---------------+
| UserID | ActivityID |
+------------+---------------+
| | |
+------------+---------------+
This question directly relates to a previous topic "MySQL - move from flat table to first normal form" (http://bit.ly/9pvS0Y) - and as I am now asking a question about moving to second and third normal forms, I figured it best to start a new topic.
Below is my first normal form schema which I am pretty sure is solid enough for my purposes, but please correct me if I am wrong.
I would like to know how to move this through to the second and third forms, any pointers as to how my tables would be affected by 2NF and 3NF rules would be really useful, thanks.
Relationships
-Activity and Location relationship = one to many - one activity can have one location, a location can have many activities (LocationID as FK in Activity)
-Activity and Week relationship = one to many - one activity can have one week, a week can have many activities (WeekID as FK in Activity)
-User and Activity = many to many - one user can have many activities, one activity can have many users
User Table - UserID PK
+------------+-----------+
| UserID | Username |
+------------+-----------+
| | |
+------------+-----------+
Activity Table - ActivityID PK / WeekID FK / LocationID FK
+------------+-----------+------------+-----------+------------+-------------+-----------+
| ActivityID | UserID | WeekID | Day | Minutes | LocationID | Miles |
+------------+-----------+------------+-----------+------------+-------------+-----------+
| | | | | | | |
+------------+-----------+------------+-----------+------------+-------------+-----------+
Location Table - LocationID PK
+------------+---------------+
| LocationID | Location_Name |
+------------+---------------+
| | |
+------------+---------------+
Weeks Table - Week ID PK
+------------+-----------+
|WeekID | Week_No |
+------------+-----------+
| | |
+------------+-----------+
User_Activity Table
+------------+---------------+
| UserID | ActivityID |
+------------+---------------+
| | |
+------------+---------------+
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不确定表 User_Activity 的用途，因为您已经在活动表中定义了这两列。否则——这个设计已经达到了第三范式。
Not sure of the purpose you have for table User_Activity, since you already have both columns defined in you Activity Table. Otherwise--this design already goes to 3rd normal form.
除非这是一项学术练习，否则我建议您不要“通过”正常形式。该过程的正式名称是分解标准化。然而，在大多数情况下它不是很实用，并且通常完全没有必要。
在实践中，从假设已经标准化的模式开始（通常针对 5NF 或 BCNF 而不是 3NF）然后验证它更有意义。设计已在所需 NF 中的模式的过程称为“综合归一化”，它比分解方法更接近大多数从业者的工作方式。有几种精确的技术可以实现综合归一化，但迄今为止最常见的方法只是良好分析、经验和常识的结合。
Unless this is an academic exercise I suggest you don't "move through" the normal forms. The formal name for that process is Normalization by Decomposition. It's not very practical in most cases however and is generally completely unnecessary.
In practice it makes more sense to start with a schema that is already hypothetically normalized (typically aim for 5NF or BCNF rather than 3NF) and then validate it. The process of designing a schema already in the desired NF is called Normalization by Synthesis and it is closer to the way most practitioners work than the decomposition method. There are several precise techiques for achieving Normalization by Sythesis but by far the commonest method is just a combination of good analysis, experience and common sense.
正如 russjudge 所说，我看不到 User_Activity 表的用途。
在规范化中，通常最好的做法是尽可能使用自然键。因此，我也看不出 Weeks 表的意义 - 为什么不直接使用 Week_No？ （这里可能的例外是，如果您想设置具有不同天数的“周” - 在这种情况下，您可能还想在周表中包含开始日期和结束日期。不过，就目前情况而言，我没有明白这一点。）
事实上，我会更进一步，用单个日期字段替换“活动”表中的周和日 - 这实际上应该是 1NF（删除派生数据）的一部分。大多数版本的 SQL（包括 MySQL）都可以根据需要轻松将日期字段转换为天或周。
我还想删除 ActivityID 字段，而是使用 UserID、Date 和 LocationID 的组合作为 Activity 表上的复合主键。但是，可能需要记录同一用户、日期和位置的多行 - 在这种情况下，ActivityID 字段应保留为表的主键。
As russjudge says, I can't see the purpose of the User_Activity table.
In normalisation, it's generally best practice to use natural keys wherever possible. As such, I can't see the point of the Weeks table, either - why not just use Week_No? (A possible exception here might be if you wanted to set up "weeks" with differing numbers of days in them - in which case, you might also want to include start and end dates on the Weeks table. As it stands, though, I don't see the point.)
In fact, I would go further, and replace week and day on the Activity table with a single date field - this should actually be part of 1NF (removing derived data). Most versions of SQL (including MySQL) can readily convert a date field into a day or week, as required.
I would also be tempted to remove the ActivityID field, and instead use a combination of the UserID, Date and LocationID as a compound primary key on the Activity table. However, there may be a requirement to record multiple rows for the same user, date and location - in which case, the ActivityID field should remain as the table's primary key.
如果“Week_no”表示“周数”，则将“Week_no”移至“Activity”，并删除表“Weeks”。
If "Week_no" means "week number", move "Week_no" into "Activity", and drop the table "Weeks".