Creating a messaging "system" in DB (most especially MySQL)

Question

Hi guys,

Sorry, I have a concern:

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

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

Thanks once again, Peace!

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

Answer 1

1. I don't understand your question here.

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