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?

sorry i'm wrong - it would not work well (the query to find a friendship is wrong). I'll delete my answer. — m_x, Feb 28 '13 at 19:52

score 4 · Accepted Answer · answered Feb 28 '13 at 19:31

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.

A bi-directional friendship is what I was going for, thank you. Even with the second option though there would still be some awkward queries(ex. to list user1's friends you have to `(select friend1 from friends where friend2=user1) union (select friend2 from friends where friend1=user1)`) — user1916823, Feb 28 '13 at 20:30

score 1 · Answer 2 · answered Feb 28 '13 at 19:16

1

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.

answered Feb 28 '13 at 19:16

Gordon Linoff

1,242,037
58
646
786

This is a nice way to represent the nature of the relationship in the most literal way possible at the database level. In fact, with a couple of domain and unique constraints and a simple helper function in the application layer, this can be done without gnarly triggers or cumbersome sprocs. For example, you could add a `Position` column to the friends table (> 0, <= 2, unique with `friendshipID`). To boot, it would still be trivial to insert data by hand if necessary. I would probably query the friends table with a self-join, though, rather than a `having` clause. – Isabelle Wedin Feb 28 '13 at 20:50

score 0 · Answer 3 · edited May 23 '17 at 11:46

You can do a has_many through or has_and_belongs_to_many http://guides.rubyonrails.org/association_basics.html

any how you want a join table that links your user models.

for example

class User < ActiveRecord::Base
  has_many :followings
  has_many :followers, :through => :followings, :class_name => "User"
  has_many :followees, :through => :followings, :class_name => "User"
end


class Following < ActiveRecord::Base 
  # fields: follower_id followee_id (person being followed)
  belongs_to :follower, :class_name => "User"
  belongs_to :followee, :class_name => "User"
end

same as user has many :users, or must I use another way for a friend based social network?

How to represent interchangeable columns

3 Answers3