将三元关系分解为二元关系 [英] Decomposing a ternary relationship into binary relationships

问题描述

我正在设计一个处理用户，帐户和项目的数据库，具有以下关系和限制：

• 一个帐户有很多用户


• 一个用户属于很多帐户


• 一个帐户有许多项目


• 一个项目属于只有一个帐户


• 用户在许多项目中进行协作（冗余注释：每个项目都属于自己的帐户）。

换句话说，用户可以在同一个帐户的许多项目中进行协作。但是，由于用户可以属于多个帐户，因此用户可以在多个帐户的许多项目中进行协作。这导致我进入一个三元协作关系：

在阅读了关于将三元关系转换为二进制关系的几篇论文之后，我提出了以下等效关系： p>

这里有两个问题：

1. 这个转换是否正确？我发现我必须在应用程序级别添加额外的检查来处理插入。例如，在添加新的（User，Project）之前，我必须检查该用户是否属于该项目所属的同一个帐户。




2. 真的有必要建立帐户和用户之间的关系吗？一旦添加了 User 和 Project 之间的关系，我们无法通过访问知道用户所属的帐户项目？

谢谢!!

解决方案

此转换是否正确？

如果正确 等价，然后否。

没有连接项目和帐户没有连接用户（等等）这在真正的三元关系中是不可能的。

真的有必要建立帐户和用户之间的关系吗？ ...我们不能通过访问项目知道用户所属的帐户？

实际上，我们只知道哪些帐户是连接到用户的候选人，但是我们没有办法选择一个。

这个方案的真正问题是它允许你连接用户到与任何用户的项目无关的帐户。

---

在我看来，如果你需要一个三元关系，直接在物理模型中直接代表它。如果我正确地了解你的要求，这将是这样的：

AccountId 在协作

/ code> PK。这意味着每个项目/用户组合必须连接到一个帐户（不同的组合可以连接到另一个帐户）。

I am designing a database that handles users, accounts and projects with the following relationships and constraints:

• An account has many users
• A user belongs to many accounts
• An account has many projects
• A project belongs to only one account
• A user collaborates in many projects (redundant note: each one of them belonging to its own account).

In other words, a user can collaborate in many projects of the same account. But since a user can belong to several accounts, thus a user can collaborate in many projects of several accounts. This leads me to a ternary collaborates relationship:

After reading a couple of papers about converting ternary relationships into binary relationships I came up with the following equivalent relationships:

Two question arises here:

1. Is this conversion correct? I have found that I have to add additional checks at application level to handle insertions. For instance, before adding a new (User,Project) I have to check that the user belongs to the same account that the project belongs to.

2. Is it really necessary to establish the relationship between Account and User? Once the relationship between User and Project has been added, couldn't we know the account a user belongs to by accessing the project?

Thanks!!

解决方案

Is this conversion correct?

If by "correct" you mean "equivalent", then no.

There is nothing to stop you connecting project and account without connecting a user (etc...), which would not be possible in a real ternary relationship.

Is it really necessary to establish the relationship between Account and User? ... couldn't we know the account a user belongs to by accessing the project?

Actually, we would only know which accounts are "candidates" to be connected to the user, but we would have no good way to pick one.

The real problem with this scheme is that it allows you to connect the user to an account unrelated to any of the user's projects.

---

In my opinion, if you need a ternary relationship, just go ahead and directly represent it in the physical model. If I understand your requirements correctly, this would look something like this:

Note how AccountId is outside Collaboration PK. This means every project/user combination must be connected to exactly one account (a different combination can still be connected to a different account).

这篇关于将三元关系分解为二元关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！