Designing A Database For User Friendship

Question

I would like to store friendships in a database. My idea is that when user1 becomes friends with user2 I store that friendship so that I can get all of either user's friends if I ever need it. At first I thought I would just store their id's in a table with one insert, but then I thought about some complications while querying the db.

If I have 2 users that have a user id of 10 and 20 should I make two inserts into the db when they become friends

ID USER1 USER2
1  10    20
2  20    10

or is there a way to query the db to only get a particular users friends if I only did one insert like so

ID USER1 USER2
1  10    20

I know the first way can definitely give me what I am looking for but I would like to know if this is good practice and if there is a better alternative. And if the second way can be queried to get me the result I would be looking for like all of user 10's friends.

Answer 1

Brad Christie's suggestion of querying the table in both directions is good. However, given that MySQL isn't very good at optimizing OR queries, using UNION ALL might be more efficient:

( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user1 = 1 AND f.user2 = u.id )
UNION ALL
( SELECT u.id, u.name
  FROM friendship f, user u
  WHERE f.user2 = 1 AND f.user1 = u.id )

Here's a SQLFiddle of it, based on Brad's example. I modified the friendship table to add two-way indexes for efficient access, and to remove the meaningless id column. Of course, with such a tiny example you can't really test real-world performance, but comparing the execution plans between the two versions may be instructive.

Answer 2

A friendship is a two-way bond (for all intents and purposes). Unlike another link (like a message that's one-way) a friendship should only have one entry. However, what you're seeing is correct; you would need to query against both columns to get a user's friends, but that's simple enough:

-- The uses of `1` below is where you'd insert the ID of
-- the person you're looking up friends on
SELECT      u.id, u.name
FROM        friendship f
  LEFT JOIN user u
  ON        (u.id = f.user1 OR u.id = f.user2)
    AND     u.id <> 1
WHERE       (f.user1 = 1 OR f.user2 = 1)

example here