友谊数据库模式 [英] friendship database schema

问题描述

我创建一个数据库模式，涉及可能是朋友的用户，我想知道什么是最好的方式来建模这些朋友的友谊的能力。应该是它自己的表，只有两列，每个表示一个用户？感谢！

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!

推荐答案

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

当用户1向用户2发送友谊请求时，请执行

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

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

如果用户2拒绝了请求，

If user 2 denies the request,

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

如果用户2接受：

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.

这篇关于友谊数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！