在关系数据库中对相同实体之间的多对多关系进行建模 [英] Modeling multiple many to many relationships between the same entities in a relational database
问题描述
当两个表具有多对多关系时，或者当一个表与自身具有多对多关系时，我们可以使用联结表对其进行建模。
When two tables have a many-to-many relationship or when a table has a many-to-many relationship with itself we can model that with a junction table.
但是关系的类型可以超出原始类型，例如：
However the type of the relationship can expend beyond the original type, for example:
要求：我们有用户。每个用户可以有0个或更多其他用户作为朋友
Requirements: we have users. each user can have 0 or more other users as friends
解决方案：一个用户表和一个称为 user_user的联结表。
Solution: a users table and a junction table called "user_user".
然后，我们发现了一个新要求：某些友谊是浪漫的，换句话说，两个用户可以用不同于友谊的方式建立联系。
Then we discover a new requirement: some friendships are romantic or in other words two users can be connected in a different way than friendship.
-
解决方案a：在联结表中添加一列，其中包含友谊的类型（朋友|伙伴|前朋友），等等。
Solution a: add a column to the junction table that contains the type of friendship (friend | partner | ex-friend) and so on.
解决方案b：将 user_user表重命名为 friendships（从这种方法开始，这将是一个更好的名称），并创建一个新的表，称为romances，
Solution b: rename the 'user_user' table to 'friendships' (that would have been a better name to start with with this approach) and create a new table called romances, also connecting a user to a user.
然后，我们发现了另一个新要求：用户可以欠其他用户钱。同样，我们可以搭载在同一联结表上，也可以创建一个称为债务的新表。这次我的直觉是100％有一个单独的表。
Then we discover yet another new requirement: users can owe other users money. Again we can piggyback on the same junction table or create a new one called 'debts'. This time my intuition is 100% to have a separate table.
每次我们发现一种新型的关系或已知关系的子类型时，我们都可以添加一个
Every time we discover a new type of relationship or a sub-type of a know relationship we can add a column to the one junction table or create a new junction table.
我的问题是，决定何时必须创建新表的好规则是什么？
My question is, what is a good rule to decide when a new table must be created?
当每个有序对中必须有多于一行时是吗？例如，如果从不删除过去的关系，那么如果两个用户在过去两年中成为朋友，失去联系，然后重新成为朋友，我们希望同时保留旧行以及开始和结束日期，但要保留两行使其他列的信息重复（旧行显示用户欠钱，新行不欠）。
Is it when we must have more then a single row per ordered pair? For example if past relationships are never deleted so if two users have been friends in the past for two years, lost contact, and then re-befriended we want to have the old row as well with a start and end date, but having two rows makes the other column information duplicated (old row shows a user owes money, the new one doesn't).
当多余的列在逻辑上不是定义时
Is it when the extra column is not logically a definition of a single type of relationship?
确定：友谊的开始与友谊1到1直接相关。
OK:start of friendship date is related directly to the friendship 1 to 1
不行：关于一个用户拥有另一个用户的货币数量的关系列可以是1对1，但从逻辑上讲不是友谊的描述。
Not OK: column for how much money one user owns another user can be 1 to 1 for the relationship but logically is not a description of the friendship.
如果我们知道预先，两个表将具有很多对多关系，这是计划多个联结表还是使单个联结表更灵活（即使连接的类型不是任意的）的原因吗？ ？
If we know in advance that two tables are going to have a lot of many to many relationship, is it a reason to plan for many junctions tables or to make a single junction table that is more flexible (even if the type of the connection is not arbitrary)?
由您决定。您可以将友谊定义为与浪漫参与不同，因此需要两个联结表。或者，您可以将浪漫参与定义为友谊的特例，因此需要一个表。我倾向于后者，因为它更简单，更容易混淆。 （如果将两个人同时列为朋友和情人该怎么办？）
It's up to you. You can define a "friendship" as distinct from "romantically involved" and so need two junction tables. Or you can define "romantically involved" as a special case of "friendship" and so need one table. I would lean toward the latter as simpler and less confusing. (What if the same two people are listed as friends and also as lovers?)
但是，欠钱却有所不同。如果A是与B的朋友，那么B是与A的朋友。但是，如果A欠B的钱，那说B欠A的钱是错误的。因此，您不仅在定义另一种关系，而且还定义了另一种关系。哪个字段包含对A的引用，哪个字段包含对B的引用，变得很重要。为此，最好有一个单独的联结表。
However, "owes money to" is different. If A is friends with B, then B is friends with A. But if A owes money to B, it is false to say that B owes money to A. So not only are you defining a different kind of relationship, but the positioning of which field contains the reference to A and which contains the reference to B becomes significant. For this then, it would be best to have a separate junction table.
这篇关于在关系数据库中对相同实体之间的多对多关系进行建模的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
