3

We are building a multitenant webservice with PHP and MongoDB. There is a need for communication between service employees and clients, communication can be established in many different ways. Some of them are: SMS, MMS, Email and, possibly, an internal chat system.

The question is, how to properly implement the database structure for it?

My boss insists, that there should be one collection - messages, where we would store the sender, type, receiver, message body, timestamp and arguments it that way, that we would have a route which points to users/1235/messages, where the chat history can be retrieved.

I, however, disagree on it and argument it in such a way, that every message type has its own properties and settings. For example, it would not be right to store the whole body of SMS message inside just one instance - one SMS can consist of up to 6 parts, which are delivered independently (they have their own tracking ID). This way, it is easy to track, which parts have been delivered, thus allowing to send missing parts instead of the whole message. Emails also have their own properties such as address, reply-to and other stuff.

Probably I think about constistency too much, since I've been using MySQL for a long time. But, in my opinion, storing all messages just in one collection would give a huge property diversity in the object. Storing each message system separately, however, would give flexible options, allowing to track each message's status and other details. All the messages normalized and joined into one data collection later. (Concat SMS to build the whole body, etc)

So, what kind of solution would you see here?

4

This sounds to me like the same problem you would have storing sub classes in a relational database.

ie. you have an abstract Message class with some common fields, Id, SenderId etc and then subclasses such as SMSMessage, EmailMessage with extra fields.

My approach here is to have one table Message with the common columns plus 'Type' and 'TypeId' then a table per type with the extra type specific data

This creates an odd relationship, but does allow you to do simple, 'get all messages regardless of type' queries, plus more complicated, 'get sms messages where some parts have failed to send'

Translating to a NoSql approach does raise some interesting questions about how many object types you want to have a single collection. However, I believe the c# MongoDb client will allow you to put the sub classes in the same collection and correctly deserialise them from BSON (assuming you have set the mappings up) Which suggest this is the approach they expect you to take.

If you did split this objects into multiple collections, how would you enforce the relationship integrity? It seems like a RDB style approach to me, which would give you additional problems.

0

I suppose that with Mongo putting everything onto one polymorphic collection is not unreasonable.

For every message you can pack whatever details pertain to its type, because you're not limited by a relational schema. Your processing / display code should be pretty flexible, though, ready to discover extra details in a record, and ready to handle absence of certain details. In time, the data format will inevitably slightly change.

That said, you'll have to have some strictly required fields, like and ID and a timestamp, which you will index for fast access.

0

I would decide based on my UI design.

If I have separate UI for each type of messages - SMS, Email, etc. and a single simple call is all I need to get the required data for each type, then I'll separate out each type of message.

But, if the UI is designed in a way that requires you to display all types of messages on the same screen, then I will club them all together.

The intention is to hit the DB as less as I can. At the same time, trying to keep the query as simple and efficient as possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.