数据库设计：透过现象看本质

最新推荐文章于 2022-11-11 10:59:47 发布

横竖撇折点

最新推荐文章于 2022-11-11 10:59:47 发布

阅读量310

点赞数

分类专栏：全栈工程师实战：从 0 开发云笔记

本文链接：https://blog.csdn.net/qq_41089021/article/details/108728387

版权

全栈工程师实战：从 0 开发云笔记专栏收录该内容

32 篇文章 1 订阅

订阅专栏

前言

需求分析完成后就确定了产品的功能结构，在此基础上进行数据库设计，将每个具体的功能数据化，为接口文档的设计和代码设计做铺垫，本篇将带领大家完成 Keler 云笔记项目的数据库设计。

设计原则 For what

数据是所有业务的基础，没有数据，业务也就没法进行下去了；数据更是业务的灵魂，所有的业务操作都是为了得到期望的数据，没有了数据，业务操作也就没有了意义。因此在编写代码前，需要先将数据设计完毕，一个好的数据库设计应该有这样的特点：数据完整、操作灵活、有可扩展性。

数据完整

数据完整是对数据库设计的基本要求，要求存储数据的精确性、可靠性，要求存储的一组数据有意义。

数据有意义包含：

字段名有意义，能根据字段名知道该字段要存储什么样的数据，必要时要加上合适的字段说明；
字段的存在有意义，该字段的存在是必要的，是其他字段无法替代的；
数据表中的所有字段组合在一起有意义，能实现一个或多个业务的需要。

好的表设计，往往查询一到两个表就能得到一个业务所需要的数据。如果你设计出来的数据库在使用时发现：一个业务要查询三四张表，那么就要认真考虑一下你是不是来给程序猿们作对的。

数据完整还包括约束的完整性和用户定义的完整性，如：有适当的主键和索引、有适当的冗余字段、有适当的存储过程和清理机制等。

操作灵活

操作灵活是指数据间耦合度低，使用起来灵活方便，不会出现一个表又当爹又当妈的情况，具体说明我总结了如下几点：

将不是强关联的数据分开存储
将高频次修改的数据和低频次修改的数据分开存储
将高访问量的数据和低访问量的数据分开存储
将记录数据和业务数据分开存储
将历史数据和应用数据分开存储

当数据表之间的耦合度降低到合适的程度后，既能保证数据的完整性，又能避免多个业务交叉操作同一张表的场景，降低了业务逻辑的复杂度和出错的几率。

有可扩展性

扩展性是指设计数据库时不要仅仅只是为了满足当下要求，要预见到未来可能出现的变化或者使用中可能出现的问题，一个设计经验比较足的 DBA 通常会考虑到这些：

设计表时考虑下该表的数据在删除时有没有需要找回数据的场景，如果有，就设计成可以软删除的表（加一个 isDelete 的字段）
如果一个表数据可能会有多人修改，添加字段分别记录最后一次修改人、最后一次修改时间，用以在出现意外时辅助定位原因
如果有登录注册功能，设计一个登录日志表、注册日志表，用以出现相关问题时的定位及后续可能使用的日活、日增长量等统计工作

数据库设计 How we do it

明确好设计原则后，我们来做云笔记项目的数据库设计。数据库设计工作往往是在需求分析完毕之后开始根据功能树和业务逻辑关系来进行的，下图是在上一篇需求分析中完成的功能树：

功能树

仔细分析功能树，发现它已经将功能模块分为了用户功能、管理员功能两大类，其中：用户功能又可细分为用户信息和内容区（也就是笔记相关）两类。那么我们在设计数据库时也可以按照用户信息、笔记、管理员功能这三部分来做。

用户信息 Users

从功能树上可以看到，与用户信息相关的功能就是登录注册、用户名片相关。按照我们上面说的设计原则，可以将其分为：

用户基本信息表
存储用户账号、密码、类型标示等基本信息
修改频率低
主要用于登录和注册功能
用户名片表
存储用户昵称、头像等名片信息
修改频率高
主要用于用户名片相关的功能
邮件发送记录表
存储邮件验证码发送记录
单独的业务逻辑，修改频率低
用于用户邮箱注册、使用邮箱验证码登录和室友邮箱验证码找回密码的功能

具体设计如下。

用户基本信息表 user_info

create table user_info
(
 id int auto_increment primary key,
 type int not null comment '用户类型',
 password varchar(200) null comment '密码',
 email varchar(200) not null comment '注册邮箱',
 createTime datetime null,
 status int null comment '用户账号状态，如账号锁定、未激活等',
 isDelete int null,
 updateTime datetime null,
 updateUserId int null
)comment '用户信息表';
create index user_info_index_email on user_info (email);
create index user_info_index_status on user_info (status);
create index user_info_index_type on user_info (type);

