0

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

5
  • Distinguish them arithmetically by user1_id < user2_id
    – Kondybas
    Mar 9, 2018 at 6:45
  • 1
    Which DBMS are you using? In Postgres you could store the pairs as an array which can be searched highly efficiently (faster than using LIKE). A graph database is another option to consider
    – user1822
    Mar 9, 2018 at 7:02
  • @a_horse_with_no_name I am using postgres, and an arrary field sounds like it might be ideal. Many thanks for the thought, will investigate
    – PlankTon
    Mar 9, 2018 at 7:03
  • @Kondybas Thanks for the thought, however with that approach I'd need to search both columns if/when I wanted to find all rows for a specific user.
    – PlankTon
    Mar 9, 2018 at 7:04
  • @PlankTon So what with the search? Anyway you need a complex unique index for (user1_id, user2_id) therefore you are always able to use WHERE user1_id = _some_id OR user2_id = _some_id
    – Kondybas
    Mar 9, 2018 at 7:15

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.