Removing chat history for a particular user from database
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.