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 .
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 ;
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
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 ：https://www.choupangxia.com/topic/detail/74
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）