1

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?

alt text

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!

Flukey
  • 6,445
  • 3
  • 46
  • 71
  • 1
    UserMessage and UserMessageReply both represent one class - a Message. I'd rathen created one class and related it to itself by a reply_id field. Read here about nest relations: http://www.doctrine-project.org/projects/orm/1.2/docs/manual/defining-models/zh#relationships:join-table-associations:self-referencing-nest-relations – Dziamid Jan 10 '11 at 21:01
  • Ah, interesting. Thanks very much. – Flukey Jan 11 '11 at 00:52
  • Did you go with this? Was it done in rails 3? – Satchel Jun 28 '11 at 16:20

3 Answers3

1

What about this model?

MESSAGES              USER_MESSAGES
========              =============
id                    id
content               message_id
reply_message_id      recipent_id
                      read
                      trash
                      hide
rkleine15
  • 147
  • 1
  • 2
0

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Hello mike,thank you for your reply. created_at, updated_at. This is a plugin behaviour of the ORM. Every record has a created_at field. ANd when a record is updated, it's updated_at field is updated accordingly. I like to have this behaviour. Oh, and yeah, the dbms will verify it, but do you have a better idea? I could have a thread table as suggested in the above comment and have parent_id, child_id. Thanks. – Flukey Jan 11 '11 at 01:55
  • I was thinking along the lines of those Booleans being declared in the database as "NOT NULL" and "DEFAULT FALSE". That guarantees they'll start out with the right values. – Mike Sherrill 'Cat Recall' Jan 11 '11 at 18:22
0

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.

ikhsan
  • 800
  • 5
  • 11