Creating a messaging "system" in DB (most especially MySQL)
I have a table successfully 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 reaso开发者_JAVA技巧n that 1 or more recipients can receive the same message.
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.
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...
P.S. I've implemented number 2), so I guess that's not going to be an issue.
I don't understand your question here.
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.
精彩评论