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:
- User 1 and User 2 don't know each other | Relationship Type = NULL
- User 1 friends User 2 | Relationship Type = Pending
- User 2 accepts | Relationship Type = Friend
- 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?
精彩评论