在关系数据库中存储1：1用户关系的最佳方法

What is the best way to store user relationships, e.g. friendships, that must be bidirectional (you're my friend, thus I'm your friend) in a rel. database, e.g. MYSql?

存储用户关系的最佳方式是什么，例如友谊，必须是双向的（你是我的朋友，因此我是你的朋友）。数据库，例如MySQL的？

I can think of two ways:

我可以想到两种方式：

Everytime a user friends another user, I'd add two rows to a database, row A consisting of the user id of the innitiating user followed by the UID of the accepting user in the next column. Row B would be the reverse.
每当用户与另一个用户交朋友时，我就会向数据库中添加两行，第一行包含启动用户的用户ID，后跟下一列中接受用户的UID。 B排是相反的。
You'd only add one row, UID(initiating user) followed by UID(accepting user); and then just search through both columns when trying to figure out whether user 1 is a friend of user 2.
你只需要添加一行，UID（发起用户），然后是UID（接受用户）;然后在尝试确定用户1是否是用户2的朋友时仅搜索两列。

Surely there is something better?

肯定有更好的东西？

4 个解决方案

#1

I would have a link table for friends, or whatever, with 2 columns both being PK's, and both being FK's to the User table.

我会有一个朋友或者其他的链接表，其中2列都是PK，而且都是用户表的FK。

Both columns would be the UID, and you would have two rows per friend relationship (A,B and B,A). As long as both columns are PK's, it should still be in normal format (although others are free to correct me on this)

两列都是UID，每个朋友关系会有两行（A，B和B，A）。只要两列都是PK，它应该仍然是正常格式（尽管其他人可以*纠正我）

Its a little more complex of a query, but nothing that can't be abstracted away by a stored procedure or some business logic, and its in Normal Format, which is usually nice to have.

它的查询稍微复杂一点，但是没有任何东西不能被存储过程或某些业务逻辑抽象出来，而且它的标准格式通常很好。

#2

You could check which of the two user_id's is the lowest and store them in a specific order. This way you don't need double rows for one friendship and still keep your queries simple.

您可以检查两个user_id中哪一个最低，并按特定顺序存储它们。这样，您不需要为一个友谊提供双行，并且仍然可以简化查询。

user_id_low | user_id_high

a simple query to check if you're already friends with someone would be:

一个简单的查询，以检查您是否已经是某人的朋友将是：

<?php
$my_id = 2999;
$friend_id = 500;

$lowest = min($my_id, $friend_id);
$highest= max($my_id, $friend_id);

query("SELECT * FROM friends WHERE user_id_low=$lowest AND user_id_high=$highest");
?>

Or you could find the lowest/higest userid using mysql

或者你可以使用mysql找到最低/最高的用户ID

<?php
query("SELECT * FROM friends WHERE user_id_low=LEAST($my_id, $friend_id) AND user_id_high=GREATEST($my_id, $friend_id)");
?>

And to get all your friends id's

并获得所有朋友的身份

<?php

query("SELECT IF(user_id_low=$my_id,user_id_high,user_id_low) AS friend_id FROM friends WHERE $my_id IN (user_id_low, user_id_high)");

?>

#3

Using double rows, while it creates extra data, will greatly simplify your queries and allow you to index smartly. I also remember seeing info on Twitter's custom MySQL solution wherein they used an additional field (friend #, basically) to do automatic limiting and paging. It seems pretty smooth: https://blog.twitter.com/2010/introducing-flockdb

使用双行，在创建额外数据的同时，将大大简化您的查询并允许您进行智能索引。我还记得在Twitter的自定义MySQL解决方案上看到信息，其中他们使用了一个额外的字段（朋友＃，基本上）来进行自动限制和分页。看起来很顺利：https：//blog.twitter.com/2010/introducing-flockdb

#4

-6

Use a key value store, such as Cassandra for example.

使用键值存储，例如Cassandra。

#1