同一张桌子上的多对多 [英] Many-to-many on same table
问题描述
有趣的是我从来没有遇到过这个！
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:
用户表有id"和name"列.
User table has "id" and "name" column.
User_relationship 表有uid1"和uid2"，代表用户是朋友"或好友"或朋友"或其他任何".
User_relationship table has "uid1" and "uid2", representing users that are "friends" or "buds" or "pals" or "whatever else".
很明显这里的问题是什么 - 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 = 1uid2 = 2
E.g.: uid1 = 1 uid2 = 2
等同于:
uid1 = 2uid2 = 1
uid1 = 2 uid2 = 1
因此，如果查询执行错误，则可能返回 2 条记录，或 0 条记录.
And therefore could return 2 records, or 0 records if the query is performed wrong.
本着设计好表格的精神，我不想为了检查现有值而必须扫描整个表格两次.
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.
在你问之前，我还没有尝试过任何东西，因为我已经看到我最喜欢的关联事物的方式(查找表)不足以满足我的需求，我需要一些帮助 - 我在SO 或 Google :(
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 :(
提前致谢.
推荐答案
意味着唯一键有缺陷.
meaning that unique keys become flawed.
uid1 = 1 uid2 = 2
等同于:
uid1 = 2 uid2 = 1
不，不是.
例如，在 Facebook 上，我有许多客户发送了成为朋友"的请求.我从来没有接受过......因为他们只是熟人.
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.
同样，我可能已经将一些人标记为最好的朋友，但他们没有回报，反之亦然.或者也许我忽略了一些而他们没有.
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.
基本上，(uid1, uid2) 元组中的信息比单纯的 ID 多得多.
Basically, there's a lot more information in a (uid1, uid2) tuple than mere IDs.
在决定添加之前，请确保您永远不需要处理此类情况，例如一个 uid1
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.
这篇关于同一张桌子上的多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