用户名片表 user_card

create table user_card
(
 userId int primary key,
 nickName varchar(200) null,
 email varchar(200) null comment '展示在名片上的邮箱地址',
 imgUrl varchar(200) null comment '头像原图',
 thumUrl varChar(200) null comment '头像缩略图',
 createTime datetime null,
 updateTime datetime null,
 updateUserId int null
)comment '用户名片表';

邮件发送记录表 email_log

create table email_log
(
 id int auto_increment
  primary key,
 type int not null comment '邮件发送类型,不能为空',
 email varchar(50) not null comment '收件人，不能为空',
 title varchar(200) not null comment '邮件标题，不能为空',
 content varchar(500) not null comment '邮件内容，不能为空',
 code varchar(50) null comment '验证码',
 result varchar(500) null comment '发送结果描述，如：发送失败的原因等',
 statusCode int not null comment '发送状态',
 createTime datetime not null comment '发送时间',
 isUsed int null comment '验证码是否已使用'
);
create index email_log_index_email on email_log (email);
create index email_log_index_type on email_log (type);

在 email_log 的设计时，要考虑到灵活性：对于验证码邮件来说，重要的是里面的验证码，因此可以为验证码单独设计一个字段保存。这样，在邮件内容里面有验证码，是拼接好的完整邮件内容，以展现给用户；code 字段单独存储一个验证码，方便系统取用。

笔记 Notes

从功能树上看到，与笔记相关的业务有：笔记本、笔记内容的增删改查，因此数据表可以分为：

笔记本表
存储笔记本名称、所属用户、笔记本说明等与笔记本相关的信息
数据量小，适合频繁读写
用于显示用户笔记本列表的功能
笔记表
存储笔记内容、笔记类型等笔记详情
数据量大（一篇笔记可能有几千、几万字）
用于笔记内容修改、预览等功能

具体设计如下。

笔记本表 notes_info

create table notes_info
(
 id int auto_increment primary key,
 userId int null comment '用户Id',
 title varchar(200) null comment '标题',
 subTitle varchar(200) null comment '副标题',
 createTime datetime null comment '创建日期',
 noteNumber int null comment '笔记数量',
 sort int null comment '排序',
 isDelete int null,
 updateTime datetime null,
 updateUserId int null
)comment '笔记本';
create index notes_info_index_userId on notes_info (userId);

笔记表 note_info

create table note_info
(
 id int auto_increment primary key,
 userId int null comment '用户ID',
 notesId int null comment '笔记本Id',
 title varchar(200) null comment '标题',
 contentMD mediumtext null comment 'MD内容',
 content mediumtext null comment '内容',
 sort int null comment '排序',
 createTime datetime null,
 isDelete int null,
 updateTime datetime null,
 updateUserId int null
)comment '笔记';
create index note_info_index_notesId on note_info (notesId);
create index note_info_index_userId on note_info (userId);

在这两张表的设计中，有一点需要注意：不要使用外键，外键的关系在应用层实现。

这么说是因为：

使用外键会降低系统性能：使用外键的表在 Insert、Update、Delete操作时都要额外去查询对应的表中相关数据，增加数据库负担、增加系统响应时间、降低系统性能
使用外键更容易造成数据库死锁：由于每次操作都要额外查询对应的表，就需要多获取一个锁，在高并发的场景中更容易死锁，引起并发问题
使用外键不利于系统的扩展性：外键、触发器、存储过程等可以在应用层控制的逻辑尽量在应用层控制，这样在数据迁移、数据扩容的过程中工作量会大大减少

另外，如果频繁使用外键、触发器、存储过程等数据库的判断逻辑，也就意味着对数据库的性能安全性需要更大的依赖，可能要专门的 DBA 维护数据库，无形中增加了小企业小项目的维护成本和复杂度。

管理员 Administrator

通过功能树可以看到，在本项目中，管理员功能主要是可以查看用户统计信息，为了实现用户增长量和用户活跃度的统计，设计注册日志表和登录日志表，具体设计如下。

注册日志表 register_log

create table register_log( 
id int auto_increment primary key,
userId int comment '用户ID', 
ip varchar(50) comment '注册IP', 
createTime datetime comment '注册时间' 
) comment '注册日志表';

登录日志表 login_log

create table login_log( 
id int auto_increment primary key,
userId int comment '用户ID', 
ip varchar(50) comment '登录IP', 
createTime datetime comment '登录时间' 
) comment '登录日志表';

你会发现，两张表的数据结构一致，但是因为如下原因，将其分开存储：