In social systems, The design of the relationship between users and their friends is essential, So how to design a friend's database is very important, This article takes you to learn about the relevant design scheme.

Basic analysis

First step, There is a user table, Table contains basic information of users, For example, account number, Full name, Gender and other information. Use heretb_user Represent user information table.

ID User name
1 Zhang San
2 Li Si
3 Wang Wu
4 Zhao Liu
The second step, You need to establish a direct friend relationship between users. There are two situations here: One way friend relationship, Be friends with each other.
- One way friend relationship is that Zhang San is on Li Si's friend list, But Li Si is not on Zhang San's friends list;
- Be friends with each other, If Zhang San and Li Si are good friends, Then both parties are in each other's friends list;

Friend relationship design

No matter which of the above two relationships, The following design can be used for friend relationship table, surfacetb_friend:

ID user_id friend_id
1 1 2
2 1 3
Example, Zhang San has two good friends, Li Si and Wang Wu.

One way friend mode

If it's one-way friend mode, So 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 at this timesql Statement queries are limited onlyuser_id As a condition, you can find the user's friends list:
select * from tb_friend where user_id = 1
Be friends with each other

Because they are good friends with each other, Only one piece of data needs to be inserted. The corresponding query statement is:
select * from tb_friend where user_id = 1 or friend_id = 1
It can also be usedUNION ALL To achieve:
select friend_id as friends from tb_friend where user_id = 1 UNION ALL -- Use
UNION ALL, Because there is no duplicate select user_id as friends from tb_friend where friend_id = 1
Matters needing attention:
- user_id1–>friend_id2 anduser_id2–>friend_id1 It's the same record, 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 into the program layeruser_id1 < user_id2;
- Can be added to the cache layer(Redis orMemcached) To improve performance;
- Can be restricted from the database layer(user_id,friend_id) Not duplicated;

Join group

If there are more friends, Complex relationship, Group of friends can be introduced, The following modifications can be carried out:

ID user_id friend_id user_group friend_group
1 1 2 Good friend Classmate
2 1 3 Classmate Classmate
Added in databaseuser_group, currentuser tofriend Groups set,friend_group It is the present.user Group categories set by friends of.

Therefore, Query friends list'sSQL 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 andSQL Statement use basic completion. It can be modified according to the specific business situation. In addition to the query friendsid After the list, you can query the friend information. Note here that if you usein Statement to query whether there is an index,sql Statement size limit, Performance and other problems, Consider using left join for query.

Original link:

Pay attention to wechat public

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