DB design for messaging users

[wincen]

I'm working on a website where users that have logged in can send and receive messages from each other.  I'm wondering what is the best database design for messages?  I want users to be able to read and delete messages that they sent as well as received - and that is my delema.  Do I create two tables, one for those in the inbox, and one for those in the sent box?

 

 

 

my initial DB deisgn was something like:

 

messages

--------------

id

sender_id

receiver_id

date_sent

subject

messages

status_id

 

 

if i just share one entry in this table for both users, one user may delete the message but the other may not.  Thus do I need two entries per table, two tables, or some other solution?

 

Thanks in advance

[wincen]

or perhaps a db isn't the way to go?

maybe using files would be better?  it seems that using a db would mean dealing with scaling issues.

 

btw... the messaging system would be something like friendster's... or at least that's the idea

[dbo]

or perhaps a db isn't the way to go?

maybe using files would be better?  it seems that using a db would mean dealing with scaling issues.

 

btw... the messaging system would be something like friendster's... or at least that's the idea

 

I would think you would have more scaling issues, permission problems, and custom processing code if using flat files rather than a database....

 

I'd have to think about a full blown layout to give a better answer for design. One quick and dirty way might be to have a "SenderDeleteFlag" and "ReceiverDeleteFlag" added to your existing table structure. This way the record never truly gets deleted, just hidden from view if the appropriate party chooses to do this. It also eliminates the need to store a record for each party involved.

[wincen]

that's an excellent idea.  thanks!

 

i've been checking some other sites and it seems that with the same message (one being from the sender while the other is the recipient) that their message ids (in the url) are different.

 

is there an advantage to this?  maybe it's a security issue?  just curious.

[dbo]

I can't think of a reason you would need (or want) to have the message stored twice. Redundancy in databases is generally not a good thing.

 

One thing your database probably is missing is the ability to reply to a message. I'm not sure, but you would probably want to be able to link 1 or more responses to a parent message.

 

That being said... it would really require a full set of requirements/scope to come up with a full blown design. Just make sure you test  your database and run through various scenarios before you write any code. Changes made to the data model are much easier to handle up front.