Remove messages from one caller without affecting the other

Question

I am implementing a messaging system and need to make a way to remove messages from one caller without affecting the other.

So-and-so and Sicrano exchange messages:

1. At a certain point, Fulano removes the conversation, but Sicrano continues with the full conversation history.
2. When Fulano and Sicrano resume the dialogue, Fulano will only see the new messages and Sicrano continues with the messages from the beginning.
3. Naturally, messages can only be removed when both remove the conversation, and when a new message is exchanged there will be no old messages.

Perhaps by setting an individual flag with the timestamp of the last removal of the conversation, so only those messages that were earlier than the date were deleted by both Fulano and Sicrano would be listed. That way it would be easier to 'hide' in the message box those that were removed by an interlocutor, but it would be more complicated to combine the point where both parties deleted the messages to actually give a DELETE.

Answer 1

I prefer to leave for the idea of timestamp also, about your doubt of the DELETE I would do something like that:

1. A timestamp for each user of that conversation that will be overwritten every time they click remove.

2. So-and-so removed the messages, when Sicrano remove would DELETE in the Messages table passing the MENOR timestamp among the 2.

3. Why the MENOR? Simple, based on the smallest you know that both erased up there, if you catch the MAIOR it will happen that the other user has not deleted it yet.

4. From the time you have already removed once, every time the next user (different from the last one who did the deed) do the removal then run the DELETE.

Answer 2

From the way you describe the problem, it seems clear to me that a "message" and a "message in history" are independent entities. You have to make a model like

CREATE TABLE Message (
    MessageId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Content TEXT NOT NULL);

CREATE TABLE UserMessage (
    UserId INT NOT NULL,
    MessageId INT NOT NULL,
    SentBy INT NOT NULL,
    DateTime DATETIME NOT NULL,
    PRIMARY KEY (UserId, MessageId),
    FOREIGN KEY (UserId) REFERENCES User ON DELETE CASCADE,
    FOREIGN KEY (MessageId) REFERENCES Message ON DELETE CASCADE,
    FOREIGN KEY (SentBy) REFERENCES User (UserId) ON DELETE CASCADE);

You’d run a Garbage Collector regularly to erase the Message older than a certain time.

• By court order, you have to tap into Ana’s communication: you create a user for the police and add a UserMessage whenever there is a UserMessage associated with Ana (you can have a table of stapled users and do this via a Trigger, for example).

• In the case of forwarded messages or distribution groups, you only need to store a copy of the message to everyone who received the message - this is especially convenient if people can send videos, images, ...

• If only one particular message is private - for example, I accidentally sent an intimate message meant for my girlfriend to the football group - I can delete that message without destroying the rest of the conversation.