MySQL好友表

我有一个 MySQL 数据库，我在其中存储有关每个用户的数据.

我想为每个用户添加一个朋友列表.我应该为数据库中的每个用户创建一个朋友表还是有更好的方法?

解决方案

假设你所有的朋友也在用户表中，你将需要一个朋友表，它定义了一个简单的一对多关系 - 将用户表链接回本身.所以

用户表UserID int 身份不为空[其他属性字段]朋友桌UserIDLink1 intUserIDLink2 int[其他属性字段]

其中 UserIDLink1 和 UserIDLink2 都是用户表上的外键.

例如，如果我有三个用户

1 乔2 比尔3 简

Joe 和 Jane 是朋友，那么 Friends 表将包含一行

1 3

以上隐含假设，如果 A 是 B 的朋友，那么 B 是 A 的朋友 - 如果不是这种情况，您可能希望将 UserIDLink1 和 UserIDLink2 重命名为 UserID 和 FriendID 或类似名称 - 在这种情况下您也可以将记录翻倍.

同样对于双向配置(如果 B 是 A 的朋友，A 是 B 的朋友)你应该在 Friends 表上为 (UserIDLink1,UserIDLink2) 和 (UserIDLink2,UserIDLink1) 设置索引以确保访问是如果我们搜索 joe 的朋友或 jane 的朋友，总是有效的(如果您没有设置第二个索引，那么第一个查询将是一个有效的索引查找，但第二个将需要全表扫描).

如果您的链接不是双向的，就没有必要找出 A 的朋友是谁，但您可能仍然最需要它，因为您可能还需要找出 B 的朋友.

I have a MySQL DB in which I store data about each user.

I would like to add a list of friends for each user. Should I create a table of friends for each user in the DB or is there a better way?

解决方案

Assuming all your friends are also in the user table you will need a friends table which defines a simple one-to-many relationship - linking the users table back to itself. So

User Table
UserID int identity not null
[other attribute fields]

Friends Table
UserIDLink1 int
UserIDLink2 int 
[other attribute field]

Where both UserIDLink1 and UserIDLink2 are foreign keys on the Users table.

So for instance if I have three users

1 Joe
2 Bill
3 Jane

and Joe and Jane are friends then the Friends table would contain a single row

1 3

The above implicitly assumes that if A is a friend of B then B is a friend of A - if this isn't the case you'd probably want to rename UserIDLink1 and UserIDLink2 to UserID and FriendID or similar - in which case you'd have up to double the records too.

Also for the bi-directional configuration (A is a friend of B if B is a friend of A) you should set up indexes on the Friends table for (UserIDLink1,UserIDLink2) and (UserIDLink2,UserIDLink1) to ensure access is always efficient if we were searching either for friends of joe or friends of jane (if you didn't set up the second index then the first query would be an efficient index lookup but the second would require a full table scan).

If your links were not bidirectional this wouldn't be necessary to find out who A's friends are, but you would still probably most require it as you'll likely also need to find out who B is a friend of.