是否有存储的双重关系，一种优雅的方式（即用户1和用户2是朋友） [英] Is there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)

问题描述

我在两个不同的几幅作品本月碰到了同样的问题：

I've run into the same problem in two different pieces of work this month:

Version 1: User 1 & User 2 are friends
Version 2: Axis 1 & Axis 2 when graphed should have the quadrants colored...

问题是，我没有看到一个优雅的方式，使用RDBMS，存储和查询这些信息。

The problem is, I don't see an elegant way, using a RDBMS, to store and query this information.

有两个明显的方法：

方法1：

store the information twice (i.e. two db rows rows per relationship):
u1, u2, true 
u2, u1, true
u..n, u..i, true
u..i, u..n, true

have rules to always look for the inverse on updates: 
on read, no management needed
on create, create inverse
on delete, delete inverse
on update, update inverse

Advantage:    management logic is always the same.
Disadvantage: possibility of race conditions, extra storage (which is admittedly cheap, but feels wrong)

方法二：

store the information once (i.e. one db row per relationship)
u1, u2, true
u..n, u..i, true

have rules to check for corollaries:
on read, if u1, u2 fails, check for u2, u1 
on create u1, u2: check for u2, u1, if it doesn't exist, create u1, u2
on delete, no management needed
on update, optionally redo same check as create

Advantage: Only store once
Disadvantage: Management requires different set of cleanup depending on the operation

我不知道是否还有一个第三方法，使用函数f（x，y），其中F（X，Y）是唯一为每个X，Y组合，其中f沿着键行云（X，Y） === F（Y，X）

I'm wondering if there's a 3rd approach that goes along the lines of "key using f(x,y) where f(x,y) is unique for every x,y combination and where f(x,y) === f(y,x)"

我的直觉告诉我，应该是能够满足这些要求的按位运算的某种组合。像两列：

My gut tells me that there should be some combination of bitwise operations that can fulfill these requirements. Something like a two-column:

键1 = X＆放大器;＆安培; ÿ
键2 = X + Y

key1 = x && y key2 = x + y

我希望人们谁在数学系社会学系花更多的时间和更少的时间已经看到了这种可能性或不能证明，并能提供快速的[你白痴，]它很容易证明（IM）可能的，看到这个链接（对骂可选）

I'm hoping that people who spent more time in the math department, and less time in the sociology department have seen a proof of the possibility or impossibility of this and can provide a quick "[You moron,] its easily proven (im)possible, see this link" (name calling optional)

任何其他优雅的办法也非常欢迎。

Any other elegant approach would also be very welcome.

感谢

推荐答案

还有通过增加一个额外的限制使用第二个方法的一种方式。检查 U1＆LT; U2 ：

There is also a way to use the 2nd approach by adding an extra constraint. Check that u1 < u2:

CREATE TABLE User
( Name VARCHAR(10) NOT NULL
, PRIMARY KEY (Name)
) ;

CREATE TABLE MutualFriendship
( u1 VARCHAR(10) NOT NULL
, u2 VARCHAR(10) NOT NULL
, PRIMARY KEY (u1, u2)
, FOREIGN KEY (u1) 
    REFERENCES User(Name)
, FOREIGN KEY (u2) 
    REFERENCES User(Name)
, CHECK (u1 < u2) 
) ;

的规则来读取，创建，插入或更新将不得不使用（最低（U1，U2），GREATEST（U1，U2））。

这篇关于是否有存储的双重关系，一种优雅的方式（即用户1和用户2是朋友）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！