friendship database schema [closed]

Question

I'm creating a db schema that involves users that can be friends, and I was wondering what is the best way to model the ability for these users to have friendships. Should it be its own table that simply has two columns that each represents a user? Thanks!

Answer 1

create table  friendship( user bigint,  friend bigint, primary key(user, friend), key(friend, user), constraint `fk_user` foreign key (user) references user(id), constraint `fk_friend` foreign key (friend) references user(id) ); 

When user 1 sends a friendship request to user 2, do

insert into friendship (user, friend) values (1,2); 

If user 2 denies the request,

delete from friendship where user = 1 and friend = 2; 

if user 2 accepts it:

insert into friendship (user, friend) values (2,1); 

Then, a friendship can be found this way:

select f1.*  from friendship f1 inner join friendship f2 on f1.user = f2.friend and f1.friend = f2.user; 

You can make a view with this last query, it will help you query-ing for users' friends, or even friends of friends.

Answer 2

I am not sure that, this is the way I would want to model the whole set up. When a user A sends request to user B, in my opinion, pending_friendship is created. When user B accepts this request, a friendship between the two is established. Usually this would be a two way relationship and would be great. But I would want to ensure that there is scope for a one way relationship in future. So I would model the scenario by making use of two tables for the relationship and one table for the user himself.

User( user_id, email, password_hash, name, ..., ... )

pending_friendship( user_id_from, user_id_to )

friendship( friendship_id, current_user_id, friend_user_id, is_following boolean # This would ensure scope for one way relationship. )

Now lets look up the use cases-

1- User A sends a request to user B- I would create an entry in the pending_friendship table. When user B wants to see which friend requests are pending, we can just do a select query based on friend_request_to column.

2- User B accepts the friend request- The pending_friendship item is deleted from the table. Two entries are made in the friendship table for the two sided relationship. Both users would follow each other.

3- User B is a friend and does not want to follow user A's feed- The is_following column is set to false for user B's friendship row of user A.

4- User A does not want to be friends anymore with B-(After all B has unfollowed him in case 3 :-) ) We go ahead and remove the two rows for friendship between the two users.

This schema does have more complexity but adds more clarity. It also allows you to have relationship of following a user.