Remove messages from one party without affecting the other

3

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

Fulano and Sicrano exchange messages:

  • At one point, So-and-so removes the conversation, but Sicrano continues with the full conversation history.
  • Of course, messages can only be removed when they both remove the conversation, and when a new message is exchanged there will be no old messages.
  • Perhaps by making an individual flag with the timestamp of the last removal of the conversation, so only the messages before the date they 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 at which both parties excluded the messages to actually give a DELETE .     

    asked by anonymous 23.06.2015 / 01:13

    2 answers

    2

    I'd rather start with the idea of timestamp , too, about your question of DELETE I'd do something like this:

  • A timestamp for each user of that conversation that will be overwritten each time you click remove.

  • 23.06.2015 / 01:27
    1

    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>
    23.06.2015 / 01:45