开发者

Social Network Database Design - Friend/Block Relationships

I'm working on a social networking site and need users to be able to friend each other and/or block each other. The way I see it, 2 users can either be Friend, Pending, Block, or 开发者_如何学JAVANULL. I'd like to have a single view that shows a single row for each confirmed relationship. My view properly shows the relationship but I had to do a workaround to only show 1 row/relationship without unioning the table with itself and swapping the order or Requestor and Requestee.

Anybody have any ideas about how to clean this up?

Thanks, - Greg

Relationship Table:

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

vwRelationship View:

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

Example Query:

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

Scenario:

  1. User 1 and User 2 don't know each other | Relationship Type = NULL
  2. User 1 friends User 2 | Relationship Type = Pending
  3. User 2 accepts | Relationship Type = Friend
  4. a month later User 2 blocks User 1 | Relationship Type = Block


Here's what I ended up using:

Table - Relationship
RelationshipID, RelationshipTypeID, CreatedByUserID, CreatedTimestamp

Table - RelationshipType
RelationshipTypeID, RelationshipTypeName

Table - UserRelationship
UserID, RelationshipID, IsPending

Anybody think of anything better?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