Facebook style messaging system schema design
- by Jamie
Hi all,
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 }
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!
