如何在数据库建模中表示对称关系

Question

如果表中存在对称关系，如何以优雅的方式表示?例如，有一个名为Friend的表，其中应该包含两个用户的用户ID。如果我们在这个表中使用UID1和UID2，当我们想要找出A_uid和B_uid是否是朋友时，我们应该使用

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

，因为UID1和UID2在代表友谊方面是相同的。这个困境中最重要的是UID1和UID2是对称的

我认为这很难看，想知道是否有更好的方法来处理它。

谢谢!

Answer 1

我的一个想法是:如果您的UID类型服从于总排序，您可以这样做:

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

那么你的查询将不得不把较低的一个放在前面。但是您可以使用存储过程来保护它:

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;

这当然必须转换为您选择的RDBMS。

现在你的查询看起来像这样:

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

，它们返回相同的结果

另一方面，你可以用过程来保护它，而不把CHECK约束放在那里，我只是认为这很方便，以防止你意外地插入相同的关系不止一次。