Php – Database Relationship or friend table design for a social networking site

databasedatabase-designMySQLPHP

Hello Im in the midst of creating a social networking site and I would like to know how I would go about creating the relationships between users. Many sites say that I should create a relationship/friend table, but Im looking into the future and believe that this would be ineffective. This idea could be as popular as facebook and I want to be ready for that many users. Facebook has 400 million users so a friends table would be at least 150 times that. Doing a query for ones friends would be very slow I would think. So would the solution be a seperate table for each user containing their friends ID's. or an associated CSV file containing the ID's. Any help would be greatly appreciated to the design of my site. Thanks

Best Answer

Build the schema you need today, not the one you think you'll need 5 years from now.

Do you think facebook designed their schema to support 400 million users on day one? Of course not. Building for that kind of scale is complicated, expensive, and honestly, if you try it now, you'll probably get it wrong and have to redo it later anyway.

And let's be honest: you have a better chance of winning the lottery than hitting 400 million users any time soon. Even if you do, your project will have hundreds of engineers by then -- plenty of bandwidth for redesigning your schema.

Now's the time to build simple.

Edit to add some solid examples:

Youtube:

They went through a common evolution: single server, went to a single master with multiple read slaves, then partitioned the database, and then settled on a sharding approach.

Keep it simple! Simplicity allows you to rearchitect more quickly so you can respond to problems. It's true that nobody really knows what simplicity is, but if you aren't afraid to make changes then that's a good sign simplicity is happening.

Livejournal also grew from a single database on a single server to multiple sharded replicated databases

I'm sure you could find a dozen more examples on the highscalability blog

Related Solutions

Sql – Facebook database design

Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table.

Somewhat useful example:

Table Name: User
Columns:
    UserID PK
    EmailAddress
    Password
    Gender
    DOB
    Location

TableName: Friends
Columns:
    UserID PK FK
    FriendID PK FK
    (This table features a composite primary key made up of the two foreign 
     keys, both pointing back to the user table. One ID will point to the
     logged in user, the other ID will point to the individual friend
     of that user)

Example Usage:

Table User
--------------
UserID EmailAddress Password Gender DOB      Location
------------------------------------------------------
1      bob@bob.com  bobbie   M      1/1/2009 New York City
2      jon@jon.com  jonathan M      2/2/2008 Los Angeles
3      joe@joe.com  joseph   M      1/2/2007 Pittsburgh

Table Friends
---------------
UserID FriendID
----------------
1      2
1      3
2      3

This will show that Bob is friends with both Jon and Joe and that Jon is also friends with Joe. In this example we will assume that friendship is always two ways, so you would not need a row in the table such as (2,1) or (3,2) because they are already represented in the other direction. For examples where friendship or other relations aren't explicitly two way, you would need to also have those rows to indicate the two-way relationship.

Database design for a social networking site

Try having a look at Database Answers in particular the data models. They have several different designs for various systems. This one is for a social networking site which may give you an idea of what's required.

You may want to search on SO for other social network database questions. I found this one that had a link to flickr showing a schema which appears to be from Facebook.

Your database design will be based around your system requirements. Without knowing exactly what you are trying to achieve, it is difficult to give you the best design.

Best Answer

Related Solutions

Sql – Facebook database design

Database design for a social networking site

Related Topic