Efficient many to two relationship

Question

I'm building out a database with a users table, and I want to store the mutual (Facebook) friends between every set of two users in the database. Doing this in a normalized, efficient way seems tricky. I've considered two options so far:

Option 1

Create a table mutual_friend_set with columns id, user1_id, user2_id. Create another table mutual_friends with columns id, mutual_friend_set_id, name, picture, unique_mutual_friend_id.

The problem with this solution is that there's nothing to distinguish user1_id from user2_id, so I'd need to either create duplicate entries such as:

id   user1_id  user2_id
1    123       456
1    456       123

which would take up twice as many rows as necessary and throw normalization out the window, or ensure the lower of the 2 user_ids was in the user1_id column. If, however, I wanted to get all mutual friend sets for a single user, I'd need to query across both columns.

Option 2

Create a table mutual_friend_set with fields id, user_pair, such that user pair is a string of the combined user relationship, delimited by a comma. Eg: id: 1, user_pair: '123,456'. The user with the lower ID would be placed before the comma.

This would get around the normalization issues, but of course if I wanted to grab all the mutual friends pairs for a single user I'd have to run a LIKE query which isn't exactly efficient.

I assume this is a common problem, which raises the question: is there a standard way of doing this? Given my inexperience, am I missing something obvious?

Any ideas appreciated

Answer 1

Adding as friend is not a commutative operation. When A add B as friend that doesn't means that B do the same for A. That is why the mutuality emerge.

You definitely need both rows:

id   user1_id  user2_idz
1    123       456
2    456       123

If you want to get mutual relationships from that table, you have to JOIN it with itself:

SELECT w.*
  FROM friends AS w
  JOIN friends AS z ON z.user2_id = w.user1_id
                   AND z.user1_id = w.user2_id
                   AND w.user1_id < w.user2_id