在关系型数据库中存储1:1用户关系的最佳方式[英] best way to store 1:1 user relationships in relational database

本文是小编为大家收集整理的关于在关系型数据库中存储1:1用户关系的最佳方式的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

存储用户关系的最佳方法是什么,例如友谊,这一定是双向的(您是我的朋友,所以我是您的朋友).数据库,例如mysql?

我可以想到两种方法:

  1. 每次用户朋友另一个用户,我都会在数据库中添加两行,该行由Innitiating用户的用户ID组成,然后在下一列中接受了接受用户的UID.第B将是反向.
  2. 您只会添加一行,UID(启动用户),然后是UID(接受用户);然后,在尝试找出用户1是否是用户2的朋友时,只需搜索这两个列.

肯定有更好的东西?

推荐答案

我将有一个适合朋友或其他的链接表,其中有2列是PK的链接表,并且在用户表上都是FK.

这两个列都是UID,每个朋友关系将有两行(A,B和B,A).只要两列都是PK的

它的查询更为复杂,但是没有用存储过程或某些业务逻辑来抽象的,并且它以正常格式,通常很不错.

其他推荐答案

您可以检查两个用户_ID中的哪一个是最低的,并以特定顺序存储它们.这样,您就不需要双行才能建立一条友谊,并且仍然使您的查询保持简单.

user_id_low | user_id_high

一个简单的查询,可以检查您是否已经与某人成为朋友:

<?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");
?>

,或者您可以使用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)");
?>

并获得所有朋友ID

<?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)");

?>

其他推荐答案

使用双行,虽然它创建额外的数据,但可以大大简化您的查询,并允许您巧妙地索引.我还记得在Twitter的自定义MySQL解决方案上看到信息,其中他们使用了一个其他字段(基本上是朋友#)进行自动限制和分页.看起来很顺利: https://blog.twitter.com/2010/introducing-focking-flockdb >

本文地址:https://www.itbaoku.cn/post/597517.html

问题描述

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?

I can think of two ways:

  1. 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.
  2. 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.

Surely there is something better?

推荐答案

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.

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)

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.

其他推荐答案

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_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

<?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)");

?>

其他推荐答案

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