RDBMS 数据库中的多对多关系
在像 mySQL 这样的 RDBMS 数据库中处理多对多关系的最佳方法是什么?
已尝试使用数据透视表来跟踪关系,但它会导致以下任一情况:
标准化被抛在后面
列为空或为空
您采用了什么方法来支持多对多关系?
What is the best way of handling many-to-many relations in a RDBMS database like mySQL?
Have tried using a pivot table to keep track of the relationships, but it leads to either one of the following:
Normalization gets left behind
Columns that is empty or null
What approach have you taken in order to support many-to-many relationships?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在专门针对该关系的表(有时称为联结表)中跟踪多对多关系。该表将关系建模为两个指向相反方向的一对多关系。
然后,您可以使用联结表通过外键通过它连接其他表。
Keep track of a many-to-many relationship in a table specifically for that relationship (sometimes called a junction table). This table models the relationship as two one-to-many relationships pointing in opposite directions.
You then use the junction table to join other tables through it via the foreign keys.
我会使用数据透视表,但我不知道你的问题来自哪里。使用一个简单的学生/班级示例:
或者,正如其他人在回答您的学生/老师/课程问题时提到的那样(其中将有一个附加表来存储课程中人员的类型):
学生表包含学生信息,课程表存储课程信息...而数据透视表仅包含相关学生和课程的 ID。这不应该导致任何空/空列或任何内容。
I would use a pivot table, but I don't see where your issues are coming from. Using a simple student/class example:
Or, as somebody else mentioned in response to your Student/Teacher/Course question (which would have an additional table to store the type of person in the course):
The Student table contains student information, the Course table stores course information...and the pivot table simply contains the Ids of the relevant students and courses. That shouldn't lead to any null/empty columns or anything.
除了贾斯汀的回答之外:如果巧妙地使用外键约束,您可以控制数据更新或删除时发生的情况。这样,您就可以确保最终不会得到非规范化的数据。
In addition to Justin's answer: if you make clever use of Foreign Key constraints, you can control what happens when data gets updated or deleted. That way, you can make sure that you do not end up with de-normalized data.