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

Solution

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

Comments

[elitalon]

I like your approach but a lot of folks recommend decomposing by default, hence my question.

[Tony Andrews]

Who recommends decomposing by default?

[Branko Dimitrijevic]

@elitalon Don't ever do something just because somebody (including me!) tells you to. Always understand what are you doing and why, otherwise you'll be programming by coincidence.

[elitalon]

@TonyAndrews You'd be surprised after a thorough Google search :p

[Tony Andrews]

@elitalon - I just did a Google search and got lots of results but all seemed to involve buying a PDF to see the answer! Do you have a link to anything freely readable? I'm curious, but not enough to put my hand in my pocket!

[Branko Dimitrijevic]

@elitalon Let me quote the conclusion of "Binary Equivalents of Ternary Relationships in Entity-Relationship Modeling: a Logical Decomposition Approach" by Jones and Song: "We have shown in this paper, that logical (fully equivalent) decompositions do exist for certain combinations of ternary / binary cardinalities, but the majority do not have fully (logical and practical) equivalents."

[elitalon]

What a coincidence! That paper was one of the sources I read to try to decompose the ternary relations. That sentence was precisely the one which made me ask this question

[VJune]

"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." This is why elitalon wrote that you have to place application level checks. Are there any benefits of decomposing? Eliminating redundancy perhaps but is it a good trade off?

[Branko Dimitrijevic]

@aryan I'm not sure how OP's second model "eliminates redundancy". As a general rule, database-level declarative integrity should be preferred to application-level integrity. Reasons are numerous, but in a nutshell: DB constraints minimize chance for mistakes (through their declarative nature), promote reuse (since they are centralized and cannot be bypassed by a buggy application) and typically are more performant and scalable. There are cases where application-level integrity is justified, but DB integrity should definitively be your "default" choice.