Within the following database design, how do you mark an unread message, therefore we know who browse the message either the sender and recipient?

(ie When person X transmits a note to person Y, the content is going to be marked as unread for person Y until person Y reads the content, nevertheless its marked as read for person X while he sent the content).

MESSAGE

  • Id (PK)
  • Subject
  • Content
  • MessageTypeId (FK) - Message, Update (global board-cast), notices etc
  • UserId (FK) - creator
  • CreateDate
  • ReadDate

MESSAGE_COMMENT

  • Id (PK)
  • MessageId (FK)
  • Content
  • UserId (FK) - creator
  • CreatedDate

USER

  • FirstName
  • LastName
  • Username
  • Password
  • IsActive
  • etc...etc...

MESSAGE_TYPE

  • Id
  • Code
  • Title

EDIT: it appears such as the design is incomplete.

You could include a Read table which links user ID and message ID. Whenever a message is produced the applying instantly puts within the UserID from the creator and also the MessageID. When the receiver reads the content it records the ReceiverID and also the MessageId.

So while there's no record for ReceiverID and MessageID, the content can look unread for that receiver.