Sample Header Ad - 728x90

Efficient many to two relationship

0 votes
1 answer
189 views
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
Asked by PlankTon (183 rep)
Mar 9, 2018, 06:31 AM
Last activity: Jun 20, 2025, 10:00 PM