wincen Posted February 13, 2008 Share Posted February 13, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/90818-db-design-for-messaging-users/ Share on other sites More sharing options...
wincen Posted February 13, 2008 Author Share Posted February 13, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/90818-db-design-for-messaging-users/#findComment-465718 Share on other sites More sharing options...
dbo Posted February 13, 2008 Share Posted February 13, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/90818-db-design-for-messaging-users/#findComment-465931 Share on other sites More sharing options...
wincen Posted February 13, 2008 Author Share Posted February 13, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/90818-db-design-for-messaging-users/#findComment-466389 Share on other sites More sharing options...
dbo Posted February 14, 2008 Share Posted February 14, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/90818-db-design-for-messaging-users/#findComment-466481 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.