Database Design for Messaging in Facebook

Question

I'm currently working on creating a messaging system similar to Facebook. More specifically, the private messages on Facebook-- complete with an Inbox, Sent Messages, "Unread" and "Read."

Is anyone familiar with a similar database structure to what Facebook currently uses for their messaging system?

Thanks!

Answer 1

I believe Facebook is using a completely custom system that does not use a traditional "Database" at all. That said, this guy did reverse engineer what a schema would look like: http://blogs.x2line.com/al/archive/2007/06/02/3124.aspx

It turns out they are using something called Casandra. You can go to the google code project directly as well (link is down for me). The short of it is that they use something like Google's BigTable and not MySQL.

Answer 2

Here is something you might find useful to start off :

Start with 2 tables, one that will contain the actual message, and one you will use to keep track of the relationship between messages

That could be something like this :

private_messages tbl:
id
date_sent
title
content
status ENUM ('unread', 'read') DEFAULT 'unread'

private_message_relation tbl:
id
message_id
sender_id
receiver_id

Then you may want to add constraints to the ids of the relation tbl for data integrity purposes.

Answer 3

As a correction to Joe's comment, Facebook uses Cassandra to power inbox search, not messaging. And that "reverse engineering" is of the Facebook platform's object classes, which I don't think gives a very representative look at the database design at all.