Using PHP and MySQL, how to structure a "Friendship" in database for a community website?

Question

For community websites like Facebook, myspace, etc.. They have a feature called "Friendship", means you can add friends, send "add friends request", etc..

How to structure it in database? I means, what tables required? What are the columns for each table required?

Answer 1

table Person

table Friendship with 2 FK's to Person

this is just common Database normalization

Answer 2

id_inviter // FK to the inviting user
id_friend  // FK to the invited user
status     // 0 = not accepted, 1 = accepted
updated_at // datetime, last status change
created_at // datetime, initial friendship request

Answer 3

You need a table storing your users with their own unique id then a "friends" table storing which users are friends using the unique ids given to them in the users table.

This article will help you understand how to structure your tables

Answer 4

First you need a table for the users, each user will have a userID which will be the primary key.

Then you need a table that describes relationships between users. The mimimal solution would be a table with only two columns (each an FK userID).

But most probably you need more information about the nature of these relationships. Who has contacted whom? When? Has the other end accepted? When?

You can add all this information to the relationship table, if you query for all relationships a person has, you can easily check for a certain subgroup of relationships like 'confirmed relationships' or 'open requests' etc.

Answer 5

One way to do this would be to have

Table user -- contains user details, primary key user_id
Table friends -- contains user_id, friend_user_id, along with friendship status/type

Answer 6

This is how I have done in it in the past, before facebook was even online....

Tables tbl_users, tbl_users_friendship

Table Definition

tbl_users

intID(Primary Key) and other fields.....

tbl_users_friendship

intID(Primary Key for the table)

intUserID(foreign Key(tbl_users->intID))they person who initiated the friendship

intFriendID(foreign Key(tbl_users->intID)) the person who was inivited to the friendship

intStatus (this flag is used to verify the status of the friend ship)active,disabled,suspended....etc

Answer 7

Yep, two tables, one with the users and one like this: friends(user_id, friend_id) both foreign key of friends is good. But indexing this table, or using a foreign key (like in my example) slows down the database on inserting new values. I mean, after 2 million friendships having each user an average of 50 friendships, the entire table is collapsing after only 40k users. That is really the problem. How to solve it?

Thank you

Answer 8

This shouldn't be a database question, it should be a user experience question.

What kind of friendships would support the site's intent? Does level of intimacy matter? Is it just friend-to-friend, or friend-to-peergroup-friend? How does privacy come into play?

The scalability issue comes in at some point. For example, Facebook does these intense friend-friend comparisons to find "common friends" between you and pretty much anybody else, when you visit their page. The response has to be subsecond -- and each user may have hundreds or thousands of friends. That takes major caching and specialty programming.

You probably won't have that problem for a while. For you, the database design should be simple, if you can figure out what the needs of the experience are.

Answer 9

The big problem, I think, is to optimize the resulting queries, because you don't know at the first place, only with the id, if that id corresponds to the inviter or the invited person to the relationship, then you have to do so many comparisons and queries, and it's no clear how to deal with indexes in this situation. You have to request all friends of A=id, and all friends of B=id, and then query for the user table to get all fields. The question is not so simple, because then you have to know if a has requested friendship to b, and if b has requested friendship to a, etc...