Mysql structure for something like Facebook

Question

I'm thinking of starting a facebook style website with bio pages, friend requests etc, roughly what sort of database tables would be advisable?

Ie. Users, status?

A rough list would help?

Thanks

Answer 1

If/when you site grows to millions or records, you will find that mysql is your worst enemy. Facebook has already been there, they started with Mysql then switch to Cassandra. If you want to write your Facebook, then do a research to see how/what facebook is using. They are actually pretty open about it, they open source Cassandra, which Twitter now uses also.

My advice will be to NOT use MySQL at all, it's a ticking time bomb. What I mean is it starts off wonderfully, fast, happy times. Then as your database grows in size you will find youself in never-ending optimization battle against MySQL, the battle you can't win.

Answer 2

Everything about FB is people so you would definitely need a Person table with self-referencing many-to-many relationship. You would really want to do this with PHP ORM such as doctrine, and it will greatly simplify your life.

Person: id: int, name: string

RelatedPerson: person_id: int, related_person_id: int, relationship_id: int

Relationship: id: int, name: string

There are many more tables, but sorry I can't list them all. A piece of advise is that everything should be around people. What exact thing you are planning to do?

Answer 3

I'm going to have to agree with Dmitri on this one. When mysql databases reach a certain size it can get VERY hard to make it run as fast and smooth as Facebook (sometimes) does.

As to your question, you're going to need at least users, users-friends, status, friend-requests and so on. I'd recommend starting out small.