jamesjmann Posted August 8, 2012 Share Posted August 8, 2012 Hi, I'm creating a php/mysql/jquery based email application which allows users on my site send and receive private messages, and I was wondering if someone could give me advice on which way I should go about storing different types of messages, e.g., spam, draft messages, and messages in the "trash" folder. Right now, I'm leaning on using only one table for all messages no matter what folder they're in. Example 1 Table 1 Messages Fields id user_1 user_2 message draft spam trash date Example 2 Table 1 Messages Fields id folder_id user_1 user_2 message date Table 2 Folders Fields id name date Example 3 Table 1 Messages Fields id user_1 user_2 message date Table 2 Trash Fields id user_1 user_2 message date exp Table 3 Drafts Fields id user_1 user_2 message date Table 4 Spam Fields id user_1 user_2 message date So with the first example, if a user moves a message to the "trash" folder, there would be a query run to update the row storing the message and would place a "1" in the trash field. If the user, then, decided to move it to the "spam" folder, the row would be updated to make the "spam" field contain a value of "1" and would make the "trash" and "draft" fields "0". With the second, there would be one table storing messages and a second storing all of the possible folders a user could move them to. For instance, if the user decided to move a message to the "trash" folder, the row containing the message would be updated to have the "folder_id" field contain the value of the id of the folder in the "Folders" table. The third is just really messy and redundant in my opinion, but I figured I'd include it, because it is ultimately one way that can allow for flexibility and seems versatile enough. I figure that the 3rd example is probably the worst way to go about this, because it uses up more resources, i.e., it takes up unnecessary space with all of the extra tables. I like the first the best, because only one table is used, and so only one table would need to be updated anytime a user opts to move a message to another folder. Anyone have an idea as to what I should do? Any and all advice is appreciated Quote Link to comment Share on other sites More sharing options...
maxudaskin Posted August 9, 2012 Share Posted August 9, 2012 Here is the setup I would use. You should relate the fields, but it is just as good either way. == Users == Name | Type | Comments ==================================== id | int | The unique id for the user. Auto-increment ------------------------------------ username | varchar | The username ------------------------------------ What ever other fields you want... == Messages == Name | Type | Comments ==================================== id | int | The unique id for the message. Auto-increment ------------------------------------ users_id | int | The id relating to the user whose inbox | | this message will reside. ------------------------------------ sender | int | The id of the sending user. ------------------------------------ message | longtext | The message. ------------------------------------ type | int | The type of message it is. ------------------------------------ status | int | The status of the message The users id will correlate to the user that received the message. The sender id will correlate to the user that sent the message. You can use hard coded negative id's to indicate certain server messages. The message should be rendered safe from sql injection before saving. The type of message is essentially the inbox; less important, regular, more important, spam. The status would indicate whether it's a draft, sent, deleted, or saved. Yes, you should want to keep deleted messages, but make sure that you indicate that you reserve the right to retain any messages sent. Quote Link to comment Share on other sites More sharing options...
ignace Posted August 10, 2012 Share Posted August 10, 2012 -- the user's table users (usr_id, ..) -- the messages table: author_id references usr_id messages (msg_id, author_id, ..) -- a message may have multiple recipients, type=to,cc,bcc recipients (msg_id, usr_id, type) -- each folder is owned by a user, so that my inbox will never be visible to you, type=inbox,spam,.. see folder_types -- this means that when a user registers you need to create all system dependent folders (inbox, spam, ..) for this user -- there is another way you can create this relation, which involves a compound pk, this one is simpler, you still would need to insert -- the relations, but you wouldn't have to insert the name of the folder. folders (fldr_id, usr_id, name, type) -- which messages are contained in this folder? folder_has_message (fldr_id, msg_id) -- folder types, inbox, spam, .. also is_deleteable, for custom folders this is always true, for system dependent folders (inbox, spam, ..) this is false -- unless you would also start to allow something like rules (put a msg from a specific author in a specific folder) folder_types (fldr_tp_id, is_inbox, is_spam, .., is_deleteable) Quote Link to comment 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.