Database Design for User Friendship

Question

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

If I have 2 users that have user IDs 10 and 20, I have to do two inserts in db when they become friends

ID USER1 USER2 1 10 20 2 20 10 

or is there a way to request db to get only specific user friends if I only inserted them like this

 ID USER1 USER2 1 10 20 

I know that the first way can 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 method can be requested to get the result, I would search, like all users, 10 friends.

Answer 1

Brad Christie's suggestion to query a table in both directions is good. However, given that MySQL does not optimize OR queries very well, using UNION ALL may 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 SQLFiddle from , 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 cannot really test real performance, but comparing execution plans between two versions can be instructive.

Answer 2

Friendship is a two-way communication (for all purposes and goals). Unlike another link (for example, messages unilaterally) friendship should have only one record. However, what you see is true; you will need to query both columns to get the user's friends, but 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