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:
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
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.
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