Relationship problem in Cakephp. How to fetch data?

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

WRT your schema, I would only set the relationships in the direction that you need to access them. Thus, if you don't need to ever access the User given a Message, don't link messages back to users. It can create some recursion problems where your models will automatically fetch way too much data, because you have a circular loop (User hasMany Message, Message HasMany User, etc.) That's actually what the HABTM relationship is, so use that instead of hasMany (if you want it to be a bi-directional relationship).

My comment mentioned tree behavior. Simply put, it allows you to easily store heirarchical data into a relational database table. I won't repeat the manual, but check out http://book.cakephp.org/view/91/Tree and google around for examples. I'm pretty sure this is what you want to use for threaded replies.

One further word of caution -- if you're going to use this with a serious user base, run some load tests on this part of your database. I'm not sure how well TreeBehavior would work with millions of rows on a cheap server.