对于同一个表的相同属性之间的多对多关系，正确的表和联接结构是什么？
假设我有一个带有 UserID 列的用户表，我需要建模一个场景，其中用户可以与另一个用户建立多种关系，例如电话。任何用户都可以与其他用户发起 0...n 次电话呼叫。
它会是一个经典的连接表吗
UserCalls
-----------------------
| CallerID | CalleeID |
-----------------------
？
Lets say I have a Users table with a UserID column and I need to model a scenario where a user can have multiple relationships with another user, e.g. phone calls. Any user can initiate 0...n phone calls with another user.
Would it be a classic junction table like:
UserCalls
-----------------------
| CallerID | CalleeID |
-----------------------
?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于这些表来说，真正重要的是主键。如果允许一个人多次呼叫另一个人，并且每次都由不同的行表示，则 (Caller, Callee) 不是候选键。需要有诸如代理键或某种时间戳之类的东西来确保您拥有良好的主键。
此外，从业务规则的角度来看，如果关系是可逆的，则任何时候您查找呼叫时，您只关心两方是否相同（而不是谁呼叫了谁），让表以您所拥有的方式区分它们可能会有问题。解决这个问题的典型方法是使用一个 Calls 表和一个 CallParties 表，将呼叫链接到呼叫中的各方（可能具有有助于识别呼叫发起者的标志）。通过这种方式，列顺序依赖性消失，并且可能使某些查询变得更容易（它可能使其他查询变得更困难）。这也可以减少所需的索引数量。
因此，我会首先考虑您所拥有的表格设计，但也要记住可能需要逆转。
The thing that's really important to get right on these tables is the primary key. If a person is allowed to call another person several times and each is represented by a distinct row, then (Caller, Callee) is not a candidate key. There needs to be something like a surrogate key or some kind of timestamp which is used to ensure you have a good primary key.
In addition, from a business rules perspective, if the relationship is reversible where any time you are looking for calls, you only care that the two parties were the same (not who called who), having the table distinguish them in the way you have can be problematic. The typical way around that is to have a Calls table and a CallParties table which links the call to the parties in the call (which may have flags which help identify the call originator). In this way the column order dependency goes away and MAY make certain queries easier (it may make others more difficult). This can also reduce the number of indexes required.
So, I would consider first the table design as you have it, but also keep in mind the possible need for reversals.
听起来你走在正确的轨道上......
对于你的 PK，请考虑这篇关于选择 PK 的文章：
http://www.agiledata.org/essays/keys.html
Sounds like you're on the right track...
For your PK, consider this article on choosing a PK:
http://www.agiledata.org/essays/keys.html
是的，这是正确的。
Yes, that's correct.