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
user1_id < user2_id
LIKE
). A graph database is another option to considerWHERE user1_id = _some_id OR user2_id = _some_id