Problem
A basic messaging system should be able to manage messages, senders and recipients, folders that contain the messages, and email notifications. In our case, we are going to try and keep things simple where it makes sense to do so, but in one area, we will do things in a more complicated way simply because it will result in less wear and tear on the overall system. This is how the messages will be delivered to the users.
Rather than following a standard email messaging system where each person gets a physical copy of a message, we are going to build our system in the same way that the MS Exchange server works. We are going to make one copy of a message and subscribe users to that message. So rather than have 50 messages for 50 recipients, we will have one message and 50 recipient subscriptions.
The next problem lies in building a WYSIWYG (what you see is what you get) messaging editor. For this feature, there are many open source WYSIWYG editors; we will use one of those to save us a bit of time. We will be using one of the popular editors—X INHA. This editor can be downloaded for free here at http://xinha.webfactional.com/. You may have seen this editor already as it is widely used across many popular community sites.
Design
Let’s take a look at the design of these features.
Messages
Messages are the core of any messaging system. Generally, a message would contain details like the sender of the message, receiver of the message, and other metadata like time sent, server from where it was sent, etc. and the message, subject, and body. In our case, the message will contain the sender, subject, body, and the data sent. It will also contain one additional field, i.e. the type of message (message, friend request, and so on).
We will need to create a page that allows a user to compose a new message (as seen in the image at the start of this article). This interface should also allow a user to add his/her friends easily rather than force them to remember everyone. Also, this interface should allow a user to quickly snap together some HTML without ever having to look at HTML. This can be accomplished with a WYSIWYG editor.
Recipients
As we have already discussed that we are going to move some of the complexity away from the message, following a subscription model instead, you will find that most of the complexity of this system lies around the recipient concepts.
In this case, the recipient subscription is what will be contained in a folder and will have a read status. With this design, we will remove some of the burden from the database. The overhead of doing this of course means that we now need to manage our data closely, as it is kept in many pieces.
A more simple design that would result in more copies of data to be managed would be to create one message for each recipient. This is easier as each message can easily be deleted and moved around without having to worry about the copies of that message of the other recipients. Having said that, if the message is quite large, and more importantly if we were to allow file attachments, all the copies of the messages would be identical for each recipient. This would quickly bloat your database!
Solution
Now let’s take a look at our solution.
Implementing the database
First let’s take a look at what tables are needed:
Messages
A message will primarily be made up of the subject and its body. In addition to that we will need to know what type of message we are sending so that we can do some more fancy things in the UI down the road. In addition to this, we are going to maintain who owns/created the message at this level.
There aren’t really any major complexities to note here other than the fact that the Body is made up of a varchar(MAX) data type. If you feel this is too large for your system, feel free to make it anything you are comfortable with. The value you eventually use will drive the message for your system.
MessageTypes
Message Types allows us to assign a type to our messages. This is purely a lookup table that will allow us to know what the types are during queries. We will keep a list of enums in the code to make the lookups easier from that end.
MessageRecipients
A message recipient is simply the receiving party to the message. But as we try to minimize the data that we manage in our system, the message recipient is also a very important part of the message. In our case, it is the receiving party as well as all the things that the receiving party does with their subscription of that message. We will use this subscription to denote which folder the receiver is keeping the message in, and whether the receiver has read the message or not. Also, if the receiver chooses to delete the message, he/she can just delete the subscription to a message (unless they are the last subscription, in which case we will delete the message as well).
The SQL for this subscription is actually quite straightforward. It tracks a relationship to the message, a relationship to the receiver, which folder the subscription is currently in, and the status of the message for this receiver.
MessageRecipientTypes
The message recipient type allows us to track the receiver of this message addressed in the TO, CC, or BCC fields. Initially, our interface will only have a TO field. We should add this bit of metadata though just in case we want to expand our capabilities down the road! This is another example of a lookup table that we might need to use in the SQL queries. In our case, we will have an enum defined that maintains this lookup for us on the code side.
MessageStatusTypes
MessageStatusTypes allows us to track what a recipient is doing with his/her copy of the message, whether they have read the message, replied to the message, and so on. This is primarily so that we can change the UI to refiect its status to the recipient. However, we could also create a dashboard down the road for the senders of the messages to know whether their message was read or not and by whom (think of all the big brother things one could do…but probably should not do!).
MessageFolders
MessageFolders in our first round of implementation will simply hold copies of new messages in the Inbox and copies of sent messages in the Sent folder. We will also have a trash folder and a spam folder. That said, we always wanted to build a system with the future in mind if it doesn’t require a lot of extra work, and so we have also baked in the concept of a user being able to create and manage his/her own folders.
Therefore, rather than just see the MessageFolders table as another lookup table, you will see that there is an IsSystem fiag to denote which folders are to be seen system-wide. And you will see an AccountID column for custom folders so that we know who owns which folders.
Creating the relationships
Once all the tables are created, we can create the relationships.
For this set of tables, we have relationships between the following tables:
- Messages and MessageRecipients
- Messages and Accounts
- Messages and MessageTypes
- MessageRecipients and MessageRecipientTypes
- MessageRecipients and MessageFolders
- MessageRecipients and MessageStatusTypes
Setting up the data access layer
The data access layer in this case is very straightforward. Open up your Fisharoo.edmx file and add all of your new message-oriented tables.
Once you save this, you should now have a list of new domain objects in your arsenal (see the previous screenshot).
Building repositories
With these new tables come some additional repositories. We will create the following repositories.
- MessageRepository
- MessageRecipientRepository
- MessageFolderRepository
A detailed creation of repositories is out of the scope of this article. We will create a method for selecting a single entity by ID, a group of entities by their parents, saving entities, and deleting entities.
Having said that, there are a couple of methods that have something special in the set of repositories. As we are using message subscriptions, we don’t necessarily want to delete recipients haphazardly. We may want to delete a recipient, and if that recipient is the last recipient with a subscription to a message, we may also want to delete the message. On the other end of the spectrum, if we do delete a message, we may also want to remove all the recipient subscriptions.
In addition to these different ways of deleting data, we will also run into a scenario where selecting a single entity from our repositories won’t be quite good enough. So in this case, we have created an aggregate class that will allow us to select several entities at once for use in our inbox scenarios.
MessageRepository
When we think of a standard inbox, we know that we need to see the messages that we have, who sent them, when they were sent, and at least the subject of their message. In this case, we have discussed two different entities here. When we think about the fact that we also need to know who they were sent to, we have added a third entity. While we could run three separate queries for this data, it would be better for us to run one query (as we would have done in the old days) and return the data that we need in one shot.
What do we do? In this case, we need to create an aggregate. This is a class that contains other entities. We will therefore create a MessageWithRecipient class that will contain the sender’s account info, the message, and the recipient. This should provide us with enough data to represent messages in our inbox view later.
Before we write any queries, we first need to create the aggregate.
//Fisharoo/DataAccess/MessageWithRecipient.cs
namespace Fisharoo.DataAccess
{
public class MessageWithRecipient
{
public Account Sender { get; set; }
public Message Message { get; set; }
public MessageRecipient MessageRecipient{ get; set; }
}
}
With this aggregate in place we can now turn our attention to the repository that will get all this data for us.
//Fisharoo/DataAccess/Repositories/MessageRepository.cs
public List<MessageWithRecipient> GetMessagesByAccountID(Int32
AccountID, Int32 PageNumber, MessageFolders Folder)
{
List<MessageWithRecipient> result = new
List<MessageWithRecipient>();
using(FisharooDataContext dc = conn.GetContext())
{
IEnumerable<MessageWithRecipient> messages =
(from r in dc.MessageRecipients
join m in dc.Messages on r.MessageID equals m.MessageID
join a in dc.Accounts on m.SentByAccountID equals
a.AccountID
where r.AccountID == AccountID &&
r.MessageFolderID == (int)Folder
orderby m.CreateDate descending
select new MessageWithRecipient()
{
Sender = a,
Message = m,
MessageRecipient = r
}).Skip((PageNumber – 1)*10).Take(10);
result = messages.ToList();
}
return result;
}
This is a fun method! This method involves selecting a list of our MessageWithRecipient aggregate objects. The LINQ query is joining all the tables that we need and selecting a new instance of the MessageWithRecipient aggregate, that is then populated with the three classes that we need in the aggregate. Additionally, we have introduced some paging logic with the .Skip and .Take methods to produce a subset of the MessageWithRecipient objects.
In addition to the selection method above, we also need to discuss the delete method for this repository. As we have the data holding a subscription to our message data, it is important that we first remove all the subscriptions prior to removing the message itself.
//Fisharoo/DataAccess/Repositories/MessageRepository.cs
public void DeleteMessage(Message message)
{
using (FisharooDataContext dc = conn.GetContext())
{
IEnumerable<MessageRecipient> recipients =
dc.MessageRecipients
.Where(mr => mr.MessageID == message.MessageID);
foreach (MessageRecipient mr in recipients)
{
dc.MessageRecipients.DeleteObject(mr);
}
dc.Messages.DeleteObject(message);
dc.SaveChanges();
}
}
This is easily accomplished by retrieving all the MessageRecipients for the needed MessageID from the MessageRecipients in DataContext. Once we have the list, we iterate over each recipient and remove it from DataContext’s MessageRecipients list. Finally, we delete the message and save changes.
MessageRecipientRepository
The message recipient repository is considerably easier. It simply has an altered delete statement to adjust for the fact that if we delete the last subscription to a message, it will amount to deleting the message.
//Fisharoo/DataAccess/Repositories/MessageRecipientRepository.cs
public void DeleteMessageRecipient(MessageRecipient messageRecipient)
{
using (FisharooDataContext dc = conn.GetContext())
{
dc.MessageRecipients.DeleteObject(dc.MessageRecipients.Where
(mr=> mr.MessageRecipientID.Equals
(messageRecipient.MessageRecipientID))
.FirstOrDefault());
//if the last recipient was deleted
//…also delete the message
int RemainingRecipientCount =
dc.MessageRecipients.Where(mr => mr.MessageID ==
messageRecipient.MessageID).Count();
if (RemainingRecipientCount == 0)
{
dc.Messages.DeleteObject(dc.Messages.Where(m
=> m.MessageID == messageRecipient.MessageID).
FirstOrDefault());
}
dc.SaveChanges();
}
}
In this method, we delete the recipient in question. We then get a count of the remaining recipients for the message, which has the last recipient removed. If that count is zero, then there are no more recipients remaining for that message. In that case we perform a delete on that message and remove it from the system as well.
