How to represent Symmetry relationships in database modeling

Question

If there is a symmetry relationship in a table, how to represent it in a elegant way? For example, there is a table called Friend, in which should contain user ID of two users. If we use UID1 and UID2 in this table, when we want to find out if A_uid and B_uid are friends, we should use

SELECT * FROM Friend WHERE (UID1 = A_uid AND UID2 = B_uid) OR (UID1 = B_uid AND UID2 = A_uid);

since UID1 and UID2 are the same in representing a friendship. And the most important in this dilemma is that UID1 and UID2 are symmetry.

I think this is ugly and want to know if there is a better way to do with it.

Thanks!

Answer 1

An idea off the top of my head: if your UID types are subject to a total ordering, you can do something like this:

CREATE TABLE friends (
  uid1 uid REFERENCES users,
  uid2 uid REFERENCES users,
  PRIMARY KEY (uid1, uid2),
  CONSTRAINT uid1_above_uid2 CHECK(uid1 < uid2)
);

Then your queries will have to put the lower one in first. But you can protect that with a stored procedure that ensures that you do that:

CREATE FUNCTION friends_with(uid, uid) RETURNS SETOF friends AS $$
  SELECT * FROM friends WHERE uid1 = LESSER($1, $2) AND uid2 = GREATER($1, $2)
$$ LANGUAGE SQL;

That will of course have to be translated to your RDBMS of choice.

Now your queries look like this:

SELECT * FROM friends_with(5001, 2393);
SELECT * FROM friends_with(2393, 5001);

and they return the same results.

On the other hand, you could protect it with procedures without putting the CHECK constraint on there, I just think it's handy to prevent you from accidentally inserting the same relation more than once.