CODEWITHSUNDEEP
database-designentity-relationship
elitalon
elitalon

Reputation: 9447

Decomposing a ternary relationship into binary relationships

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

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:

enter image description here

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

enter image description here

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

Upvotes: 9

Views: 14222

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

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:

enter image description here

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

Upvotes: 15

Related Questions