Is there an elegant way to store a dual relationship (i.e. user 1 and user 2 are friends)
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) ) ;The rules to read, create, insert or update will have to use the (LEAST(u1,u2), GREATEST(u1,u2)).
In SQL it's easy to implement the constraints to support your 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 anyone that's interested, I played around with a few bitwise operations and found that the following seems to fulfill the criteria for f(x,y):
#Python, returns 3 tupledef get_hash(x, y): return (x & y, x | y, x * y)I can't prove it, though.