Reputation: 14008
I am creating a social networking site and need to know the best way to join users together as friends. I have a users table and each user has their own unique id called user_id.
I made a table called friends with two fields; user_id and friend_user_id however I'm thinking this might not be the best way to do it since it could allow for duplicate entries.
For example if I had an entry in this table like so: user_id 1 and friend_user_id 3 I could then insert the reverse like so: user_id 3 and friend_user_id 1
These would be duplicate entries.
Is there a better way or will I just have to program in logic in my website to prevent being able to add duplicates?
Thanks
Upvotes: 0
Views: 129
Reputation: 2871
The way I do this is to have user_id and friend_id fields, user_id being the person who originally requested the friendship, and obviously friend_id being the person who they requested. Then just search for user_id=friend_id OR user_id=user_id when loading friends.
Upvotes: 0
Reputation:
There is nothing wrong with your data model, I think this is the only workable way when modelling this in a relational database.
If you'd want to prevent duplicate entries, you could make sure that you insert the lower of the two id's in the first column (user_id) and the higher id in the second column (friend_user_id). Tthis will make sure you're unique key constraint works as expected.
As far as I know, MySQL doesn't support CHECK CONSTRAINTS to enforce that the first column contains a lower value than the second, so you have to do this in your application code.
You will however need to check both columns when performing a query to find all the friends for a specific user:
SELECT * FROM friends WHERE user_id = :current_user OR friend_user_id = :current_user
Upvotes: 0
Reputation: 30765
Your approach sounds sensible. To prevent duplicates, you could first sort your tuple (so that user_id is always smaller than friend_user_id) before inserting and add add a unique key on (user_id, friend_user_id).
Upvotes: 1
Reputation: 13952
Simply add a unique index on user_id, friend_user_id and one on friend_user_id, user_id. One of those might even be the primary key.
Upvotes: 0