- 发件人和消息的关系 (一对多)
- 消息和收件人的关系 (多对多)
pre_messages
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| sender_id | int(11) unsigned | NO | | 0 | |
| message | varchar(255) | NO | | | |
| type | varchar(50) | NO | | | |
| expires_at | datetime | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| deleted_at | datetime | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
pre_message_recipients
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| recipient_id | int(11) unsigned | NO | | 0 | |
| message_id | int(11) unsigned | NO | | 0 | |
| status | tinyint(1) unsigned | NO | | 0 | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| deleted_at | datetime | YES | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
设计应该都会设计,就怕数据量大的时候,查询和分发是有点技术难度
按照主键 id 和关联 id 分表,一般的业务查询都能满足