4

I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far

enter image description here

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

5

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.

| improve this answer | |
2

I'd have a table sit between users and messages with a one-to-one many relationship to both. Call it users_messages for example. Each row in users_messages would describe all the users who have an involvement in any particular message. For example if a message had 3 users involved then the table would have 3 rows for this. Each row linking to the same message but linking to a seperate user.

When a row in users_message is deleted/deactivate you could check if any active entries for that message still exist and if not delete that message.

Edit: Missed the 2-user requirement but this is still valid and more future proof.

| improve this answer | |
  • This is a better solution than @blobbles', if you can have more than two parties to a conversation. – Daniel Hutmacher Aug 29 '14 at 15:36
0

Incase you need it to deactivate it for a single user, you can add a column named something like DEACTIVE_USER_ID in message table with default value as NULL and make a update for message table when a single user dectaivate it.

| improve this answer | |
  • this will not do anything because if DEACTIVE_USER_ID is not null the same message will be deactivated for both users – Sora Aug 29 '14 at 7:47
  • nope, I think my answer was missing the description that accepted answer has. anyway thanks :) – vijayp Aug 29 '14 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.