Database design for internal Messaging (like facebook message)

Question

In the following database design, how do I mark an unread message, so we know who read the message either the sender and recipient?

(ie When person X sends a message to person Y, the message will be marked as unread for person Y until person Y reads the message, but its marked as read for person X because he sent the message).

MESSAGE

• Id (PK)
• Subject
• Content
• MessageTypeId (FK) - Message, Update (global board-cast), notifications 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
• Name

EDIT: it seems like the design is incomplete.

Answer 1

You could add a Read table which links user ID and message ID. When a message is created the application automatically puts in the UserID of the creator and the MessageID. Then when the receiver reads the message it records the ReceiverID and the MessageId.

So while there is no record for ReceiverID and MessageID, the message will appear unread for the receiver.