Removing chat history for a particular user from database I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far 



I've added an option where the user can Clear Chat history for a particular conversation , but I'm having trouble with the concept of clearing history for a particular user in wish case if user-1 hit clear history all the messages of this conversation will not be displayed for user 1 but will still be seen for user-2

How can I upgrade my database schema so I can make this functionality ?

PS : yesterday i asked the same question on stackoverflow website and someone gave me a solution .but the solution he gave me is complicated and it didn't fill my requirement .
my database is currently supporting only private chat between 2 users
A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO to the ID of User2. Something like (passing in USER_ID for both User1 and User2):

UPDATE Messages
SET MESSAGE_VISIBLE_TO = @UserID2
WHERE MESSAGE_SENDER_ID = @UserID1 AND MESSAGE_SENDER_ID = @UserID2


The MESSAGE_VISIBLE_TO field could have 4 possible "states":


NULL = the default, both users can see the message
-1 = neither user can see the message
UserID1 = User1 ONLY can see the message
UserID2 = User2 ONLY can see the message


The application would need to incorporate the logic to UPDATE and SELECT the messages either at the database level (probably preferable as you wouldn't need to send more data than required) or at the application level (simply hiding the messages that the user is not supposed to see).

If you wish to eventually upgrade the application to support more than 2 people chatting, your approach would need to be different. You would need to create another table in between the Users and Messages table. You could name the table something like "MessageStatus", including the following fields:

USER_ID
MESSAGE_ID
VISIBLE (bit)


The table would then identify which users can see which messages by the value of the VISIBLE bit field.