创建消息传递“系统”在DB（最特别是MySQL） [英] Creating a messaging "system" in DB (most especially MySQL)

问题描述

对不起，我有一个问题：

Sorry, I have a concern:

我在MySQL中创建了一个表格：

I have a table successully created in MySQL:

CREATE TABLE IF NOT EXISTS MESSAGE
(
    MESSAGE_ID          BIGINT NOT NULL AUTO_INCREMENT      
    ,AUTHOR_ID          VARCHAR(30) NOT NULL
    ,TITLE              VARCHAR(100) NOT NULL
    ,MESSAGE            VARCHAR(4095) NOT NULL
    ,UNREAD_FLAG            BOOLEAN NOT NULL DEFAULT TRUE                   
    ,CREATION_DATE          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ,DATE_LAST_MODIFIED     TIMESTAMP NULL

    ,PRIMARY KEY (MESSAGE_ID)
    ,FOREIGN KEY (AUTHOR_ID) REFERENCES USER (USR_ID)
);

正如你所看到的，没有引用谁收到消息的原因1个或多个收件人可以收到相同的邮件。

As you can see, there is no references to who receives the message(s) for the reason that 1 or more recipients can receive the same message.

1）我如何实现一个消息线程模型，如果有一个消息发送超过1用户，收件人可以回复该邮件并跟踪收件人回复的消息？例如。我已经向5个朋友发送了一个关于一个聚会的消息，他们都回复了，我如何保持记录在响应消息链接到原始消息？此外，接收者还可以响应响应的消息，创建对响应消息的响应，因此最终它将是响应消息的树状结构。

1) How would I implement a message-threading "model" such that if there's a message that is sent to more than 1 user, the recipients can reply to the message and keep track on what message the recipient replied to? E.g. I've send a message to 5 friends about a party, and they all reply, how will I keep records on the response message link to the original message? Also, a recipient can also respond to a responded message, creating a response to a responded message, so eventually, it'll be a tree like structure of responded message.

2）如何创建一个表，让多个收件人接收同一个消息？我有一个想法创建一个表recipient_id（引用用户表）和message_id（消息id）对。那么高效吗？我问的原因是，如果200人收到相同的消息，那么将有200表的user_id，message_id对...

2) How can I create a table to have more than 1 recipient receiving the same message? I had an idea of creating a table with recipient_id (reference to user table) and message_id (message id) pair. Is that efficient? The reason I ask is that if 200 people receive the same message, then there'll be 200 tables of user_id, message_id pair...

再次感谢，和平！

PS我实现了第2号），所以我想这不会是一个问题。

P.S. I've implemented number 2), so I guess that's not going to be an issue.

推荐答案

1. 我不明白您的问题。

1. I don't understand your question here.

您的想法是正常的做法。您可以设置一个包含recipient_id和message_id的表，并对其执行查询以确定（例如）特定用户具有什么消息。您可能还需要在此表格中存储其他数据，例如用户是否已阅读邮件等，具体取决于您的应用的需求。

Your idea is the normal way to do this. You'd set up a table with recipient_id and message_id and run queries against it to determine (for instance) what messages a particular user has. You might also need to store additional data in this table like whether the user has read the message, etc., depending on the needs of your app.

这篇关于创建消息传递“系统”在DB（最特别是MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！