Need to create messaging system like in facebook - any ideas for design

[eluser]chamil sanjeewa[/eluser]
Hi guys, I'm building a small messaging system like you have in facebook. Only thing is that I want to know whats the best way to get on about it.
Like all users would have an inbox and sent items folder. Logically something in one persons sent folder would actually be in someone elses inbox. How do I implement this...

[eluser]Bastian Heist[/eluser]
Well, basically you'd need a table with a minimum of four fields: unique messageid (primary key), userid of sender, userid of recipient, and text. You'll want to add more fields for timestamp and title as well. Also, if you want a message reply history, you'll need another field to save the predecessor messageid.
So:

MESSAGEID, PRE_MESSAGEID, SENDER, RECIPIENT, TIMESTAMP, TITLE, TEXT.

That's how I'd start. You can extend it with a deletion flag, a link to a file or whatever else you might need.

[eluser]Mischievous[/eluser]
I've actually just got done building a replicate of facebook messaging with CI. And actually if you think about it, facebook messaging is just a conversation set between two users. My system needed to be a little bit more complex for specific project reasons. However, I have basically 3 tables:

messages, message_sets, message_statuses

Messages has: message_id, message_reply_to_id, message_set_id, sender_id, recipient_id, subject, body, time, last_update, recipient_status_id, sender_status_id.

Message_sets has: message_set_id, time, last_update, sender_user_id, recipient_user_id, marked_spam, sender_deleted, recipient_deleted

Message_statuses has: message_status_id, status

Then just setup a model that would get Message Sets grouped set id and entire message convo and the basic send etc. functions needed.

Whole thing was actually fairly simple.

[eluser]tkyy[/eluser]
i have built several messaging systems like this- i organize the actual viewing into threads by the user_id of the other person. then i do a really neat query to pull the results that groups them correctly, like this-

Code:

$array = $this->db->select('*, m.id as id, MAX(time_sent) as time_sent')
                            ->from('messages m')
                            ->join('users u','m.user_id_from = u.id')
                            ->where('user_id_to',$this->userdata['id'])
                            ->where('user_id_to_deleted',0)
                            ->group_by('user_id_from')
                            ->order_by('time_sent DESC')
                            ->limit(30)
                            ->get()
                            ->result_array();

        if(is_array($array) && count($array)>0)
        {
            foreach($array as $thread_base)
            {
                $one_item = $this->db->select('*, m.id as id')
                                                ->from('messages m')
                                                ->join('users u','m.user_id_from = u.id')
                                                ->where('user_id_to',$this->userdata['id'])
                                                ->where('user_id_to_deleted',0)
                                                ->where('user_id_from',$thread_base['user_id_from'])
                                                ->order_by('time_sent DESC')
                                                ->limit(1)
                                                ->get()
                                                ->result_array();

                $data['messages'][] = $one_item[0];
            }
        }

[eluser]chamil sanjeewa[/eluser]
hi tkyy


can u put your sorce code to the forum

[eluser]Unknown[/eluser]
I am also interested in how to implement such