用于向多个用户发送消息的数据库模式 [英] Database schema for messaging to multiple users

问题描述

我正在寻找最好的解决方案来实现消息传递给系统内的多个用户（Facebook风格）。

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

我想出了以下想法：每个消息属于Message_Chain，并且在Message_status表中列出了user-sender和users-receivers。但是，恐怕这个模式在系统中有数百万条消息时使用效率不高。

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`);

推荐答案

例如，如果您要向系统的所有用户发送消息，请放置在usr_id列中输入，然后程序化地获取usr_id = current_usr_id或''的所有消息，然后你可以使用各种过滤器，并创建自己的语法来创建messager_user没有数据库存储的列表。

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 ...

这篇关于用于向多个用户发送消息的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！