9

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:

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

starblue
  • 55,348
  • 14
  • 97
  • 151
elitalon
  • 9,191
  • 10
  • 50
  • 86

1 Answers1

15

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

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I like your approach but a lot of folks recommend decomposing by default, hence my question. – elitalon May 15 '12 at 12:48
  • 1
    Who recommends decomposing by default? – Tony Andrews May 15 '12 at 12:50
  • 2
    @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](http://pragprog.com/the-pragmatic-programmer/extracts/coincidence). – Branko Dimitrijevic May 15 '12 at 12:54
  • 1
    @TonyAndrews You'd be surprised after a thorough Google search :p – elitalon May 15 '12 at 13:25
  • @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! – Tony Andrews May 15 '12 at 14:18
  • @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."_ – Branko Dimitrijevic May 15 '12 at 15:20
  • 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 – elitalon May 15 '12 at 17:59
  • "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? – VJune Sep 27 '12 at 07:20
  • 2
    @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. – Branko Dimitrijevic Sep 27 '12 at 09:42