Database Design for Facebook Message like, application

Designing a facebook message like application for an internal application.

Is this how you would design it? any way to improve on this design? thanks :)

MESSAGE
Id
Subject
Content
ReadDate -- datetime
DeletedDate -- datetime
CreatedBy -- userid
CreatedOn -- datetime

MESSAGE_COMMENT
Id
MessageId
Content
CreatedBy -- userid
CreatedOn -- datetime

MESSAGE_RECIPIENT
Id
MessageId
Recipient -- UserId 
R开发者_如何学运维eadDate -- datetime
DeletedDate -- datetime

EDIT: there might be something missing with the design or it may be incorrect, let me know.

---

First, a few minor suggestions from an over-all design perspective:

1. Since you are using Pascal Case for your field names, you should also use it for the Table names to be consistent. So MESSAGE_COMMENT should be MessageComment

2. Do not reuse the field name "Id" since it means something different in each table. So "Id" in Message should be "MessageId", "Id" in MessageComment should be MessageCommentId, and so on

3. If the field is a Foreign Key, it should contain the name of the field it points to. So, "CreatedBy" should be "CreatedByUserId" and "Recipient" should be "RecipientUserId". Remember, the more explicit you are in naming your fields the less additional comments and documentation you will need and hence it will be less confusing for anyone else to understand what is intended.

Now, as far as the question of "is this an appropriate design to do X" is concerned, you will need to give more details on how you envision this system working. It appears that the Message is the parent whereas any replies to it are considered MessageComments. So far that looks ok.

I am not sure why you need "ReadDate" in the main Message table as that also exists at a more accurate level in MessageRecipient. Unless of course you want to trap the FIRST time it is read but you can still get that from the MessageRecipient table by looking at all Recipients of a particular Message.

Lastly, you should consider not even having "MessageRecipientId" and using "MessageId" and "RecipientUserId" as the composite Primary Key. This will ensure that you can only have a RecipientUserId on a Message one time without needing to create an additional Unique Index on those two fields.

---

I think, this is pretty enogh, for sending/receiving messages.
More depends on what you would like to do...