Facebook style messaging system schema design

I'm looking to implement a facebook style messaging system (thread messages) into a site of mine.

Do you think this schema markup looks okay?

Doctrine schema.yml:

UserMessage:
  tableName: user_message
  actAs: [Timestampable]
  columns:
    id: { type: integer(10), primary: true, autoincrement: true }
    sender_id : { type: integer(10), notnull: true }
    sender_read: { type: boolean, default: 1 }开发者_JAVA百科
    subject: { type: string(255), notnull: true }
    message: { type: string(1000), notnull: true }
    hash: { type: string(32), notnull: true }
  relations:
    UserMessageRecipient as Recipient:
      type: many
      local: id
      foreign: message_id
    UserMessageReply as Reply:
      type: many
      local: id
      foreign: message_id
UserMessageReply:
  tableName: user_message_reply
  columns:
    id: { type: integer(10), primary: true, autoincrement: true }
    user_message_id as message_id: { type: integer(10), notnull: true }
    message: { type: string(1000), notnull: true }
    sender_id: { type: integer(10), notnull: true }
  relations:
    UserMessage as Message:
      local: message_id
      foreign: id
      type: one
UserMessageRecipient:
  tableName: user_message_recipient
  actAs: [Timestampable]
  columns:
    id: { type: integer(10), primary: true, autoincrement: true }
    user_message_id as message_id: { type: integer(10), notnull: true }
    recipient_id: { type: integer(10), notnull: true }
    recipient_read: { type: boolean, default: 0 }

When I a new reply is made,i'll make sure the boolean for "recipient_read" for each recipient is set to false and of course i'll make sure sender_read is set to false too.

I'm using a hash for the URL: http://example.com/user/messages/aadeb18f8bdaea49882ec4d2a8a3c062

(As the id will be starting from 1, i don't wish to have http://example.com/user/messages/1. Yeah, I could start incrementing from a bigger number, but i'd prefer to start at 1.)

Is this a good way to go about it? Your thoughts and suggestions would be hugely appreciated.

Thanks guys!

What about this model?

MESSAGES              USER_MESSAGES
========              =============
id                    id
content               message_id
reply_message_id      recipent_id
                      read
                      trash
                      hide

What's the difference between user_message.created_at and user_message_recipient.created_at?

Same question for updated_at. (Later: I guess user_message_recipient.updated_at might be the time the recipient read the message. If that's the case, I'd prefer a more meaningful name.)

Did you consider digging into some source code for open source projects that are intended to be Facebook replacements?

When I a new reply is made,i'll make sure the boolean for "recipient_read" for each recipient is set to false and of course i'll make sure sender_read is set to false too.

I hope that means the dbms will verify those Booleans are set correctly.

i also tried to create a message system using database, the most common flaw is forgot that every user have different state of read-unread message. i hardly showing in database scheme, but in short you must consider to create flag like isread for every user for every user_message and user_message_reply, so every user just see the unread message.