|
|
Posted by NC on 03/29/06 20:53
J2be wrote:
> <chris.withers@gmail.com> wrote in message
> news:1143627975.829456.318230@t31g2000cwb.googlegroups.com...
> >I have built a web portal and was considering adding a 'user mail'
> > feature for users to message each other. I'm a bit uncertain on how to
> > design the DB, so was wondering if there is a standard to do this. I
> > was figuring perhaps a table storing all user messages (but this might
> > become very large very fast?) or would a seperate db storing
> > sent/saved/recieved etc messages be better?
>
> 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... Second, changing message status, say, from
received to saved would require an INSERT INTO `saved` and a DELETE
FROM `received` (probably inside a transaction, just to make sure we
don't end up with twins), as opposed to something much more mundane,
such as UPDATE `messages` SET status='saved'.
Personally, I like Jerry Stuckle's suggestion:
First table:
msgid
from
to
date
subject
status (sent/received/saved)
Second table:
msgid
msgtext
This is a good architecture, which may require a little tweaking if
users are allowed to delete their messages and/or send messages to
multiple users.
Cheers,
NC
Navigation:
[Reply to this message]
|