If I want to link users with other users. Would this be a good way to do it:

Relationship diagram

I'm asking because I probably get double data like this if i don't do specific checks:

|   1  |      2      |
|   1  |      3      |
|   2  |      1      |
|   2  |      3      |
|   3  |      1      |
|   3  |      2      |
  • 3
    Just add a CHECK constraint (userId>relatedUserId), and you are all set.
    – A-K
    Commented Apr 1, 2012 at 22:16
  • @AlexKuznetsov: Using > would prevent at least the first two relationships in this example from existing. <> (or !=) would probably be better. Commented Apr 2, 2012 at 18:28
  • @FrustratedWithFormsDesigner <> would prevent 1,1 or 2,2, but would not prevent 1,2 or 2,3. The point of preventing these is to enforce an entry order for the values. By enforcing the order 1,2 would have to be entered the same way as 2,1 (as 1,2) and would therefore be detected as a duplicate. Commented Jun 29, 2012 at 13:00

3 Answers 3


It sounds like you are describing a mutual relationship such that there is no difference between (1,2) and (2,1). In this case you should design your code to always insert the lower userId as the first value and the higher as the second. Then you can use AlexKuznetsov's suggestion and add a CHECK constraint of (userId<relatedUserId). The code can then catch the duplicate exceptions and ignore them since the relationship already exists.

Note: I flipped the comparison from > to < because the lower number being first makes more sense to me. It works either way and may make more sense the other way in your environment.

  • I agree that this is a valid answer (and have given it, too, in a SO question) but I'm thinking it may lead to more complex and less performant queries. Do you think that would be true - with a big relationship table? Commented Jun 29, 2012 at 15:53
  • @ypercube Examining what is actually being done may lead to alternatives such as the one Cade Roux suggests, but the OP's stated goal was to prevent redundant relationships. Commented Jun 29, 2012 at 16:03
  • No argue in that. Just wodering on the performance aspects of it. You surely have more experience with big databases. Commented Jun 29, 2012 at 16:07
  • For performance, if goal was to make searching for entries containing a particular user fast, I would probably ignore the duplication and have the application insert the data both ways (as Cade suggested). If the goal was to make searching for a particular set fast, this single entry method would be preferred. Commented Jun 29, 2012 at 17:16

I think the question of duplicated data depends on the meaning of the relationship between the two users. For example if the relationship is who is following who, then User 1 can follow user 2, but that does not mean that User 2 is following user 1.

However if the relationship is where there are no duplicates, for example if the two belong to a team or work together, then the above model would not necessarily work since you need to relate them through another entity, team in my example

  • The OP made the meaning of the relationship clear by calling relationships in the opposite direction redundant. Commented Jun 29, 2012 at 14:47
  • Nope it is not, if you look at Twitter for example If User 1 follows 2, then 1 can send 2 a direct message, but 2 cannot send 1 a direct message. It all depends on the business rules assigned to the relationship between 1 and 2. Commented Jun 29, 2012 at 14:50
  • Of course if you depart from this question there are situations in which your statement would be true, I'm just pointing out that for this question such a relationship does not exist. Commented Jun 29, 2012 at 14:53

It seems like this is a symmetric relationship, however, in usage a symmetric relationship becomes a little problematic, since the table has to be joined both ways (effectively a UNION or OR) and indexed both ways.

Sometimes it can be easier to have only asymmetric relationships and enforce that both exist with a trigger or something.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.