Facebook messaging database design

Question

I am currently working on a messaging system similar to Facebook. More specifically, private messages on Facebook are bundled with Inbox, Sent Messages, Unread, and Read.

Anyone familiar with this database structure with what Facebook is currently using for its messaging system?

Thanks!

Answer 1

Here's what you might find useful to get started:

Start with two tables that will contain the actual message and which you will use to track the relationship between messages

:

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

tbl .

Answer 2

, Facebook Cassandra Inbox-, . " " Facebook, , , .

Answer 3

, Facebook , " ". , , : http://blogs.x2line.com/al/archive/2007/06/02/3124.aspx

, -, Casandra. google ( ). , - Google BigTable, MySQL.

EDIT: . .

Answer 4

, , . , .

Message {
  MessageId,
  SenderId, -- Foreign key User.UserId  
  DateSent,
  Subject, 
  Content,
  Attachment, -- can be null or default to a 0
  ...
}

UserMessage {
  Id, 
  MessageId, -- Foreign key Message.MessagId
  RecepientId, -- Foreign key User.UserId
  DateRead -- can default to year 1900 if you do not want to deal with nulls
}

User {
  UserId
  UserName
  ...
}

Queries    

Read = UserMessage where DateRead > year 1900 (or not equal to null)
Inbox = UserMessage where RecepientId = Me.UserId
Sent = Message where SenderId = Me.UserId
Conversation = Group by Subject
Attachment = (Simple = Path to attachment file. || Better = DocumentId ... see below)

Attachment

Document {
  int DocumentId,
  int DocTypeId,
  virtual DocumentType DocumentType,
  string FileName,
  int UserId,
  string mimeType,
  float fileSize,
  string storagePath,
  int OrganizationId,
  string fileHash,
  string ipAddress,
  DateTime DateCreated = DateTime.Now;
}