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:

Approach 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)
```

Approach 2:

```
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.

Thanks

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 `(LEAST(u1,u2), GREATEST(u1,u2))`

.

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.