|
|
Posted by NC on 03/29/06 23:07
Andy Jeffries wrote:
> On Wed, 29 Mar 2006 09:53:54 -0800, NC wrote:
> >> Yes, a separate Table for messages sent,saved,received could give better
> >> results than a whole table with all the messages,
> >
> > I respectfully disagree. First, this would imply that a message sent by
> > user A to user B will have to exist in two instances (in the `sent` table
> > for user A and in the `received` table for user B). If a user broadcasts
> > a message to 100 other users, you'll have to store 101 copies of that
> > message...
>
> OK, if you only have one table, how do you cope with the situation above
> when 50 people delete it from their inbox, but the others want to keep the
> message.
Simple, really; per Jerry Stuckle's suggestion, let's adopt the
following data model with a few tweaks:
Table `messages`:
msgid (primary key)
msgtext
Table `headers`:
id (primary key)
msgid (index, non-unique; links to `messsages`.`msgid`)
owner (index, non-unique; links to user ID)
from
to
date
subject
status ('draft'/'sent'/'received'/'saved'/'deleted')
Now, let's say user A sends a message to B, C and D. Now we have
something like this (partial rendering of the `headers` table):
id | msgid | owner | from | to | status
11 | 8 | A | A |B,C,D| sent
12 | 8 | B | A |B,C,D| received
13 | 8 | C | A |B,C,D| received
14 | 8 | D | A |B,C,D| received
Viewing A's outbox would look like this:
SELECT [fields] FROM headers
WHERE owner='A' AND status='sent';
Viewing B's inbox would look like this:
SELECT [fields] FROM headers
WHERE owner='B' AND status='received';
Deleting a message by C would look like this:
UPDATE headers SET status='deleted'
WHERE msgid=8 AND owner='C';
Pretty straightforward, I think...
Cheers,
NC
Navigation:
[Reply to this message]
|