geoffs3310
geoffs3310

Reputation: 14008

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

Upvotes: 0

Views: 129

Answers (4)

Justin Buser
Justin Buser

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

user742071
user742071

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

Frank Schmitt
Frank Schmitt

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

wonk0
wonk0

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

Related Questions