具有用户已读状态功能的公告(1):数据库设计

青旅半醒 2021-11-10 15:46 540阅读 0赞

### 文章目录 ###

*   *   *  1 需求
         *  2 需求分析
         *  3 方案设计
         *   *  3.1 方案一
             *  3.2 方案二
             *  3.3 方案三
         *  4 数据库表设计
         *  5 思考

### 1 需求 ###

设计一个公告功能，要求可以让用户知晓已读未读状态

### 2 需求分析 ###

公告,主要字段为标题和内容,一般是对于面向所有用户的，通常只需要一张表来保存。  
消息，除了包含标题和内容外，还需要包含用户标识，从而实现用户对于消息读取状态的判断。  
需求是既要有面向所有用户的功能，同时还需要有像消息一样的用户读取状态的标识

### 3 方案设计 ###

#### 3.1 方案一 ####

只用一张表保存公告信息

只用一张表记录公告信息，每条公告只需记录公告的主体内容即可，这样可以大大节省数据库开销  
可能出现的问题: 用户无法从服务端(数据库层)获取公告消息的读取状态；如果将公告的读取状态缓存至本地，则在多应用端(iOS/Android/PC web端等等)切换的时候，原来在一个客户端已经读取的公告又重新显示为未读，这样的用户体验较差，甚至是属于 bug。

总结: 这种方案不合理

#### 3.2 方案二 ####

公告(数据库)表中包含用户标识，就像消息表一样设计

这样的设计可以实现上述需求，但是数据库的压力会非常大，会出现以下场景: 用户规模在 10亿，发布一条公告，为了保存每个用户对于公告的读取状态，需要在公告表中同时插入 10亿条数据，不仅数据库写操作压力大，而且还对数据库的空间造成巨大的浪费

总结: 这种方案不合理

#### 3.3 方案三 ####

公告信息用一张表来保存，再设计一张表将公告和用户关联起来

分析: 后台管理员发布一条公告，则会保存再公告表中；用户读取这条公告时，则会在公告与用户的关联表中插入一条数据，表明这条公告被这个用户读取了，从而实现了获取用户对于公告读取状态的反馈；由于是用户读取公告时才会对数据库关联表中插入数据，而非在创建公告时即将所有用户信息插入表中，从而缓解了数据库的写操作压力

总结: 这个方案可行

### 4 数据库表设计 ###

根据**方案三** 进行数据库表设计

/*==============================================================*/
    /* Table: notice */
    /*==============================================================*/
    drop table if exists notice;
    create table notice
    (
       id                   bigint unsigned not null auto_increment comment 'id,主键',
       title                varchar(50) not null default '' comment '标题',
       content              varchar(200) not null default '' comment '内容',
       publish_flag         tinyint(4) not null default 0 comment '发布标记,0:未发布(默认),1:已发布',
       insert_time          datetime not null default CURRENT_TIMESTAMP comment '创建时间',
       insert_operator      bigint not null default 0 comment '添加人用户 id',
       insert_identity      tinyint(4) not null default 1 comment '添加人身份标识,0:前台用户;1:后台用户(默认)',
       update_time          datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
       update_operator      bigint not null default 0 comment '修改人用户 id',
       update_identity      tinyint(4) not null default 1 comment '修改人身份标识;0:前台用户;1:后台用户(默认)',
       versions             int not null default 1 comment '版本号(默认1);用于更新时对比操作',
       del_sign             tinyint not null default 0 comment '是否逻辑删除;0:不删除(默认);1:逻辑删除;所有查询sql都要带上del=0这个条件',
       primary key (id)
    )
    ENGINE = INNODB DEFAULT
    CHARSET = UTF8MB4;
    
    alter table notice comment '公告';
    
    /*==============================================================*/
    /* Table: notice_user */
    /*==============================================================*/
    drop table if exists notice_user;
    create table notice_user
    (
       id                   bigint not null auto_increment comment 'id,主键',
       notice_id            bigint unsigned not null default 0 comment '公告 id',
       user_account_id      bigint unsigned not null default 0 comment '用户 id',
       read_flag            tinyint(4) not null default 1 comment '已读标记表,0:未读;1:已读(默认)',
       insert_time          datetime not null default CURRENT_TIMESTAMP comment '创建时间',
       insert_operator      bigint not null default 0 comment '添加人用户 id',
       insert_identity      tinyint(4) not null default 1 comment '添加人身份标识,0:前台用户;1:后台用户(默认)',
       update_time          datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
       update_operator      bigint not null default 0 comment '修改人用户 id',
       update_identity      tinyint(4) not null default 1 comment '修改人身份标识;0:前台用户;1:后台用户(默认)',
       versions             int not null default 1 comment '版本号(默认1);用于更新时对比操作',
       del_sign             tinyint not null default 0 comment '是否逻辑删除;0:不删除(默认);1:逻辑删除;所有查询sql都要带上del=0这个条件',
       primary key (id)
    )
    ENGINE = INNODB DEFAULT
    CHARSET = UTF8MB4;
    
    alter table notice_user comment '公告-用户已读标记表';

### 5 思考 ###

问题一: 有必要对公告进行已读和未读状态的设计吗？  
这个问题，产品说了算

问题二: 用户在查询公告列表的时候，如何将每一条公告的读取状态也返回其中?  
这个问题，且看下回分解

[具有用户已读状态功能的公告(2):用户查询公告列表，同时包含读取状态][2]

个人公众号:**404Code**,分享半个互联网人的技术与思考，感兴趣的可以关注.  
![404Code][]

[2]: https://blog.csdn.net/Mrqiang9001/article/details/98340623
[404Code]: /images/20211109/42384de833074256a0c78f1036b1d705.png