doubledee Posted March 26, 2012 Share Posted March 26, 2012 I could use some help setting up the Database Design for a Private Messaging (PM) system that I want to add to my website. After Googling this topic, I haven't many good examples, and the ones that I have found seem off or don't relate. Here are my requirements (for now)... - A User can be a Sender - A User can be a Recipient - A PM can only have one Sender - A PM can have multiple Recipients If I only allowed one Sender and one Receiver for a PM, then this is my best guess... Member table: - id - first_name - username - email Message table: - id - from_id - to_id - sent_on - subject - body Relationships: member (sender) -||-------0<- message member (recipient) -||-------0<- message ...where the "member" table is really just serving as a "lookup" table TWICE. If I allowed one Sender and multiple Receivera for a PM, then this is my best guess... Member table: - id - first_name - username - email Recipient table: - id - member_id - message_id - read_on Message table: - id - from_id (FK to id in Member table) - sent_on - subject - body Relationships: member (sender) -||-------0<- message member -||-------0<- recipient -0>-------||-message How do those two different scenarios look? Comments? Thanks, Debbie Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/ Share on other sites More sharing options...
AyKay47 Posted March 26, 2012 Share Posted March 26, 2012 didn't we discuss this topic in the application design forum a few days ago? I gave a very detailed answer in that thread. Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/#findComment-1331360 Share on other sites More sharing options...
doubledee Posted March 27, 2012 Author Share Posted March 27, 2012 didn't we discuss this topic in the application design forum a few days ago? I gave a very detailed answer in that thread. Your response to my answers were pretty brief, so, no, we didn't discuss things very deeply... http://www.phpfreaks.com/forums/index.php?topic=356381.new;topicseen#new But I'm game here! Debbie Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/#findComment-1331409 Share on other sites More sharing options...
AyKay47 Posted March 27, 2012 Share Posted March 27, 2012 that is the exact thread I was referring to. This topic should be continued there, I posted the last reply, a response is needed. And my replies are most certainly not short there. If you do not want my help, I do not have to give it. Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/#findComment-1331446 Share on other sites More sharing options...
doubledee Posted March 27, 2012 Author Share Posted March 27, 2012 that is the exact thread I was referring to. This topic should be continued there, I posted the last reply, a response is needed. And my replies are most certainly not short there. If you do not want my help, I do not have to give it. But your response there doesn't answer my questions here. Here I talk about two different solutions. And I am asking about a new problem here (i.e. One Sender, and Many Recipients). So what do you think about what I've said here? Debbie Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/#findComment-1331450 Share on other sites More sharing options...
Psycho Posted March 27, 2012 Share Posted March 27, 2012 So what do you think about what I've said here? Pretty much, but I would make a few comments. Give tables and fields meaningful names that will provide context to the purpose and relationship of the data. It will make a world of difference when you are coding - especially when you need to go back and change things. I would change the "Recipients" table to "Message_Recipients" which implies that it is dependent on the messages. Also, the recipients table does not need a unique ID field since it would not be needed for anything. The recipient records are associated with the messages table using the message id. Lastly, for the foreign key references don't give them a name that is completely different than the primary key name. For example, I would use "from_member_id" for the "member_id" FK. (in fact I, personally, would never name a field simply "id" even as the primary key). Here is the layout I would suggest: Member table: - id - first_name - username - email Message table: - id - from_member_id (FK to id in Member table) - sent_on - subject - body Message Recipient table: - message_id - to_member_id - read_on Then to get the data for a message and all the recipients you would use SELECT * FROM messages LEFT JOIN members AS m1 ON messages.from_member_id = m1.id LEFT JOIN recipients ON messages.id = recipients.message_id LEFT JOIN members AS m2 ON recipients.to_member_id = m2.id WHERE message.id = '$messageid' That's only a rough example, you would only want to explicitly incude the necessary fields in the select list Quote Link to comment https://forums.phpfreaks.com/topic/259760-setting-up-tables-for-pm-system/#findComment-1331695 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.