In social systems , It is necessary to design the relationship between users and their friends , So how to design the database of friends is very important , This article takes you to learn about the relevant design scheme .

Basic analysis

The first step , There is a user table , The table contains the basic information of users , For example, account number , full name , Gender and other information . It's used here tb_user Represents the user information table .

ID user name
1 Zhang San
2 Li Si
3 Wang Wu
4 Zhao Liu
Step two , It is necessary to establish a friendly relationship between users and users . There are two situations : One way friendship , Be friends with each other .
- One way friendship is that Zhang San is in Li Si's friends list , But Li Si is not on Zhang San's friends list ;
- Be friends with each other , If Zhang San and Li Si are friends , Then both parties are on each other's friend list ;

Friend relationship design

No matter which of the above two relationships , The following design can be used for all friend relationship tables , surface tb_friend:

ID user_id friend_id
1 1 2
2 1 3
In the example , Zhang San has two friends, Li Si and Wang Wu .

One way friend mode

If it is one way friend mode , If two people are friends with each other, the inserted data should be like this :

ID user_id friend_id
1 1 2
2 2 1
That is, Zhang San is a good friend of Li Si , Li Si is also a good friend of Zhang San . Use the sql Statement queries are limited only user_id As a condition, you can query the user's friend list :
select * from tb_friend where user_id = 1
Be friends with each other

Because it's a good relationship , You only need to insert a piece of data . The corresponding query statement is :
select * from tb_friend where user_id = 1 or friend_id = 1
It can also be used UNION ALL To achieve :
select friend_id as friends from tb_friend where user_id = 1 UNION ALL -- use
UNION ALL, Because there is no duplication select user_id as friends from tb_friend where friend_id = 1
matters needing attention :
- user_id1–>friend_id2 and user_id2–>friend_id1 It's the same record , There is no need to insert repeatedly ;
- In order to quickly judge whether two people are friends or not , You can add a restriction before inserting data at the program level user_id1 < user_id2;
- Can join cache layer (Redis or Memcached) To improve performance ;
- It can be restricted from the database layer (user_id,friend_id) Not repeatable ;

Join group

If there are more friends , The relationship is complicated , Can introduce friend group , The following modifications can be carried out :

ID user_id friend_id user_group friend_group
1 1 2 Friends classmate
2 1 3 classmate classmate
Added to the database user_group, current user to friend Grouping of settings ,friend_group It's the present user Group category set by friends of .

therefore , Query friends list SQL as follows :
select friend_id as friends ,user_group as my_group from tb_friends where
user_id =1 UNION ALL select user_id as friends , friend_group as my_group from
friend_id =1

So far, the design of friend relationship in social system and SQL Statement is basically completed with . It can be modified according to the specific business situation . In addition to friends in the query id After the list, you can query the information of friends . It should be noted here that if in Statement to query, there will be no index ,sql Statement size limit , Performance and other issues , Consider using left join for query .

Link to original text :

Pay attention to wechat public

More technology , framework , Management and other knowledge sharing , Please pay attention to WeChat official account. : New vision of procedure (ID:ershixiong_see_world)