CODEWITHSUNDEEP
mysqldatabasedatabase-designdata-modeling
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

Data Model for Social Network?

If I wanted to create a site that allowed users to have 0 or more "friends", how would I model such a relationship in a database? Would something this simple work:

Table Friends
- Id (PK)
- UserId (FK)
- FriendId (FK)

???

Would this allow me to later on do things like Facebook does (e.g. "3 of your friends knows this user, maybe you do too")? Or something like 6-degrees-to-Kevin-Bacon?

EDIT 1:

Table Friends
- UserId (FK)
- FriendId (FK)
- Status ('Pending', 'Approved', 'Rejected', 'Blocked'?)

Upvotes: 8

Views: 3995

Answers (3)

dustypants
dustypants

Reputation: 1

@devfreak is absolutely right, but I would not make a "Pending" table. It's simply redundant. The friend table can have a status field, and you can query against it based on status.

Upvotes: 0

devfreak
devfreak

Reputation: 1231

You need many to many relationship - you can have 0 or more friends, every friend can have 0 or more friends. The most common approach is to bind both users in the additional table. You need just an additional DB table: create table Relationships( user1 int not null references Users(id), user2 int not null references Users(id) ); You definitely want to create indexes for user1 and user2.

I think you don't need the ID column. One more thing you should be aware of the thing that if I'm your friend, you are my friend to. When you insert ([u1],[u2]) into Relationships table check first if there is relationship ([u1],[u2]) or ([u1],[u2]). If there is such relationship don't insert another one, this could break your logic.

If you need some sort of confirmation like in most popular social networks you should make another table PendingRelationsihps which will have the same DB scheme as the Relationship one. After confirmation you will move the entry from pendingrelationships to relationsships.

Hope this will help you.

Upvotes: 1

Ashwini Dhekane
Ashwini Dhekane

Reputation: 2290

This will work. Following are points to be noted:

  • Do you have something like friend confirmation. If yes, you will have to think on how to store 'pending'
  • Indexing both UserId and FriendId. These are the values on which you will be joining the tables.
  • The unordered pair (UserId, FriendId) is contender for Primary key.
  • Suppose Uid_1 and Fid_1 are friends where Uid_1 != Fid_1 then does your Friends Table store (Fid_1, Uid_1) as well as (Uid_1, Fid_1).
  • How far in degrees of relationship are you going to search.

Everytime you have to query for DOR(Degree of relationship) you will have to initialize a graph and run Shortest Path Algo (This is the least optimization I can think of). If your member-count rises to some kilos then how are you going to handle this?

Upvotes: 2

Related Questions