数据建模：是否总是需要使用交集表？
考虑以下情况：
我有两个表（客户和案例），定义如下：
**Customers**
CustomerId (PK)
CustomerName
CustomerPhone
**Cases**
CaseId (PK)
CaseManager
CaseNotes
每个客户可以拥有无限数量的案例，但每个案例只能属于一个客户。因此，为了将表相互关联，我将使用交集表 (CustomerCases)，它分别由每个表的外键组成。
但是，我不能将 Customers 表中的 CustomerID 作为外键添加到 Cases 表中吗？我觉得我失去了一些东西。如果我选择不使用交集表，是否应该注意一些陷阱？
Consider the following:
I have two tables (Customers and Cases) defined as follows:
**Customers**
CustomerId (PK)
CustomerName
CustomerPhone
**Cases**
CaseId (PK)
CaseManager
CaseNotes
Each customer can have an unlimited amount of cases, however each case can only belong to one customer. So in order to relate the tables to each other, I would use an intersection table (CustomerCases), that consists of the foreign keys from each table respectively.
However, couldn't I just add the CustomerID from the Customers table as a foreign key to the Cases table? I feel like I'm missing something. Are there any pitfalls that I should be aware of should I choose not to use an intersection table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果一个案例只能属于一个客户，那么在我看来，只需将 CustomerID FK 添加到 Cases 表中是完全合理的。
如果您认为需求最终可能会发生变化（例如，一个案例可能有多个客户），那么交集表方法可能更有意义。
此外，除非您有无限数量的 CaseManager，否则拥有一个 Managers 表并拥有一个来自 Cases 表的 FK 也可能是有意义的。
If one case can belong only to one customer, it seems totally reasonable to me that you just add a CustomerID FK to the Cases table.
If you think that requirement might eventually change (e.g. a case might have multiple customers), then the intersection table approach might make more sense.
Also unless you have an indefinite number of CaseManagers, it might also make sense to have a Managers table and have a FK from the Cases table to that.
仅需要对多对多关系建模时才需要“交集”（又名“接合点”或“交叉引用”）表。在这种情况下，案例参与与客户的多对一关系。因此，只需要一个外键即可 - 事实上，这将是一个更传统的解决方案。避免连接表不必要的复杂性——除非有相反的架构原因（例如，应用程序依赖于一个要求所有关系都使用连接来表示的组件）。
An "intersection" (aka "junction" or "cross-reference") table is only necessary to model a many-to-many relationship. In this situation, a Case participates in a many-to-one relationship with a Customer. So, a foreign key is all that is required -- and indeed that would be a more conventional solution. Avoid the unnecessary complexity of a junction table -- unless there is an architectural reason to the contrary (e.g. the application relies upon a component that demands that all relationships be represented using junctions).