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 only takes a minute to sign up.
Sign up to join this community
If I want to link users with other users. Would this be a good way to do it:
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 |
+------+-------------+
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 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
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.
>would prevent at least the first two relationships in this example from existing.<>(or!=) would probably be better.<>would prevent1,1or2,2, but would not prevent1,2or2,3. The point of preventing these is to enforce an entry order for the values. By enforcing the order1,2would have to be entered the same way as2,1(as1,2) and would therefore be detected as a duplicate.