社会网络数据库设计-朋友/块关系

Question

我在一个社交网站工作，需要用户能够互相加朋友和/或阻止对方。在我看来，2个用户可以是Friend、Pending、Block或NULL。我希望有一个视图，为每个已确认的关系显示单行。我的视图正确地显示了关系，但是我必须做一个解决方案，只显示1行/关系，而不将表与本身合并并交换顺序或Requestor和Requestee。

有谁知道怎么清理这个吗?

谢谢,——格雷格

关系表:

Requestor (int) | Requestee (int) | ApprovedTimestamp (smalldatetime) | IsBlock (bit)

vwRelationship视图:

SELECT DISTINCT 
                      CASE WHEN f.Requestor < f.Requestee THEN f.Requestor ELSE f.Requestee END AS UserA, 
                      CASE WHEN f.Requestor < f.Requestee THEN f.Requestee ELSE f.Requestor END AS UserB, CASE WHEN b.Requestor IS NULL AND b.Requestee IS NULL 
                      THEN CASE WHEN f.AcceptedTimestamp IS NULL THEN 'Pending' ELSE 'Friend' END ELSE 'Block' END AS Type
FROM         dbo.Relationship AS f LEFT OUTER JOIN
                          (SELECT     Requestor, Requestee
                            FROM          dbo.Relationship
                            WHERE      (IsBlock = 1)) AS b ON f.Requestor = b.Requestor AND f.Requestee = b.Requestee OR f.Requestor = b.Requestee AND f.Requestee = b.Requestor

示例查询:

Select Type From vwRelationship Where (UserA = 1 AND UserB = 2) OR (UserA = 2 AND UserB = 1)

场景:

1. 用户1和用户2互不认识|关系类型= NULL
2. 用户1好友用户2 |关系类型= 待处理
3. 用户2接受|关系类型= 好友
4. 一个月后用户2阻塞用户1 |关系类型= 阻塞

Answer 1

我最后用的是:

Table - Relationship
RelationshipID, RelationshipTypeID, CreatedByUserID, CreatedTimestamp
Table - RelationshipType
RelationshipTypeID, RelationshipTypeName
Table - UserRelationship
UserID, RelationshipID, IsPending

有人想到更好的吗?