CakePHP Relationships - How to setup relationship for message system ?

Question

Hi ! I am trying to make a messages functionality similar to the facebook. Just the message thing and not facebook. A brief descriptions does like this.

1) There are a number of users ( user table) 2) One person can send a message to one or more than one person. 3) There can be multiple reply to the same message. 4) If its send to multiple people. All can reply and it is show to all of them.

Tables used

messages table

id
timestamp
sender_id
subject
message
due_date
urgent_flag
open_flag
reply_id

message_user (table)

id
timestamp
message_id
receiver_id
read_flag

The CakePHP relations are as follows :

Message Model

var $hasMany = array(
        'MessageUser' => array(
            'className'     => 'MessageUser',
            'foreignKey'    => 'message_id',    
                )
    );  
var $belongsTo = array (
    'User' => array (
        'className' =>  'User',
        'foreignKey' => 'sender_id',
    )
);
var $hasAndBelongsTo=array(
    'Message' => array (
        'className' => 'Message',
        'foreignKey' => 'reply_id',
       )
);

MessageUser Model

var $belongsTo = array (
    'User' => array (
        'className' =>  'User',
        'foreignKey' => 'receiver_id',
    ),
    'Message' => array (
        'className' =>  'Message',
        'foreignKey' => 'message_id'

    )
);

Questions :

1) Is my approach correct ? Or the database schema needs to be revised. 2) If yes, How should I fetch the data for inbox ? This is a bit complex as I want to show the conversation for those messages which people has sent me.

For example, user 1 send message to user 2. User 2 adds 2 replies to the same. Then users 1's inbox should show only 1 message. and when I open it. it will show the previous msgs as well.. (this is similar to facebook)

One more problem which I see here is, How to delete messages ? Suppose user 1 deletes a message it should not show anything in his inbox. but user 2 can see the whole conversation which he had.

Answer 1

Seems like a good approach

As to your last problem. Add a tinyint / datetime column to message_user, ex. "deleted" (or something similar).

Then in your User-model do something like:

var $hasAndBelongsTo=array(
    'Message' => array (
        'joinTable' => 'message_user',
        'className' => 'Message',
        'foreignKey' => 'reciever_id',
        'associationForeignKey' => 'message_id'
        'conditions' => 'MessageUser.delete = 0'
       )
);

You'll need to modify this, but it's a start...