数据库设计心得

在软件工程导论以及数据库实验课程中，我们学习了如何通过分析业务需求来构建数据库实体对象以及PowerDesigner的使用。最终通过PowerDesigner完成了本项目的数据库概念模型、物理模型的设计。以下是我们团队的数据库设计过程以及一些心得体会：

项目名称：合同管理系统

指导老师：戴牡红

小组名称：麓南匪帮

小组成员：谢英祺（PM）、陈文康、何沁泽、卢永昌、苏国超、木热阿地力·买买提江

对于一个软件系统，数据库的设计是非常重要的，数据库设计的好坏决定了以后数据好不好维护、后期需求好不好开展。

同时也决定了系统的性能：一个坏的数据库设计可能会导致一个功能点的改动涉及多张表的改动，一不小心可能就会引起数据的不一致。为了解决这些问题，在数据库设计之初就要充分考虑，以减少后期系统的维护量。

在本次的项目过程中，数据库设计分为以下几个阶段：

需求分析

概念模型设计

物理模型设计

数据库定义

在项目开展初期，我们已经完成了需求的收集与分析，并且撰写了文档化的需求分析报告、图形化的原型界面和图解化的系统用例图。

我们后续任务的开展都以这些资料作为参考和指南。

概念模型设计是整个数据库设计的关键，它通过对用户需求进行综合，归纳与抽象，形成一个独立于具体DBMS的概念模型，即概念模型的设计与某一特定的数据库无关，具有通用性和普遍性。

概念模型有很多种，我们项目使用的是自底向上的E-R图。设计的流程大致分为以下步骤：

在数据抽象过程中，我们需要根据系统的需求，提炼和抽象出实体对象并通过E-R图表示出来。

例如，在我们所设计的系统中，存在两个比较大的模块：合同模块、合同模板模块。具体见下方系统的局部用例图：

接下来的任务，便是分析这些模块分别应该对应哪些实体、应该具有哪些属性，并且将这些实体属性抽象出来，通过E-R图进行表示：

下面根据我们所抽象出的实体属性，分析思考其之间的关联性，将其联系起来，形成局部的E-R图：

例如合同与合同模板之间的关系：一个合同模板可以创建多个合同。所以合同模板对应合同应为一对多（1：n）关系：

同理，可以完成其他实体之间的关联设计。

完成局部E-R图之后，考虑各个局部之间的关系，完成全局E-R图的设计。

这里以合同模块的一部分E-R图为例进行展示（并非本项目的全局E-R图）：

逻辑模型设计的任务就是把概念模型设计好的基本E-R图转换为与指定DBMS产品所支持的数据模型相符合的逻辑结构。逻辑结构依赖于所选择的数据库的类型，例如：关系型、文档型、key-value型等。

因为本项目使用的DBMS是mysql，属于关系型数据库，所以逻辑模型我们采用的是关系模型。例如，对于上述的合同、合同模块的实体，将其转化为关系模型如下：

合同（合同id，合同名称，存储URL，合同状态，合同类型，创建时间……）；

合同模板（模板id，模板名称，存储URL，模板类型，创建时间……）；

合同字段（合同字段id，所属合同id，字段名，字段内容，修改时间……）；

合同模板字段（模板字段id，所属合同模板id，字段名……）；

其中标注有下划线的属性表示为主键

物理设计是为逻辑数据结构模型选取一个最适合应用环境的物理结构，包括每个模型如何存储、每个字段如何存储、以及每个字段存储的类型大小等。物理模型严格地按照相应的ODBC（数据库对象）进行设计，与DBMS相关联。

物理模型的设计，我们采用的是建模工具是PowerDesigner。并且在本项目中，对应的物理环境是关系型数据库mysql。所以在建模工具PowerDesigner中选取的ODBC（数据库对象）为mysql。并且根据所设计的E-R图、逻辑模型，完成系统物理模型（PDM）的设计：

模型检查需要对目前完成的模型进行数据库范式的检查，是否真正做到数据的一致性、共享性。如果不满足，则需要对模型进行修正。

例如，在物理模型的初始版本中，我们设计了这样一种表关系：

