Database design for internal messages (e.g. facebook message)

In the following database design, how can I mark an unread message, do we know who reads the message from either the sender or the receiver?

(i.e. when person X sends a message to person Y, the message will be marked as unread for person Y until person Y reads this message, but it was marked as read for person X because he sent the message).

Message

  • Id (PK)
  • Subject
  • Content
  • MessageTypeId (FK) - message, update (public board), 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
  • The code
  • Name

EDIT: The design seems to be incomplete.

+3
1

Read, . , UserID MessageID. , , ReceiverID MessageId.

, ReceiverID MessageID, .

+1

All Articles