Basic function database design of private message messages

This article analyzes the implementation of the basic functions of private message messages through the database level.

Project requirements: Private message function, after sending a private message to the other party, the list of people who sent or received the message will be displayed on my private message list page. Each record in the list will only display the latest message of the conversation. . Click any item in the list to enter the message conversation details page, where the details of the conversation are displayed in reverse order. At the same time, you can delete conversations on these two pages. The private message list page deletes all conversations with the other party, and the private message details page deletes a certain conversation. The conversation records are deleted unilaterally without affecting the other party's viewing.

Software environment: mysql

Having said so much, in fact, there are only a few important points in summary. First, each record in the private message list only displays the last record, and second, unilateral deletion of the conversation Record without affecting the other party's viewing. First go to the data table, and then explain it one by one.

CREATE TABLE `private_message` (
  `id` bigint(20) NOT NULL auto_increment COMMENT '主键Id',
  `user_id` bigint(20) NOT NULL COMMENT '发送者Id',
  `friend_id` bigint(20) NOT NULL COMMENT '接受者Id',  
  `sender_id` bigint(20) NOT NULL COMMENT '发送者id',  
  `receiver_id` bigint(20) NOT NULL COMMENT '接受者Id',  
  `message_type` tinyint(4) NOT NULL COMMENT '消息类型,1：普通消息 2：系统消息',  
  `message_content` varchar(500) NOT NULL COMMENT '消息内容',  
  `send_time` datetime NOT NULL COMMENT '消息发送时间',  
  `status` tinyint(4) NOT NULL default '1' COMMENT '消息状态 1：未读 2：已读 3：删除',  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;123456789101112

Create a private_message table, field description:

id：主键，自增长 
user_id： 发送者id，非真实发送者id 
friend_id： 接受者id，非真实接受者id 
sender_id：发送者id，真实的发送者id 
receiver_id：接受者id，真实的接受者id 
message_type：消息类型,1：普通消息 2：系统消息，区分消息列表，可以发送不同类型的消息内容 
message_content：消息内容 
send_time：消息发送时间 
status：消息状态 1：未读 2：已读 3：删除，标记不同消息状态，可以实现统计未读消息数，逻辑删除用户恢复等

Everyone should be depressed after seeing this. How to get two sender ids and receiver ids?

Because we consider that unilateral deletion of records will not affect the other party's viewing function, we need to insert two pieces of data with the same content when sending a private message, but we have to do some tricks on user_id and friend_id. , when inserting data twice, the user_id and friend_id of the second inserted data are swapped with the first inserted data. That is:

INSERT INTO `private_message` VALUES ('1', '121', '127', '121', '127', '1', 'hello word', '2015-09-09 10:25:43', '2');INSERT INTO `private_message` VALUES ('2', '127', '121', '121', '127', '1', 'hello word', '2015-09-09 10:26:41', '1');INSERT INTO `private_message` VALUES ('3', '127', '121', '127', '121', '1', '你是程序猿吗？', '2015-09-11 10:30:16', '2');INSERT INTO `private_message` VALUES ('4', '121', '127', '127', '121', '1', '你是程序猿吗？', '2015-09-11 10:30:59', '2');1234

In this way, our needs can be met. The first and fourth records are for 121 users to see, and the second and third records are for 127 to see. When 121 deletes the first or fourth record, it will certainly not affect 127's viewing of the second record. The first and third records! ! !

Okay, now we can handle other functional requirements.
1. My private message list

SELECT p.id, COUNT(p.id) AS message_count,p.user_id,p.friend_id,p.sender_id,p.receiver_id,p.send_time,p.message_content, u.`name` AS receiver_name,u.img_url AS receiver_image FROM (SELECT * FROM private_message ORDER BY id DESC) p INNER JOIN user u on u.id=friend_id WHERE p.user_id=121 and p.`status` !=3 GROUP BY p.friend_id ORDER BY p.id DESC limit 0,101

2. My private message list details

SELECT p.id,p.message_content,p.sender_id,p.receiver_id,p.send_time,u.`name` AS sender_name,u.img_url AS sender_image,uu.`name` AS receiver_name FROM private_message p INNER JOIN user u on u.id=p.sender_id INNER JOIN user uu on uu.id=p.friend_id WHERE p.user_id=121 and p.friend_id=127 and p.`status` !=3 ORDER BY p.id DESC limit 0,101

3. Delete the entire conversation on my private message list page

UPDATE private_message SETstatus=3 WHERE user_id=121 AND friend_id=1271

4. Me Private message list details to delete a single conversation

UPDATE private_message SET status=3 WHERE id=11

5. Get the number of unread messages from the user

SELECT COUNT(*) FROM private_message WHERE user_id=121 AND receiver_id=127 AND status=11

Of course, you can also update unread messages as read and remove deleted users from the recycle bin Recovery, sending system messages, etc. can all be achieved. This is, some students will definitely say, the data redundancy in this table design, each record is inserted twice, if there is a lot of content or when sending system messages, the table data is too large. Of course, this is only for small private message functions, it is definitely It is different from large-scale social networking websites, but we can also split the content and create a new content table. Here, the ID can be associated to reduce data redundancy. Also, this design does not involve high concurrent access! When it comes to high concurrency, it requires more complex designs and methods to solve it!

Related reading:

php chat one-to-one chat function source code

How to make the use of database indexes more efficient?

General steps and examples of designing a database

The above is the entire content of this article. If you have any questions, please feel free to contact us Leave a message in the comment area!

The above is the detailed content of Basic function database design of private message messages. For more information, please follow other related articles on the PHP Chinese website!