在合同表中，存在一个字段：“合同拟制人”，指向了一个确定的用户。这样的设计很显然不满足数据库设计的范式，因为一个具体的合同与一个用户相关联了。假设我们删除了某一个用户，就会导致相应的合同也被删除，这显然是不合理的。

于是我们将 “合同拟制人” 这一字段抽取出来，新添加一个中间enroll表来联系这两个实体，从而将合同表和用户表独立出来：

即使用户被删除了，只是会将enroll表中一条记录删除，具体的合同记录不会受影响。

数据库实施阶段，设计人员通过特定DBMS的sql语言，根据物理设计的结果建立数据库表和字段。

因为本项目采用了PowerDesigner设计PDM，PowerDesigner可以通过PDM自动生成相应数据库表的创建sql语句，我们只需要在DBMS中执行即可，大大减少了数据库设计人员的工作量。下图是本项目中所有的数据库表：

1. 时间类型DateTime和TimeStamp使用不合理

问题描述：在mysql中，有两种时间类型都可以精确到秒（年-月-日  时:分:秒）：DateTime和TimeStamp，分别是8字节存储和4字节存储。我们在建表时没有仔细考虑，只是简单地选择了4字节的TimeStamp类型，而没有考虑到我们真实的业务场景：合同管理系统。一般来说，合同文件应该是需要长久保存的，而4字节的TimeStamp时间类型只能存储1970-01-01 00:00:01 ~ 2038-01-19 03:14:07时间段的时间，这就导致了合同不能长久保存这一问题的出现。

问题解决：一开始我们没有意识到，也并不觉得有问题。在PDM设计完成之后交于戴老师进行审查时，戴老师一眼便观察到问题所在。并指出：涉及到与法律相关的，例如合同、保险等，一定要用DateTime类型进行存储（DateTime可以存储1000-01-01 00:00:00 ~ 9999-12-31 23:59:59时间段的时间）。最终我们也对表中与时间相关的类型进行了修改更正。

2. 表的设计不严格满足数据库设计范式

问题描述：在设计合同表时，为了表示合同是由哪位用户拟制的，我们添加了 “合同拟制人” 这一字段，指向用户表。当我们模拟删除某一用户时（在合同系统中表现为离职），如果该用户曾经拟制过某一合同，会导致该合同也会被删除。

问题解决：在合同表和用户表之间添加一个enroll表，用来联系合同表和用户表。当需要删除某一用户时，只会将enroll表中的相关记录删除，而合同记录仍会保留。

3. 表和字段的命名糟糕或者没有注释

问题描述：数据库表的命名或者字段的命名十分糟糕，有些也没有进行注释。导致过了两天之后，自己都看不懂某个表的作用、某个字段的含义，于是又召集小组会议重新讨论，费时费力。

问题解决：在之后的数据库设计中，我们不仅讨论了如何进行表的设计，也讨论了表的命名与注释，保证每个表、每个字段都能做到见名知意。

突如其来的黑客攻击！

当我们搭建好服务器环境，并在服务器上建立好数据库后，悲剧便发生了：在双十一这天，黑客无情地攻击了我们的服务器和数据库，并且删库威胁我们给他的账户打钱😭😱

导致该问题的原因就是我们数据库密码过于简单，被黑客暴力破解掉之后删除了所有的表和数据，所幸的是项目开展前期没有过多的业务数据，恢复起来也不是很难。

最终经过小组讨论决定，我们对华为云账户、服务器和数据库的密码进行修改以及重置系统和数据库，同时将数据库和后端服务分别部署在不同的服务器上，对服务器进行加密保护，并安排组员定期对数据库进行维护和备份，避免被再次被删库！

1. 严格按照数据库设计流程进行

数据库设计流程一般分为：需求分析->概念模型设计->逻辑模型设计->物理模型设计->数据库定义，其中每一环都是不可或缺、环环相扣的。

通过需求分析过程收集到的需求以及绘制的系统用例图，我们可以分析并获取到本系统中涉及到的实体以及属性，从而设计出系统的E-R图（概念模型）；

