Funny that I've never come across this yet!
有趣的是，我从未遇到过这个！
It never occurred to me that one could have a "many to many" relationship on one table- until I started working on a system where users can "friend" each other (social networking).
我从来没有想到一个人可以在一张桌子上拥有“多对多”的关系 - 直到我开始研究一个用户可以互相“交朋友”的系统（社交网络）。
A standard lookup table, at least in the way that I'm used to using it, isn't appropriate here. Lets keep it simple:
标准查找表，至少在我习惯使用它的方式，在这里是不合适的。让我们保持简单：
User table has "id" and "name" column.
用户表具有“id”和“name”列。
User_relationship table has "uid1" and "uid2", representing users that are "friends" or "buds" or "pals" or "whatever else".
User_relationship表具有“uid1”和“uid2”，表示“朋友”或“芽”或“好友”或“其他任何”的用户。
It becomes apparent pretty quick what the problem here is- uid1 and uid2 are the same data type from the same column of the same table, meaning that unique keys become flawed.
很明显这里的问题是什么 - uid1和uid2是来自同一个表的同一列的相同数据类型，这意味着唯一键变得有缺陷。
E.g.: uid1 = 1 uid2 = 2
例如：uid1 = 1 uid2 = 2
Is the same as:
是相同的：
uid1 = 2 uid2 = 1
uid1 = 2 uid2 = 1
And therefore could return 2 records, or 0 records if the query is performed wrong.
因此，如果查询执行错误，则可以返回2条记录或0条记录。
In the spirit of designing a table well, I don't want to have to scan the entire table twice to check for existing values.
本着设计好表的精神，我不想两次扫描整个表以检查现有值。
Is there some sort of trick for handling this? This is a design question that has never occurred to me, and it irks me because I know that there's some simple trick to make it work.
处理这个有什么技巧吗？这是一个从未发生过的设计问题，它让我感到烦恼，因为我知道有一些简单的技巧可以让它发挥作用。
Before you ask, I haven't tried anything yet, because I already see that my favorite way of relating things (lookup tables) is insufficient for my needs here, and I need some help- I can't find anything on SO or Google :(
在你问之前，我还没有尝试过任何东西，因为我已经看到我最喜欢的关联方式（查找表）不足以满足我的需求，我需要一些帮助 - 我在SO或Google上找不到任何东西:(
Thanks in advance.
提前致谢。
4 个解决方案
#1
7
meaning that unique keys become flawed.
意味着唯一键变得有缺陷。
uid1 = 1 uid2 = 2Is the same as:
是相同的：
uid1 = 2 uid2 = 1
Nope, it's not.
不，不是。
On Facebook, for instance, I've a number of customers who sent requests to become "friends" that I never accepted... Since they're mere acquaintances.
例如，在Facebook上，我有许多客户发出请求成为我从未接受的“朋友”...因为他们只是熟人。
Along the same lines, I might have marked a few people as best friends, and they didn't reciprocate, or vice versa. Or perhaps I'm ignoring a few and they are not.
同样，我可能会将一些人标记为最好的朋友，但他们没有回应，反之亦然。或许我忽略了一些而他们不是。
Basically, there's a lot more information in a (uid1, uid2) tuple than mere IDs.
基本上，（uid1，uid2）元组中的信息比仅仅ID更多。
Make sure that you never need to deal with situations like these before deciding to add e.g. a uid1 < uid2 constraint on your table.
在决定添加之前，请确保您永远不需要处理这些情况。你桌子上的uid1
#2
6
If the relationship you're describing is symmetrical, as in "Bob is a friend of Joe" means "Joe is also a friend of Bob", then you can make sure in your code that the smaller of the 2 user IDs goes on the first column, and the larger one goes on the second column. This constraint pretty much ensures that the records in your lookup table will be unique. It also means that when you're performing a lookup, you usually have to search both columns.
如果您描述的关系是对称的，如“Bob是Joe的朋友”意味着“Joe也是Bob的朋友”，那么您可以在代码中确保2个用户ID中的较小者继续第一列，较大的一列在第二列。这种约束几乎可以确保查找表中的记录是唯一的。这也意味着当您执行查找时，通常必须搜索两列。
For example, if you were trying to get all of Bob's friends, you would have to query for records that have Bob's ID in either column. This leads to a bit more code and possibly an impact on performance.
例如，如果您尝试获取Bob的所有朋友，则必须查询在任一列中具有Bob ID的记录。这会导致更多代码，并可能对性能产生影响。
If the relationship can be asymmetrical, as in "Bob is a friend of Joe" does not necessarily mean "Joe is also a friend of Bob", then you need 2 entries for every pair of users: Bob - Joe and Joe - Bob. This means that your lookup table will contain twice as many entries and also that your site is very stalker-friendly :D Of course, you can still choose to apply this system even though your relationship is symmetrical.
如果关系可以是不对称的，如“Bob是Joe的朋友”并不一定意味着“Joe也是Bob的朋友”，那么每对用户需要2个条目：Bob - Joe和Joe - Bob。这意味着您的查找表将包含两倍的条目，并且您的站点对跟踪器非常友好：D当然，即使您的关系是对称的，您仍然可以选择应用此系统。
Using this method, if you want to get all of Bob's friends you just have to select the records with Bob's ID in the first column. It is possible that this might mean faster lookups and less code for you to write, but again, it means you're taking up more room in your database.
使用这种方法，如果你想得到Bob的所有朋友，你只需要在第一列中选择带有Bob ID的记录。这可能意味着更快的查找速度和更少的代码供您编写，但同样，这意味着您在数据库中占用更多空间。
#3
2
This is not that uncommon.
这并不罕见。
Typically what is done is that there is a table, as in most many-many relationships that consists of two columns, each being an id, of the two tables, that make up the primary key.
通常所做的是有一个表，就像在大多数关系中一样，它由两个列组成，每个列是构成主键的两个表的id。
As you stated userId1 and userId2. Attributes, if necessary, can be added to the relationship (such as classification of friendship).
正如您所说的userId1和userId2。如有必要，可以将属性添加到关系中（例如友谊分类）。
When user 1 befriends user 2, there are typically two inserts, (1,2) and (2,1).
当用户1与用户2成为朋友时，通常有两个插入，（1,2）和（2,1）。
Same sort of thing with a defriending, there needs to be two deletes.
与defriending相同的事情，需要有两个删除。
You could end up with a user having himself as a friend, and that might be critical to the actual workings of the system. If a user can only view photos of his friends, then if he is not a friend to himself, some systems might not allow him to see his own photos.
您最终可能会将自己视为朋友，这可能对系统的实际运行至关重要。如果用户只能查看他朋友的照片，那么如果他不是自己的朋友，某些系统可能不允许他看到他自己的照片。
It is very dependent upon how the application is written on top of the database.
它非常依赖于如何在数据库上编写应用程序。
#4
1
I agree with what others have said, it's isn't a bad idea to have 2 inserts for a relationship (1:2 and 2:1). This actually helps to expand some of the features which are commonly found in modern social networks. Some cases of practical use that I can think of is description of relationship or other attributes. While individuals remain friends they maintain different setting towards each other. In 1:2 relationship Bob is following Joe's updates and has him on best friend list (add bff column), while in 2:1 Joe doesn't have Bob on bff list and doesn't care to follow his posts (following column).
我同意其他人的说法，为关系（1：2和2：1）设置2个插入并不是一个坏主意。这实际上有助于扩展现代社交网络中常见的一些功能。我能想到的一些实际使用案例是关系或其他属性的描述。虽然个人仍然是朋友，但他们保持着彼此不同的环境。在1：2的关系中，Bob跟随Joe的更新并将他列在最好的朋友列表中（添加bff列），而在2：1中Joe没有Bob在bff列表上并且不关心他的帖子（在列之后） 。
#1
7
meaning that unique keys become flawed.
意味着唯一键变得有缺陷。
uid1 = 1 uid2 = 2Is the same as:
是相同的：
uid1 = 2 uid2 = 1
Nope, it's not.
不，不是。
On Facebook, for instance, I've a number of customers who sent requests to become "friends" that I never accepted... Since they're mere acquaintances.
例如，在Facebook上，我有许多客户发出请求成为我从未接受的“朋友”...因为他们只是熟人。
Along the same lines, I might have marked a few people as best friends, and they didn't reciprocate, or vice versa. Or perhaps I'm ignoring a few and they are not.
同样，我可能会将一些人标记为最好的朋友，但他们没有回应，反之亦然。或许我忽略了一些而他们不是。
Basically, there's a lot more information in a (uid1, uid2) tuple than mere IDs.
基本上，（uid1，uid2）元组中的信息比仅仅ID更多。
Make sure that you never need to deal with situations like these before deciding to add e.g. a uid1 < uid2 constraint on your table.
在决定添加之前，请确保您永远不需要处理这些情况。你桌子上的uid1
#2
6
If the relationship you're describing is symmetrical, as in "Bob is a friend of Joe" means "Joe is also a friend of Bob", then you can make sure in your code that the smaller of the 2 user IDs goes on the first column, and the larger one goes on the second column. This constraint pretty much ensures that the records in your lookup table will be unique. It also means that when you're performing a lookup, you usually have to search both columns.
如果您描述的关系是对称的，如“Bob是Joe的朋友”意味着“Joe也是Bob的朋友”，那么您可以在代码中确保2个用户ID中的较小者继续第一列，较大的一列在第二列。这种约束几乎可以确保查找表中的记录是唯一的。这也意味着当您执行查找时，通常必须搜索两列。
For example, if you were trying to get all of Bob's friends, you would have to query for records that have Bob's ID in either column. This leads to a bit more code and possibly an impact on performance.
例如，如果您尝试获取Bob的所有朋友，则必须查询在任一列中具有Bob ID的记录。这会导致更多代码，并可能对性能产生影响。
If the relationship can be asymmetrical, as in "Bob is a friend of Joe" does not necessarily mean "Joe is also a friend of Bob", then you need 2 entries for every pair of users: Bob - Joe and Joe - Bob. This means that your lookup table will contain twice as many entries and also that your site is very stalker-friendly :D Of course, you can still choose to apply this system even though your relationship is symmetrical.
如果关系可以是不对称的，如“Bob是Joe的朋友”并不一定意味着“Joe也是Bob的朋友”，那么每对用户需要2个条目：Bob - Joe和Joe - Bob。这意味着您的查找表将包含两倍的条目，并且您的站点对跟踪器非常友好：D当然，即使您的关系是对称的，您仍然可以选择应用此系统。
Using this method, if you want to get all of Bob's friends you just have to select the records with Bob's ID in the first column. It is possible that this might mean faster lookups and less code for you to write, but again, it means you're taking up more room in your database.
使用这种方法，如果你想得到Bob的所有朋友，你只需要在第一列中选择带有Bob ID的记录。这可能意味着更快的查找速度和更少的代码供您编写，但同样，这意味着您在数据库中占用更多空间。
#3
2
This is not that uncommon.
这并不罕见。
Typically what is done is that there is a table, as in most many-many relationships that consists of two columns, each being an id, of the two tables, that make up the primary key.
通常所做的是有一个表，就像在大多数关系中一样，它由两个列组成，每个列是构成主键的两个表的id。
As you stated userId1 and userId2. Attributes, if necessary, can be added to the relationship (such as classification of friendship).
正如您所说的userId1和userId2。如有必要，可以将属性添加到关系中（例如友谊分类）。
When user 1 befriends user 2, there are typically two inserts, (1,2) and (2,1).
当用户1与用户2成为朋友时，通常有两个插入，（1,2）和（2,1）。
Same sort of thing with a defriending, there needs to be two deletes.
与defriending相同的事情，需要有两个删除。
You could end up with a user having himself as a friend, and that might be critical to the actual workings of the system. If a user can only view photos of his friends, then if he is not a friend to himself, some systems might not allow him to see his own photos.
您最终可能会将自己视为朋友，这可能对系统的实际运行至关重要。如果用户只能查看他朋友的照片，那么如果他不是自己的朋友，某些系统可能不允许他看到他自己的照片。
It is very dependent upon how the application is written on top of the database.
它非常依赖于如何在数据库上编写应用程序。
#4
1
I agree with what others have said, it's isn't a bad idea to have 2 inserts for a relationship (1:2 and 2:1). This actually helps to expand some of the features which are commonly found in modern social networks. Some cases of practical use that I can think of is description of relationship or other attributes. While individuals remain friends they maintain different setting towards each other. In 1:2 relationship Bob is following Joe's updates and has him on best friend list (add bff column), while in 2:1 Joe doesn't have Bob on bff list and doesn't care to follow his posts (following column).
我同意其他人的说法，为关系（1：2和2：1）设置2个插入并不是一个坏主意。这实际上有助于扩展现代社交网络中常见的一些功能。我能想到的一些实际使用案例是关系或其他属性的描述。虽然个人仍然是朋友，但他们保持着彼此不同的环境。在1：2的关系中，Bob跟随Joe的更新并将他列在最好的朋友列表中（添加bff列），而在2：1中Joe没有Bob在bff列表上并且不关心他的帖子（在列之后） 。