A Simple Messaging System

Gmail, Yahoo Mail, Facebook, Twitter all have one thing in comon. They all have at their core a messaging system. Email is about messages. Facebook status updates are basically broadcast messages. So is Twitter tweet. So, I decided to think about what a simple messaging system would be like…

First, we need to define what a message is. A message is an exchange of information between two people. Well, in a broad sense, it can also be between machines. But, in this article, we will use the exchange of information between people.

Now, if we put them into a database table, we will have:

  • message_id <- auto-increment by system
  • message_origin <- usually refers to the id in a users table
  • message_destination <- usually refers to the id in a users table
  • message_content <- the information
  • message_timestamp <- the date and time the message was sent

Let us go through the process of sending and receiving a message:

New Message

User Alex send a message to User Britney containing the information “Hello Britney”.

Now, we are going to have to insert this message into the table as a record with the following data:

  • 1
  • Alex
  • Britney
  • Hello Britney
  • 11 December 2013 16.19

It is straightforward, no problem there.

Alex can also send another new message to other users. The message will be stored the same way as above.

In the same way, Britney can create a new message to Alex or other users. The message will also be stored the same way.

Reply Message

The next step is a reply to a previous message. In this case, Britney replies to Alex’s message with the following information ‘Hello Alex’.

Now, when we insert the message into the table :

  • 2
  • Britney
  • Alex
  • Hello Alex
  • 11 December 2013 16.25

Now, wait a minute. The data looks the same as a new message. How do we differentiate between a new message and a reply message ?

It is obvious that we need a new field in the database.

The first instinct is to add the field is_reply with a boolean. There is one problem with this. When there are two replies, how do we know the original message that the reply is meant for ?

So, it is better to create is_reply_to and fill it with the message_id of the original message. If it is a new message, we simply fill it with 0.

Again, there is a problem with this. Since it is cascading, how do we retrieve the entire conversation of messages ? Due to the cascading structure, it will be impossible to grab the conversation in one query. It is going to involve multiple queries, each depending on the last result.

Here we can either :

  1. Put just the first message_id in the fields is_reply_to instead of the previous message_id.
  2. Create a new field called original_message_id and fill it with the message_id of the first message. This way, we can do a single query to get all replied messages.

The advantage of method 2, is that it we essentially keep the order of the conversation without depending on the timestamp. Especially important if we update the timestamp when the message is edited later on.

Naturally, if we use method 1, we rely on timestamp to sort the order of the conversation.

With me so far ? Good. Now we have covered the basic of a simple messaging system. Our table structure now becomes:

  • message_id <- auto-increment by system
  • message_origin <- usually refers to the id in a users table
  • message_destination <- usually refers to the id in a users table
  • message_content <- the information
  • message_timestamp <- the date and time the message was sent
  • is_reply_to <- we put the first message_id here

Or, if you choose method 2:

  • message_id <- auto-increment by system
  • message_origin <- usually refers to the id in a users table
  • message_destination <- usually refers to the id in a users table
  • message_content <- the information
  • message_timestamp <- the date and time the message was sent
  • is_reply_to <- we put the previous message_id here
  • original_message_id <- we put the first message_id here

I am going to close this article right here. There are a lot of things we can add to this simple messaging system. But that is the topic of another article.

What do you think ? Do you have another method ? Please use the comment section below.