三实体交集表“多-多-多”
我目前正在从事数据模型设计（将由 RDMS 支持）。它基于与组织成员的福利相关的 3 个实体。这些实体是福利、会员类型和提供商。我创建了一个标准的多对多交集表来关联两个实体，但从未关联过 3 个实体。我想知道是否有人做过这样的事情，以及是否存在任何我应该避免的潜在陷阱。该表如下所示：
create table BENEFIT_MEM_TYPE_PROVIDER
(
BENEFIT_ID reference BENEFITS not null,
MEMBERSHIP_TYPE_ID reference MEMBERSHIP_TYPES not null,
PROVIDER_ID reference PROVIDERS not null,
primary key (BENEFIT_ID, MEMBERSHIP_TYPE_ID, PROVIDER_ID)
)
这种关系的某些部分不适合我，所以我想我应该向聪明人寻求任何建议。
谢谢
I'm currently working on a data model design (which will be backed by an RDMS). It is based around 3 entities relating to benefits for a member of the organization. The entities are the Benefit, Membership Type, and Provider. I've created a standard many-to-many intersection table to relate two entities, but never 3. I'm wondering if anybody has done such a thing and if there are any potential pitfalls I should keep out for. The table would be like the following:
create table BENEFIT_MEM_TYPE_PROVIDER
(
BENEFIT_ID reference BENEFITS not null,
MEMBERSHIP_TYPE_ID reference MEMBERSHIP_TYPES not null,
PROVIDER_ID reference PROVIDERS not null,
primary key (BENEFIT_ID, MEMBERSHIP_TYPE_ID, PROVIDER_ID)
)
Something about this relationship just doesn't sit right with me, so I thought I'd ask the smart folks for any advice.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的。
三元关系的使用相当广泛，尽管不像二元关系那样广泛使用。这甚至可以扩展到四元或一般的 n 元关系。
在数据库设计中可以看到 n 元关系的一个地方是星型模式。星形中心的事实表是n元关系，其中n是事实表引用的维度表的数量。
规范化过程包括检测与某些规范形式的偏离，并分解表格以产生更规范化的等价形式。分解有时会导致 n 元关系的顺序降低。
星型模式故意走相反的路。这就是为什么星型模式和标准化经常被认为是相互矛盾的。
Yes.
Ternary relationships are quite widely used, although not as widely used as binary relationships. This can even be extended to quaternary or in general n-ary relationships.
One place where you can see n-ary relationships in database design is star schema. The fact table at the center of a star is an n-ary relationship, where n is the number of dimension tables referenced by the fact table.
The normalization process consists of detecting departures from some normal form, and decomposing tables in order to yield a more normalized equivalent. Decomposition sometimes results lowering the order of n-ary relationships.
Star schema deliberately goes the other way. That is why star schema and normalization are so frequently seen as being at odds with each other.
是的。
如果福利会员资格、福利提供者和会员提供者之间存在从该表中的数据看来可能是正确的关系，但实际上不应如此，则这可能会导致问题。
例如，会员-提供者-福利可能有一个“限制”，即该福利仅在涉及特定提供者时适用于会员。这种关系可能不是一般意义上的真实关系，而是由于某些条件而在特殊情况下是真实的。
在这种情况下，您需要在此表中携带该限定条件，以确保可以通过简单的 SQL 查询正确发现这些异常情况。
简而言之，当您有超过 1 个关系时，请确保每行中的成对关系也是正确的。
Yes.
This can lead to issues if there are relationships among Benefit-Membership, Benefit-Provider and Membership-Provider that might appear true from data in this table, but shouldn't actually be true.
For example, a Member-Provider-Benefit might have a "restriction" that the Benefit only applies to the Member when a specific Provider is involved. It's possible that the relationship isn't generally true, but is true as a special case because of some qualification.
In this case, you'll need to carry that qualification in this table to be sure that these exceptional cases can be discovered properly by simple SQL queries.
In short, when you have more than 1 relationship, be sure the pair-wise relationships in each row are also true.
您应该验证您的表是否满足第五范式（5NF）。如果不是，那么最好对其进行标准化，使其处于 5NF 状态。
You should verify whether your table satisfies Fifth Normal Form (5NF). If it does not then it's probably better to normalize it so that it is in 5NF.