4. 数据库模型设计（下）：通知偏好设置表、已读状态表、附件表的ER图与SQL实现

好，咱们接着往下聊。上一节我们把通知主表和用户表的核心关系理清了，这一节要处理的是三个“配角”——但别小看它们，少了任何一个，系统都会出大问题。

我个人习惯把这三张表称为“三件套”：通知偏好设置表管的是“用户想不想收通知”，已读状态表管的是“用户看了没有”，附件表管的是“通知里带了什么文件”。咱们一个一个来拆。

4.1 通知偏好设置表：用户说了算

你想想看，如果用户不想收邮件通知，你硬给他发，那跟垃圾邮件有什么区别？所以偏好设置表，说白了就是给用户一个“开关”。

我在项目中遇到过最头疼的事，就是用户投诉“为什么我关了通知还收到推送？”查了半天，原来是偏好表里没有缓存用户的最新设置，每次都是实时查配置中心，结果配置中心挂了，默认全开。嗯，这里要注意：偏好设置一定要有默认值，而且缓存要跟上。

这张表的ER图核心字段如下：

  • user_id：用户ID，外键关联用户表
  • notify_type：通知类型（邮件、短信、站内信、App推送）
  • is_enabled：是否启用（布尔值）
  • quiet_start_time / quiet_end_time：免打扰时段（可选）
  • updated_at：更新时间

这里有个小技巧：免打扰时段我建议用整型存储分钟数，比如22:30存为1350（22*60+30），这样比较大小和做范围查询都很快。

核心设计思路：偏好设置表是一对多的关系——一个用户可以有多种通知类型的偏好。不要搞成每个用户一条记录然后塞一堆字段，那样扩展性太差。

SQL实现如下：

CREATE TABLE `notification_preferences` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `notify_type` varchar(20) NOT NULL COMMENT '通知类型：email/sms/in_app/push',
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用：1启用 0禁用',
  `quiet_start_minute` int(11) DEFAULT NULL COMMENT '免打扰开始时间（分钟数，0-1439）',
  `quiet_end_minute` int(11) DEFAULT NULL COMMENT '免打扰结束时间（分钟数，0-1439）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_type` (`user_id`, `notify_type`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知偏好设置表';

避坑指南：我曾经在项目里忘了加唯一索引，结果用户重复设置了同一种通知类型，代码里又没做去重，导致查询时出现多条记录，程序直接报错。所以uk_user_type这个唯一索引一定要加上。

4.2 已读状态表：谁看了我的通知？

已读状态表，说白了就是记录“哪个用户读了哪条通知”。这听起来简单，但数据量一大就头疼。你想想看，一个公司几千人，每人每天收几十条通知，一年下来就是几千万条记录。

我建议这张表的设计要轻量、可归档。核心字段就这几个：

  • notification_id：通知ID
  • user_id：用户ID
  • is_read：是否已读（布尔值）
  • read_time：读取时间（可为空）

为什么要把已读状态单独拆一张表，而不是直接挂在通知主表里？因为一条通知要发给N个用户，如果每个用户的状态都塞到通知主表里，那字段数量就爆炸了。说白了，这就是典型的多对多关系的中间表。

SQL实现：

CREATE TABLE `notification_read_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `notification_id` bigint(20) NOT NULL COMMENT '通知ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `is_read` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已读：0未读 1已读',
  `read_time` datetime DEFAULT NULL COMMENT '读取时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_noti_user` (`notification_id`, `user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_notification_id` (`notification_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知已读状态表';

注意：这里read_time我允许为空，因为用户还没读的时候，这个字段就是NULL。不要用'0000-00-00'或者'1970-01-01'这种“假空值”，查询时容易出幺蛾子。

4.3 附件表：通知里的“干货”

通知里带附件，这事儿太常见了——比如审批通过的PDF、会议纪要的Excel、项目截图等等。附件表的设计，我建议遵循一个原则：只存元数据，不存文件内容。

文件内容放哪里？放对象存储（OSS、S3、MinIO）或者本地文件系统。数据库里只存路径和基本信息。

核心字段：

  • notification_id：所属通知ID
  • file_name：原始文件名（用户上传时的名字）
  • file_path：存储路径（相对路径或完整URL）
  • file_size：文件大小（字节）
  • file_type：MIME类型（如application/pdf）
  • sort_order：排序序号（多个附件时有用）

我记得有一次线上事故，就是因为附件表里没有限制file_name的长度，用户上传了一个超长文件名的PDF，结果前端展示时直接撑爆了布局。所以这里我建议file_name用varchar(255)就够，如果文件名超长，后端要主动截断。

SQL实现：

CREATE TABLE `notification_attachment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `notification_id` bigint(20) NOT NULL COMMENT '通知ID',
  `file_name` varchar(255) NOT NULL COMMENT '原始文件名',
  `file_path` varchar(500) NOT NULL COMMENT '存储路径',
  `file_size` bigint(20) NOT NULL DEFAULT 0 COMMENT '文件大小（字节）',
  `file_type` varchar(100) DEFAULT NULL COMMENT '文件MIME类型',
  `sort_order` int(11) NOT NULL DEFAULT 0 COMMENT '排序序号',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_notification_id` (`notification_id`),
  KEY `idx_file_type` (`file_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知附件表';

小建议：file_path我建议存相对路径，不要存完整的http://地址。这样以后迁移存储服务时，只需要改一个配置项，不用动数据库里的数据。这个坑我踩过，当时把所有附件路径都写死了带域名的URL，后来换CDN供应商，改数据改到怀疑人生。

4.4 三张表的ER关系总结

咱们把这三张表和通知主表的关系捋一捋：

表名 关联主表 关系类型 核心作用
notification_preferences 用户表 一对多 控制用户接收通知的方式
notification_read_status 通知主表 + 用户表 多对多中间表 记录每个用户对每条通知的阅读状态
notification_attachment 通知主表 一对多 存储通知附件的元信息

说白了，这三张表就是通知系统的“三驾马车”。偏好设置决定了“发不发”，已读状态决定了“看没看”，附件决定了“带没带”。少了任何一个，你的通知系统都是瘸腿的。

下一节咱们开始写业务逻辑代码，到时候你会看到这些表是怎么被CRUD操作串联起来的。嗯，先消化一下今天的内容，有问题随时翻回去看ER图。

← 上一章 📖 返回目录 下一章 →