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

Question

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

Answer 1

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

Answer 2

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.

Answer 3

UserRelationship
====
RelatingUserID
RelatedUserID
Type[friend, block, etc]

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

Hi I have a Semester project of SOCIAL NETWORKING WEBSITE, and I've planned to design a dynamic but secure database schema for it.

USERS table will have a record of all the registered users and will also insert a unique id for each user.

According to this id for each individual user I will CREATE 2 tables :

1. ID_friends : this will have the list of all the friends having foreign key to each user.
2. ID_scraps : this will have the list of the scraps submitted by the friends to the user.

For more information please have a visit to my subDomain : http://satyendra070091.freetzi.com

Regards.... Satyendra Kumar Sharma BTech (III) CS SRMSCET, Bareilly