As 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 type template
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 would run a garbage collector regularly to delete the Message
older than a certain time.
-
By court order, you have to staple Ana's communication: you create a user for the police and add 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 case of forwarded messages or distribution groups, you only need to store a copy of the message to everyone who has received the message - this is especially convenient if people can send videos, images, ...
If only one specific message is private - for example, I accidentally sent an intimate message to my girlfriend for the football group - I can delete that message without destroying the rest of the conversation.
/ li>