Table structure design of message in PHP station

1) Add the full station notification: The information is deposited to Tb_message 2) the user opens the message or sets the information as read: Insert record to Tb_message_readlog

How do I design a stored table? Find the best solution

CREATE TABLE ' message ' (' message_id ' bigint () not NULL auto_increment COMMENT ' message id ', ' subject ' varchar ($) Not Null default ' COMMENT ' theme ', ' content ' varchar ($) NOT null default ' COMMENT ' contents ', ' type ' tinyint (1) NOT NULL DE FAULT ' 0 ' COMMENT ' msg type 0 message ', ' status ' tinyint (1) Not NULL default ' 0 ' COMMENT ' state-1 Delete 0 default ', ' Create_time ' in T (Ten) unsigned not NULL DEFAULT ' 0 ' COMMENT ' creation time ', PRIMARY key (' message_id '), key ' subject ' (' Subject ')) Engine=inn ODB DEFAULT Charset=utf8; CREATE TABLE ' message_broadcast ' (' message_broadcast_id ' bigint () not NULL auto_increment, ' message_id ' bigint (20  Not null default ' 0 ' COMMENT ' message id ', ' sender ' bigint (a) NOT null default ' 0 ' COMMENT ' sender ', ' Receiver ' bigint (20) Not null default ' 0 ' COMMENT ' receiver ', ' is_read ' tinyint (1) NOT null default ' 0 ' COMMENT ' state 0 unread 1 read ', PRIMARY KEY (' Me    ssage_broadcast_id '), key ' message_id ' (' message_id '), key ' sender ' (' sender '), key ' receiver ' (' receiver '),KEY ' Is_read ' (' Is_read ')) engine=innodb DEFAULT Charset=utf8 comment= ' message broadcast table '; 

Maybe the idea is about the same as yours, so you can meet most of the news.

1.tb_message (id,content,fromuid,touid [0 for full-site users, >0 for sending to a user])
2.tb_message_readlog (Readlogid,messageid,uid,isread)

However, if your message volume is larger, it is recommended that you divide the table processing, can be based on the user's ID to the table, such as id=123456 users send messages to Tb_message6, similar to the processing

Message table:
ID ID
Time Date
Titles Title
Text content
Unread User Unread
Receiving user receive
Sending user send

The field receive holds the message receiver's ID sequence, such as 1,2,3,4 (separated by commas).
field unread The ID sequence of the recipient who holds the unread message, and the unread initial value is also for all recipients, and the user who has read the message is removed from it.
You can use the MySQL intrinsic function find_in_set (return to the location found, starting from 1) to query.
For example, an unread message for a user with a query number of 2:

SELECT * FROM `message` WHERE FIND_IN_SET(‘2‘, `unread`);

or use full-text indexing (you need to create a full-text index of field unread, and the ID sequence is separated by spaces, such as 1 2 3 4):

SELECT * FROM `message` WHERE MATCH(`unread`) AGAINST(‘2‘);

If the data volume is larger, find_in_set may have performance problems, and it is recommended to periodically clean up messages that have already been read when the message table expires.

Table structure design of message in PHP station