I have encounter exactly the same condition in two different bits 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...
The issue is, I do not see a stylish way, utilizing a RDBMS, to keep and query these details.
You will find two apparent approaches:
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
I am wondering if there is a 3rd approach that goes like "key using f(x,y) where f(x,y) is exclusive for each x,y combination and where f(x,y) === f(y,x)"
My stomach informs me that there must be some mixture of bitwise procedures that may fulfill these needs. Something similar to a 2-column:
key1 = x &lifier&lifier y key2 = x + y
I am wishing that individuals who spent additional time within the math department, and fewer amount of time in the sociology department have experienced an evidence from the possibility or futility of this and may give a quick "[You moron,] its easily proven (im)possible, check this out link" (title calling optional)
Every other elegant approach would be also very welcome.
There's also a method to make use of the second approach with the addition of an additional constraint. Make sure 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) ) ;
The guidelines to see, create, place or update will need to make use of the
In SQL it's not hard to implement the restrictions to aid the first approach:
CREATE TABLE MutualFriendship (u1 VARCHAR(10) NOT NULL, u2 VARCHAR(10) NOT NULL, PRIMARY KEY (u1,u2), FOREIGN KEY (u2,u1) REFERENCES MutualFriendship (u1,u2)); INSERT INTO MutualFriendship VALUES ('Alice','Bob'), ('Bob','Alice');
For anybody that's interested, I performed around having a couple of bitwise procedures and located the following appears to satisfy the factors for f(x,y):
#Python, returns 3 tuple def get_hash(x, y): return (x & y, x | y, x * y)
I can not prove it, though.
"x is really a friend of y".
Define a table of (x,y) pairs and enforce a canonical form, e.g. x<y. This can make sure that you cannot have both (p,q) and (q,p) inside your database, as a result it will make sure "store once".
Produce a view as Choose x,y FROM Buddies UNION Choose x as y, y as x FROM Buddies.
Do your updates from the base table (downside : updaters should be aware the enforced canonical form), do your queries from the view.
You appear to limit the amount of buddies to at least one. If this sounds like the situation i quickly would use something similar to u1,u2 u2,u1 u3,null u4,u5 u5,u4
u3 doesn't have a buddy.