friendship database schema

Question

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

Answer 1

Yes (use a n:m link table).

You have two entities:

• user (for User-Data like ID, Name, email etc.)
• relationship (links two users by ID)

create one table each.

Answer 2

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.