Database normalization for facebook-like messaging system

Question

There are several discussions about messaging systems but mostly related to email structure. How can be the most efficient way for members messaging in a normalized database?

I am thinking of creating a messages table with five columns:

ID (PRIMARY KEY)
First_Person (FK user_id)
Second_Person (FK user_id)
Message
date

My worry is about reading this large table.

finding all message for a person (e.g. user_id 876)

SELECT * FROM messages WHERE First_Person='876' OR Second_Person='876'

and communications between two persons

SELECT * FROM messages WHERE (First_Person='876' OR Second_Person='876') 
AND (First_Person='1500' OR Second_Person='1500') ORDER DESC BY date

Since this kind of messaging is like chat, for thousands of members, this table can grow to billions of row (not millions). Then, it is efficient to search for messages in such a large table?

So where is the text of the message stored? In another table? — Hammerite, Oct 23 '11 at 20:36
Oh. I read it as "Message date", because at the time it hadn't been edited to put them in a code block, and they were all on one line and weren't comma-separated. Also you say it has four columns, but it apparently has 5. — Hammerite, Oct 23 '11 at 20:45

score 0 · Accepted Answer · answered Oct 23 '11 at 20:48

0

You're right, such big table is not usable. If you need a real messages keeping system better look at NoSQL solutions (like HBase, Cassandra, MongoDB etc) just you'll have to forget anything you know about relational databases.

With MySQL you still however may do something scalable if split the table into very small pieces. Make one table keep messages of max 1k users (you will need to write all messages twice unless both users are from same table). Plus keep no more than 1k tables in one DB, auto-create another one when this limit is reached. Having several databases (even on one physical server) will make it easy for DBA to transfer each to a new server when the current one becomes overloaded. To get messages of a certain user your code will have to obtain the required DB/table from a map you will have.

answered Oct 23 '11 at 20:48

Denis Chmel

780
6
11

Thanks for suggestive reply. Since all other data are on mysql, it is not efficient to run another server (e.g. MongoDB) in parallel. I understand what you mean by splitting the row to several tables; but I did not get what is the usefulness of several databases. Each table is saved as a different file; thus, retrieving data from one table is not affected by the presence of other tables. Why creating several databases? – Googlebot Oct 23 '11 at 21:03
Splitting (partitioning) a table into several makes each one small thus easily searchable, indexable, alterable. – Denis Chmel Oct 23 '11 at 21:13
Keeping them all in one DB will work until your disk/cpu are capable of doing read/write on small traffic, but on gaining popularity you'll have to add another server. And it's natural to move a whole DB, plus safe. It's not a big deal to connect to a variable host in code, you'll get a horizontal scaling support in exchange. – Denis Chmel Oct 23 '11 at 21:23

Database normalization for facebook-like messaging system

1 Answers1

Linked