Database design: Best table structure for capturing the User/Friend relationship?

Zachary_Yates_8360 0
Viewed 68637

I'm trying to design a data model that denotes one user being the friend of another user. This is what i've come up with so far, but it seems clunky, is there a better solution?

User
=====
Id
Name
etc...

UserFriend
===========
UserId
FriendId
IsMutual
IsBlocked
social-networking database-design
13 Answers
UserRelationship
====
RelatingUserID
RelatedUserID
Type[friend, block, etc]

Agree that mutuality doesn't belong as a column; breaks normalization.

user_475290

I am currently building a social networking site for a client and I expressed things this way

CREATE TABLE [dbo].[PersonFriend] (
    [Id]                          INT            IDENTITY (1, 1) NOT NULL,
    [Timestamp]                   DATETIME       NOT NULL,
    [ChangeUser]                  NVARCHAR (200) NOT NULL,
    [FriendStatusId]              TINYINT        NOT NULL,
    [Person1Id]                   INT            NOT NULL,
    [Person2Id]                   INT            NOT NULL,
    [Person1RequestTimestamp]     DATETIME       NOT NULL,
    [Person2AcknowledgeTimestamp] DATETIME       NULL
);

Each person is stored in the Person table (imagine that). The Person1Id and Person2Id fields are FK to the person table. I keep a status list in the FriendStatus table for covering whether something has been request, accepted, denied, ignored etc. The Timestamp field is standard in my design to indicate record creation (it is a pattern thing that is used in by base persistence class) and its kind of duplicated in this table as the Person1RequestTimestamp contains the same data. I also capture when the Person2 saw the request and made an action (which gets indicated in FriendStatusId) on it and store that in the Person2AcknowledgeTimestamp).

One of the core assumptions of this design can be stated that Person1 requested friendship of Person2 - if that friendship is accepted then the friendship is considered mutual.

user_407140

I'd do something similar to what you have, but remove the "IsMutual" flag. Simply add a second row with inverse values when it is mutual. It does add rows, but feels a lot cleaner.

Jeffrey_32590

Perhaps add a Relationship table, put the relationship properties there, and reference it from UserFriend.

user_139690

Probably over the top but one can use the semantic web to model this. One can use the FOAF (FOAF Friend of a Friend)-format.

tuinstoel_439010

Friendships are less clear cut than the classic employer/boss and user/spouse self-join scenarios. Is friendship a relationship or an activity? I've received a fair amount of criticism for neglecting the latter. Either way, you're probably going to need more than one table, no matter how generic your data model is.

acoustickitty_475560

Do you actually need a physical table to discover if there are mutual friends? Why not do a SQL query like:

SELECT U.ID, U.NAME FROM USER U
INNER JOIN USERFRIEND UF
ON U.ID = UF.FRIENDID
WHERE U.ID = (SELECT USER.ID FROM USER
            WHERE USER.ID = friend_id AND USER.ID != your_id);

The query's results should return all of the mutual friends.

Admin1

From my understanding Friendship is a result of a relation between two users (User1 and User2), and because a user1 can have 0 or many user(s) as friend(s) and vice-versa user2, therefore a junction table "Friend" commes in the middle to represent that relationship like this :

User1 id(int) username(string)

User2 id(int) username(string)

Friend ---- id(int) user1_Id(int) user2_Id(int) active(bool)

Both user1_Id and user2_Id are FKs in Frind Table

I hope i am right.

user_10313220
Related