privmsg system db schema

Question

I'm making a PM-system on my site. And I want to know ultimate db schema.

I have always just used only 1 table. But my users have started complained that the messages in their outbox suddently dissapers =D Thats because if the other users deletes it, the one who sent it wont see it to.

So im thinking of making another table with the same fields

So im thinking something like this:

privmsgs

id | to | from | subject | message | date
--   --   ----   -------   -------   ----
1    76   893    blabla.   blabla.   20100404

sent_msgs

id | to | from | subject | message | date
--   --   ----   -------   -------   ----
1    76   893    blabla.   blabla.   20100404

Whatya think?

Sorry for my bad english

edit: maybe i shouldtn actualy delete the messages, just add a field called "deleted" and set it to 1

Answer 1

This can be achieved with one table.

id | to_id | from_id | subject | message | date     | to_deleted | from_deleted
--------------------------------------------------------------------------------
1  |  76   |   893   | blabla. | blabla. | 20100404 | BOOLEAN    | BOOLEAN

My Inbox

SELECT * FROM messages WHERE to_id=<my_id> AND to_deleted = FALSE;

My Outbox

SELECT * FROM messages WHERE from_id=<my_id> AND from_deleted = FALSE;

May want to add and cronjob to clean up your messages now and again

DELETE FROM messages WHERE to_deleted=TRUE AND from_deleted=TRUE;

On a side not, not a good idea to call a column name "from" as this is a MYSQL key word and may cause errors.