Decomposing a ternary relationship into binary relationships

Question

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!!

Answer 1

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).