Sorry, I've got a concern:

I've got a table successully produced 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 tell, there's no references to who receives the content(s) because 1 or even more readers will get exactly the same message.

1) Wouldso would I implement a note-threads "model" so that if there is a message that's delivered to a lot more than 1 user, the readers can answer the content and track on which message the recipient responded to? E.g. I have send a note to five buddies in regards to a party, plus they all reply, the way I keep records around the response message connect to the initial message? Also, a recipient may also react to a responded message, developing a reaction to a responded message, so eventually, it will be a tree like structure of responded message.

2) How do i produce a table to possess a lot more than 1 recipient finding the same message? I'd a concept of developing a table with recipient_id (mention of the user table) and message_id (message id) pair. Is the fact that efficient? The main reason I request is when 200 people get the same message, then there will be 200 tables of user_id, message_id pair...

Thanks once more, Peace!

P.S. I have implemented number two), and so i guess that's not really an problem.

  1. I do not understand your question here.

  2. Your idea may be the normal method of doing this. You'd setup a table with recipient_id and message_id and run queries against it to find out (for example) what messages a specific user has. There is also to keep additional data within this table like if the user has browse the message, etc., with respect to the needs of the application.