java - 将朋友列表实现到数据库中的最佳方法？MySQL

Question

所以我的项目有一个“朋友列表”，在 MySQL 数据库中我创建了一个表：

名字A

姓名B

主键（nameA，nameB）

这将导致很多条目，但为了确保我的数据库是规范化的，我不确定如何实现这一点？

我的项目也使用 Redis .. 我可以将它们存储在那里。

当一个人加入服务器时，我必须搜索所有条目以查看他们的名字是nameA还是nameB，然后将这两个名字放在一起作为朋友，这也可能效率低下。

干杯。

标签： javamysqlsqlredis

Answer 1

The task is quite common. You want to store pairs where A|B has the same meaning as B|A. As a table has columns, one of the two will be stored in the first column and the other in the second, but who to store first and who second and why?

One solution is to always store the lesser ID first and the greater ID second:

userid1 | userid2
--------+--------
1       | 2
2       | 5
2       | 6
4       | 5

This has the advantage that you store each pair only once, as feels natural, but has the disadvantage that you must look up a person in both coumns and find their friend sometimes in the first and sometimes in the second column. That may make queries kind of clumsy.

Another method is to store the pairs redundantly (by using a trigger typically):

userid1 | userid2
--------+--------
1       | 2
2       | 1
2       | 5
2       | 6
4       | 5
5       | 2
5       | 4
6       | 2

Here querying is easier: Look the person up in one column and find their friends in the other. However, it looks kind of weird to have all pairs duplicated. And you rely on a trigger, which some people don't like.

A third method is to store numbered friendships:

friendship | user_id
-----------+--------
1          | 1
1          | 2
2          | 2
2          | 5
3          | 2
3          | 6
4          | 4
4          | 5

This gives both users in the pair equal value. But in order to find friends, you need to passes: find the friendships for a user, find the friends in these friendships. However, the design is very clear and even extensible, i.e. you could have friendships of three four or more users.

No method is really much better than the other.