Database schema for messaging to multiple users

Question

I'm looking for the best solution to implement messaging to multiple users within the system(Facebook-style).

I'm came up with the following idea: where each Message belongs to the Message_Chain and in the Message_status table user-sender and users-receivers are listed. However I'm afraid that this schema is not very efficient to use when there are millions of messages in the system.

Could anyone suggest any other solution to the current problem? Or explain why my solution will be fine?

CREATE  TABLE `message` (
  `msg_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `msg_text` TEXT NOT NULL ,
  `msg_date` DATETIME NOT NULL ,
  PRIMARY KEY (`msg_id`) );

CREATE  TABLE `message_chain` (
  `msgc_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `msgc_topic` VARCHAR(255) NULL ,
  PRIMARY KEY (`msgc_id`) );

CREATE  TABLE `message_status` (
  `msgsta_msg_id` BIGINT UNSIGNED NOT NULL ,
  `msgsta_usr_id` INT UNSIGNED NOT NULL ,
  `msgsta_msgc_id` INT UNSIGNED NOT NULL ,
  `msgsta_is_sender` TINYINT(1)  NULL ,
  `msgsta_is_read` TINYINT(1)  NULL DEFAULT NULL ,
  `msgsta_is_deleted` TINYINT(1)  NULL ,
  PRIMARY KEY (`msgsta_msg_id`, `msgsta_usr_id`);

Answer 1

The schema should work just fine as long as there aren't too many recipients of the same message. I don't see how you could make it much smaller or more efficient.

The only performance problem I can see is that if you want to do broadcasting, that is, send the same message to a large group or, say, every user on the system. Sending such a message will be very slow (been there, done that). In that case, I would track the status of such global messages lazily, that is, create the status row for an individual user only after he has opened the message. But if you don't have such a feature planned, I'd say ignore this problem for now.

Answer 2

My solution is to avoid the massive data storage by using more programming to determine which message goes to whom.

For example if you want to send a message to all users of the system, you place a "" in the usr_id column, and then programmmatically you could fetch all messages where the usr_id = current_usr_id OR ''. Then you could do a variety of filters and come up with your own syntax for creating messager_user lists without database storage.

Seems like a trade-off between processing/storage ...