通过E-R图中的实体和属性，结合本项目的数据库类型，得到每一个实体的关系模式（逻辑模型）；

再通过每一个关系模式，构建出依赖于某一特定的DBMS的表对象（物理模型）；

最终根据物理模型，创建数据库对象。

每一个环节都依赖于前一个环节的成果，所以在进行某一环节时，必须要花费大量的时间进行讨论、设计和修改，才能保证后续环节的正常进行。

2. 扩展性的设计

项目初期业务场景数据模型是一对一，并且分析后期有可能会变成一对多。这种情况不要为了前期方便设计成一对一数据模型。这样做到后期可能得不偿失。这就是数据拓展性设计需要考虑。

这一点在我们项目中体现的十分明显：在需求分析时，我们对于合同模板和合同的关系，初始拟定的对应关系是1：1，即一个合同由一个合同模板拟定而来，一个合同模板只能对应于一个合同。但是到后续我们发现一个合同模板其实可以拟制多个合同。这就导致其对应关系变成了1：n。造成的后果就是我们需要重新添加中间联系表、重新修改所设计的数据库模型，增加了工作量。

所以在设计数据库时，一定要考虑到设计的可扩展性，即使可能造成一定的数据冗余。

3. 范式与反范式设计

范式是数据表设计的基本原则，很容易被忽视。很多时候，当数据库运行了一段时间之后，我们才发现数据表设计有问题。重新调整数据表的结构，可能需要做数据迁移，甚至可能会影响程序的业务逻辑。所以在开始设计数据库的时候，我们就需要重视数据表的范式设计。

范式给我们带来好处的同时，也伴随着一些不好的地方：按照范式的规范设计出来的表，等级越高的范式设计出来的表越多。如第一范式设计出来的表可能只有一张，再按照第二范式去设计这张表时就可能出现两张或更多张表，如果再按第三范式或更高的范式去设计这张表会出现比第二范式更多的表。表的数量越多，当我们去查询一些数据时，必然要去多表中去查询数据，这样查询的时间要比在一张表中查询所用的时间要高很多。

所以我们需要充分考虑自己项目的特点，设计数据库时，并不是一味地遵循数据库设计范式，在某些情况下我们可以允许适当的数据冗余，用这个冗余去换取操作数据时间的缩短，也就是利用空间来换取时间。把数据冗余在多个表中，在查询时可以减少或者是避免表之间的关联查询，从而提高查询效率。

4. 选择高效的设计工具

工欲善其事，必先利其器。优秀的设计工具能够使我们的工作事半功倍，甚至带来意想不到的作用。

例如设计物理模型时，我们使用了PowerDesigner工具。PowerDesigner是一种企业级的数据库设计工具，可以支持多种DBMS，是数据库设计的不二选择。并且其操作简单方便，对于我们这些新手也十分友好。在设计完PDM，之后的工作就是通过PDM物理模型来编写SQL语句，建立数据库表。令我们想不到的是PowerDesigner可以直接通过PDM导出SQL语句，最后轻松地完成了数据库表的创建，大大减少了设计开发的时间。

5. 勤于老师交流

不得不说，作为数据库专业方面的老师，戴牡红老师对于数据库的敏感程度要远远高于我们。当我们设计好PDM与其进行交流时，戴老师给予了我们很多建议：包括表字段的类型选择、中间表的定义、表与表之间的一对多或多对多关系的选择等等。与戴老师的交流对我们数据库的设计和完善过程起到了画龙点睛的作用，并且也深刻了我们对数据库设计的认知和深度。

6. 耐心十分重要

在数据库设计中，有时候因为操作不当，会导致外键的连接产生多余额外的字段，然后又要手动删除、有时因为表和字段过多，看得头昏脑胀、有时因为表和字段设计不合理，又要推倒重来。这些都是我们项目组亲身经历过的事情，确实让人感到不安和烦躁。但是在数据库设计时，这些问题都是不可避免会遇到的，这就需要作为设计者的我们拥有足够的耐心和信心，多与组内成员交流沟通，坚持把难题攻克。不要因为一时的烦躁而影响整个团队，更不要因为缺乏耐心而放弃。