PHP/MySQL Based Email/Inbox Application

[jamesjmann]

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

[maxudaskin]

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.

[ignace]

-- 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)