title: Database schema for messaging to multiple users
body: <p>I'm looking for the best solution to implement messaging to multiple users within the system(Facebook-style). </p>
  
  <p>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.</p>
  
  <p>Could anyone suggest any other solution to the current problem? Or explain why my solution will be fine?</p>
  
  <pre><code>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`);
  </code></pre>