后端实战-数据库表的设计 #

前言 #

数据库设计，在整个软件工程的流程中，是非常重要的一环。前期的铺垫，都是为了后期开发项目时，能够顺畅一些。包括后续的迭代，都需要在数据库表设计中留下可扩展的可能性。

我们整个「记账本」项目有两个核心模块，第一个是「用户模块」，另外一个是「账单模块」。并且配置了三张数据表，表虽然不多，但是基础功能都有涉及到，所以有后续的需求，可以举一反三，进行拓展。

现在我以一个产品经理提出需求的过程，推演整个数据库设计的思路。

数据库设计的原则 #

1、设计数据库的时间要充沛 #

数据库是用户需求的映射，设计初衷要以用户需求为中心，尽量与用户保持沟通，这里的用户可以指产品经理，亦或是提需求的人。如果遇到需求不明确的地方，设计表时就要事先预留出可变通的字段，这便是所谓的“未雨绸缪”。

2、多考虑一些性能和优化 #

提前预判哪些数据将会是比较大的，对于这类数据的表结构，设计的时候往往是粗粒度的，以达到用最少的表，最弱的关系去存储大量的数据。

3、添加必要的（冗余）字段 #

像“创建时间”、“修改时间”、“备注”、“操作用户IP”和一些用于其他需求（如统计）的字段等，在每张表中必须都要有，不是说只有系统中用到的数据才会存到数据库中，一些冗余字段是为了便于日后维护、分析、拓展而添加的，这点是非常重要的，比如黑客攻击，篡改了数据，我们便就可以根据修改时间和操作用户IP来查找定位。

4、设计合理的表关联 #

表与表之间的关系复杂的情况下，建议采用第三张映射表来维系两张张复杂表之间的关系，达到降低表之间的直接耦合度。

若多张表涉及到大数据量的问题，表结构尽量简单，关联也要尽可能避免。

接下来我们便来设计本次项目所需要用到的数据表。

用户表 user #

整个记账本项目是一个面向多人注册的 C 端项目，所以需要一个「个人中心」模块，用户可以设置自己的头像、个性签名。但是用户名不能修改，因为用户名是用于登录使用，目前系统没有手机验证码的概念，所以这里用户名相当于用户的唯一标示。在编写注册接口的时候，也会先检验数据库中是否存在相同的用户名。这里需要再给一个创建时间，用户后续扩展业务功能，如用户使用系统的年限、根据时间划分登记等等需求。

根据上述需求分析，我们可以设计如下 user 表：

• id：id 字段设置为自增字段，避免认为操作导致数据重复。设置为自增之后，每次往 user 表里新增数据，都会默认 id 加 1，就算你删除了前面的数据，是不会影响到 id 的自增。后续我们通过鉴权，生成用户信息。账单表的存储，都会以用户作为出发点。相当于 A 用户，存储自己的账单时，都会将 A 用户的 id 带上，相当于这份账单标记了用户 A 的 id 。

• username：用于存储用户登录名称。

• password：用于存储用户登录密码。

• signature：根据上图，我们还需要一个 signature 字段作为个性签名。

• avatar：用于存储用户头像信息。

• ctime：用于存储用户创建时间字段。

新建项目数据库 #

我们打开 DBevaer 选择本地数据库 localhost，新建一个 juejue-cost 用于本次项目的开发数据库。

创建好之后，在 juejue-cost 下创建一张 user 表，表属性就是我们上述提到的六个字段，如下所示：

创建的过程不再赘述，可前往第 4 章，查看创建表的相关工作。这里要注意的是，一定要给表设置好主键，我们这里设置 id 为主键。设置主键点击「约束」，在面板右键选择「新建约束」，选择要设置的属性。

账单表 bill #

上图为账单首页，首页顶部有两个信息，代表当月「总支出」和「总收入」。一笔账单记为一项，每一项账单包括几个关键属性，分别是账单的类型（收入或指出）、账单的种类（服饰、交通、奖金等）、账单的金额、账单的日期时间、账单的备注（当种类无法描述清楚时使用）。

根据上述的需求分析，我们可以这样设计 bill 表：

• id：每张表都需要一个主键，我们照旧，用 id 作为「账单表」的属性。

• pay_type：账单类型，账单无非就是两种类型，支出和收入，我们用 pay_type 作为类型字段，这里约定好 1 为支出，2 为收入。

• amount：账单价格，每个账单都需有一个价格属性，表示该笔账单你消费或收入了多少钱。

• date：账单日期，日期可自由选择，以时间戳的形式存储。

• type_id：账单标签 id，如餐饮、交通、日用、学习、购物等。

• type_name：账单标签名称，如餐饮、交通、日用、学习、购物等。

• user_id：账单归属的用户 id，本小册制作的是多用户项目，相当于可以有多个用户来注册使用，所以存储账单的时候，需要将用户的 id 带上，便于后面查询账单列表之时，过滤出该用户的账单。

• remark：账单备注。

新建账单表 #

我们在上述 juejue-cost 的基础上，新建一张 bill 表，如下所示：

保存并执行，bill 表生效。

标签表 type #

一开始我是想在前端把标签定死，比如服饰、交通、医疗等等这类账单种类，写成一个静态的对象，供前端项目使用。但是这样做有一个不好的地方，后续如果希望让用户自己添加自定义标签，就不好拓展。所以这里我们在数据库中设置一张 type 表，让用户可以灵活的设置属于自己的自定义标签。

我们需要给标签表设置下列属性：

• id：唯一标识，设为主键。

• name：标签名称，如餐饮、交通、日用、学习、购物等。

• type：标签类型，默认 1 为收入，2 为支出。

• user_id：保留字段，设置该标签的用户归属，默认 0 为全部用户可见，某个用户单独设置的标签，user_id 就是该用户的用户 id，在获取列表的时候，方便过滤。

新建标签表 #

我们在数据 juejue-cost 中新建 type 表，如下所示：

我们的简表工作，基本上完成了。

总结 #

一套系统，最重要的就是用户鉴权，只要用户鉴权实现之后，围绕用户可以展开多个模块的增删改查，目前就是设置一张账单表进行增删改查的演示，后续的拓展可以根据自己的需要来，比如我还可以再加一张表，叫 note 表，专门用于日记的记录，此时也就是对 note 表的增删改查。我一再强调举一反三的能力，是因为没有人会把所有的东西一五一十的告诉你，多数情况下需要靠自己领悟和拓展，否则你永远只能停留在初级。