Scalability: Desiging a message table

Question

This question might be deadly simple but I wanted to make sure I am going through the correct path and not making any simple and irrevertible mistakes.

My project will be based on messaging and it won't be as less-used as a side tool like in Twitter or Facebook (not that they are not used but it's not the key option presented to users)

I was wondering will a simple table design like;

• from (id)
• to (id)
• content (text)
• created_at (date)
• isRead (boolean)

will suffice because, even though it is planned to be a small project, I do not believe that all the messages should be stored in one table and for each user when they want to view their messaging history, I shouldn't be scanning the entire table to do so. I am thinking about how scalable would this structure be and how would I benefit using a NoSQL DB like mongoDB.

Answer 1

You can go through the following embedded json document to implement your message collection and can ensure index on "mail_id" key to avoid scanning all documents. For your information here collection serve as table and each document serve as row of a table.

{
mail_id:string
sentMails:{
            [
                {
                sent_at: datetime,
                subject: string,
                sent_to: string,
                cc_to:[array of mailid strings],
                bcc_to:[array of mailid strings],
                attachments:[
                                {
                                attachment_name:string,
                                attachment_type:strng
                                }
                            ],
                sent_mail_content: string               
                }
            ]
            }

receivedMails:{
                [
                    {
                    received_at: datetime,  
                    subject: string,                
                    received_from: string,
                    cc_to:[array of mailid strings],
                    attachments:[
                                    {
                                    attachment_name:string,
                                    attachment_type:strng
                                    }
                                ],
                    received_mail_content: string
                    }
                ]   
            }
}

I hope it will help.

Answer 2

To speak to the MongoDB side of things here, there are a lot of ways to approach this, and a lot is going to depend on how you want this to scale, if you ever expect to hit document size limits etc. and that impacts what indexing you need, whether you should plan to shard, what shard key you should pick and so on. Hence, lots of permutations.

I could go through the most common approaches to this kind of thing with MongoDB, the pluses and minuses, but thankfully someone has already done that for me (with pictures!). Granted, it is for an inbox application, but the theory and issues essentially remain the same:

http://www.10gen.com/presentations/data-modeling-examples-real-world-0

Take a look through this presentation, it shows the implications of some of the decisions you have to make when coming up with the data model that fits your application.