I am trying to produce a single table web hosting texting online. I produced the next table that we think is efficient however i would actually 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 DEFAULT '0', PRIMARY KEY (`id`) FOREIGN KEY (user_id) REFERENCES User(user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
I've 2 posts that determine the status from the message:
read = 1, the content continues to be read through the receiver. If
deleted = 1, either the sender or even the receiver erased the content in the sent or received mailbox. If
deleted = 2 both customers erased the content, because of this remove the row in the database table.
i'd title the table something which others might guess from context. so perhaps private_message rather than pm.
i'd be explicit around the user column names, so perhaps from_user_id, and also to_user_id rather than 'user_id' and 'to'
i'd consider tugging the status right into a new table with status, user_id, and date - this will provide you with a much more versatility in who's doing things to the content with time.
For exhibiting both receiver's mailbox and also the senders outbox (or being able to remove messages correspondingly), you'll most likely require more information that that which you presently have encoded. I recommend a "erased" area for every party. (As lengthy because this is restricted to only one user on each finish with no broadcast messages, this works. This doesn't scale to broadcast messages, however, which may require a lot more than 1 table to complete effectively)
You may even wish to enforce key associations with
ON DELETE and
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 the user will propagate changes or deletions towards the messages table.
A couple of comments:
Charset=latin1 will piss many people of I'd suggest
I'd suggest placing a foreign key sign in not just on
user_id, but on
Also I'd put a catalog on
date, because you will do lots of sorting on that area.
You have to split erased in 2 fields, otherwise you won't know which user has erased the content. (
date is really a reserved word and you will need to change it out into
`backtick` it inside your queries.