Single mysql table for private messaging

I'm trying to create a single table for private messaging on a website. I created the following table which I think is efficient but I would really appreciate some feedback.

CREATE TABLE IF NOT EXISTS `pm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `to` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `read` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DE开发者_StackOverflow中文版FAULT '0',
  PRIMARY KEY (`id`)
  FOREIGN KEY (user_id) REFERENCES User(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I have 2 columns that determine the status of the message: read and deleted

If read = 1, the message has been read by the receiver. If deleted = 1, either the sender or the receiver deleted the message from the sent or received inbox. If deleted = 2 both users deleted the message, therefor delete the row from the database table.

I see that you don't have have any indexes explicitly stated. Having the appropriate indexes on your table could improve your performance significantly. I also believe that for your message column you may want to consider making i a varchar with a max size explicitly stated. Other than those two items which you may already taken care of your table looks pretty good to me.

MySQL Table Performance Guidelines:

1. Add appropriate indexes to tables. Indexes aren't just for primary/unique keys add them to frequently referenced columns.
2. Explicitly state maximum lengths. Fixed length tables are faster than their counterpart
3. Always have an id column.
4. Add NOT NULL where ever you can. The nulls still take up space
5. Know your data types. Knowledge is power and can save on performance and space

Interesting Articles:
VarChar/TEXT Benchmarks
Similar Question
Some Best Practices
Data Type Storage Requirements

The articles and some of the items I have listed may not be 100% correct or reliable so make sure you do a bit of your own research if you are interested in further tuning your performance.

A few comments:

Charset=latin1 is going to piss some people of I'd suggest charset=utf8.

I'd suggest putting a foreign key check in not only on user_id, but on to as well.

Also I'd put an index on date, as you will be doing a lot of sorting on that field.

You need to split deleted in two fields, otherwise you will not know which user has deleted the message. (deleted_by_user, deleted_by_recipient)

Note that date is a reserved word and you'll need to change it into message_date or `backtick` it in your queries.

some comments:

not bad.

i would name the table something that other people might guess out of context. so maybe private_message instead of pm.

i would be explicit on the user column names, so maybe from_user_id, and to_user_id instead of 'user_id' and 'to'

i would consider pulling out the status into a new table with status, user_id, and date - this should give you a lot more flexibility in who is doing what to the message over time.

For displaying both the receiver's inbox and the senders outbox (and being able to delete messages respectively), you will probably need more information that what you currently have encoded. I would suggest a "deleted" field for each party. (As long as this is limited to only 1 user on each end and no broadcast messages, this works. This does not scale to broadcast messages, however, which would require more than 1 table to do efficiently)

You may also want to enforce key relationships with ON DELETE and ON UPDATE:

FOREIGN KEY (user_id) REFERENCES User(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (to) REFERENCES User(user_id) ON DELETE CASCADE ON UPDATE CASCADE

The removal or modification of a user will propagate changes or deletions to the messages table.

I think you may need to add an column called Parent_Message_ID which will have the parent mail ID. So that replies can also included. If you think in future to add replies to your private messages.