Geek Answers Handbook

Unified mysql table for private messages

I am trying to create a single table for private messages on a website. I created the following table, which in my opinion is effective, 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 DEFAULT '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: readanddeleted

If read = 1, the message was read by the receiver. If deleted = 1, the sender or recipient deleted the message from the sent or received mailbox. If deleted = 2, both users deleted the message to remove the row from the database table.

+5
source share
6 answers

A few comments:

Charset=latin1 , charset=utf8.

user_id, to.

date, .

, , . (deleted_by_user, deleted_by_recipient)

, date , message_date `backtick` .

+3

, - . . , varchar , . , , , , .

MySQL:

  • . / .
  • . ,
  • .
  • NOT NULL, . -
  • . - .

:
VarChar/TEXT




100% , , , .

+5

:

.

, . , , private_message pm.

i , , , from_user_id to_user_id 'user_id' 'to'

, user_id date - , - .

+1

, ( ) , , , . "" . ( 1 , . , 1 )

+1

ON DELETE 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

.

+1

I think you might need to add a Parent_Message_ID column that will have a parent mail id. So answers can also be included. If you are thinking in the future add answers to your private messages.

+1
source

More articles:


All Articles