Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

+------+-------------+
|userId|relatedUserId|
+------+-------------+
|   1  |      2      |
|   1  |      3      |
|   2  |      1      |
|   2  |      3      |
|   3  |      1      |
|   3  |      2      |
+------+-------------+
share|improve this question
3  
Just add a CHECK constraint (userId>relatedUserId), and you are all set. –  AlexKuznetsov Apr 1 '12 at 22:16
    
@AlexKuznetsov: Using > would prevent at least the first two relationships in this example from existing. <> (or !=) would probably be better. –  FrustratedWithFormsDesigner Apr 2 '12 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. –  Leigh Riffel Jun 29 '12 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.

share|improve this answer
    
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? –  ypercube Jun 29 '12 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. –  Leigh Riffel Jun 29 '12 at 16:03
    
No argue in that. Just wodering on the performance aspects of it. You surely have more experience with big databases. –  ypercube Jun 29 '12 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. –  Leigh Riffel Jun 29 '12 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

share|improve this answer
    
The OP made the meaning of the relationship clear by calling relationships in the opposite direction redundant. –  Leigh Riffel Jun 29 '12 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. –  ssmusoke Jun 29 '12 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. –  Leigh Riffel Jun 29 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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