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

views:

318

answers:

+2 Q:

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

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. The database design to be precise in this case.

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. However I don't think using one table to hold the messages would work as lets say someone deletes a message form his sent box it would be gone since both reciever and sender would be referring to the same table. How do I implement this...

+5 A:

Blackethylene 2009-08-10 06:38:59

What did you use to make that diagram?

Unknown 2009-08-10 06:39:45

I'd like to know that as well.

Maciek 2009-08-10 06:40:30

It's MySQL Workbench, a wonderful tool to visually design MySQL databases.http://dev.mysql.com/downloads/workbench/

Blackethylene 2009-08-10 06:57:23

Nice - how do I set it for both recievers and senders. DO I need to make separate tables for both?

Ali 2009-08-10 07:21:33

No, you can simply add a field in the "users_has_messages" table, where you can specify if the user is the sender or a receiver. To keep it minimalistic, this field could be a boolean field named something like "is_sender".

Blackethylene 2009-08-10 08:59:12

you said: "it only deletes a row in the "users_has_messages" table, and not the message itself"and what about the messages? why do you keep the message? if it's deleted i don't think we need to keep it. or am i wrong?

fabrik 2009-08-11 08:31:31

Usually a message has one sender and one/several receiver(s). If one of them erase it in its inbox, the application just erases the row in "users_has_messages" table. So others can still see it. In case all the users have deleted this message, it's true we do not need to keep it. There are many solutions to 'clean' the table from orphan messages, a cron job for example.

Blackethylene 2009-08-11 10:02:52

ansaurus

tags:

views:

answers:

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

related questions