What is the best way to join users as friends for a social networking site using a mysql database?

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

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

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.

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

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.