How to represent interchangeable columns

Question

I'm not quite sure how to phrase this, but is there a good way to implement a table where the columns are essentially interchangeable?

Example: you have a Users model and want to allow two Users to be 'friends'. The obvious way, to me, would be to have a table containing two columns ('friend1' and 'friend2') that each containing the key to a User. This makes it awkward for saying something like "are user1 and user2 friends" because you have to check for "(friend1=user1 AND friend2=user2) OR (friend1=user2 AND friend2=user1)". It would work, but it just seems awkward to me that every time you want to get something from that table you're looking in both columns. Is there a more elegant way do this?

Answer 1

A key choice when making a friendship relationship, is deciding if it is bi-directional. Twitter following being an example of one directional friendship and Facebook friendships being bi-directional. Sounds like you're committed to the bi-directional, so the 2 options you have are:

1) Check both directions

select *
from friendships 
where (friend1 = 123 and friend2 = 456) OR (friend2 = 123 and friend1 = 456)

2) Always put the lower user_id into friend1 and the higher user_id into friend2, then your test only needs to check one direction. This is a little trickier to maintain, so I'd only do it needed for perf reasons.

Answer 2

The way that you can implement this might seem a bit awkward. The idea is to have a "friendshipId" in a table with two columns: friendshipId and user. Now the users are interchangeable.

To find out if user1 and user2 are friends:

select friendshipId
from friends
group by friendshipId
having sum(case when name = user1 then 1 else 0 end) > 0 and
       sum(case when name = user2 then 1 else 0 end) > 0

Judicious use of constraints, triggers, and stored procedures will ensure that a friend relationship has only two users, that someone cannot friend themselves, and so on.