MySQL的朋友表 [英] MySQL friends table

问题描述

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

我想为每个用户添加一个朋友列表。我应该为DB中的每个用户创建一个朋友表，还是有更好的方法？

解决方案

假设你所有的朋友都是同样在用户表中，您将需要一个定义简单的一对多关系的朋友表，将用户表链接回本身。所以

 用户表
 UserID int identity not null 
 [其他属性字段] 
 
朋友表
 UserIDLink1 int 
 UserIDLink2 int 
 [其他属性字段] 
  

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

所以例如，如果我有三个用户

  1 Joe 
 2 Bill 
 3 Jane 
  

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

  1 3 
  

以上隐含假设如果A是B的朋友，则B是A的朋友 - 如果您可能不想将UserIDLink1和UserIDLink2重命名为UserID和FriendID或类似的情况 - 在这种情况下，您也可以将记录加倍。

另外对于双向配置（如果B是A的朋友，A是B的朋友），您应该在（UserIDLink1，UserIDLink2）和（UserIDLink2，UserIDLink1）的Friends表中设置索引，以确保访问始终是有效的如果我们正在搜索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.

这篇关于MySQL的朋友表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！