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

database-design social-networking
Zachary Yates picture Zachary Yates · Dec 18, 2008 · Viewed 58.7k times · Source

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

chaos picture chaos · Dec 18, 2008 
UserRelationship
====
RelatingUserID
RelatedUserID
Type[friend, block, etc]

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

Related questions

Database design for a social networking site

What are the Tables that would be present in a social networking site (ex: Twitter). I have a users table as of now. How to keep track of followers and people I do follow? Should I maintain a separate table …

Read More
Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Read More
Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should …

Read More