数据库设计（2）_逻辑结构设计

逻辑结构设计是将概念模型转换成逻辑模型的过程，也就是将E-R图中的实体、关系、属性转化为DBMS所支持的数据结构的过程，关系型数据库的数据结构为：表。

这个过程可以使用一些CASE工具，比如：ROSE、ER-WIN、Power Designer等。

一、创建表

1.1、实体的描述

在创建实体表的时候，按照E-R模型中实体及实体的属性，一个实体建立一个表，属性作为表的字段。可以参考以下的属性分类，进行详细、必要的实体属性描述。

（1）标识属性
标识实体的属性，如用户实体用户名，如果找不到就自行编号，这是实体完整性所必须的；

（2）一般属性
实体自身的基本属性，如用户实体的密码、联系方式等；

在定义一般属性时，需要注意属性自身的分类，不同的属性分类，结合业务规则，可能会产生不一样的表结构设计：
简单属性：由单个元素构成的属性，比如：邮编；

复合属性：由多个元素构成的属性，比如：姓名（FIRST_NAME、MIDDLE_NAME、LAST_NAME）、地址（江苏省南京市玄武区北京东路63#）。
根据业务需要，可以把复合属性拆成多个简单属性，比如：IP（192.168.0.1），就可以用1个bigint、一个处理过的int、或4个tinyint来表示；

单值属性：一行纪录只有一个值的属性，比如：用户实体的身份证号码；

多值属性：一行纪录有多个值的属性，比如：公司实体的电话（12345678、23456789、34567890），每个公司都可能有多个电话，这时，可以将电话属性抽象成一个实体，按照下面1:N的关系来描述，即将公司编号（标识属性）当成电话实体的一般属性；

派生属性：从某个或某几个属性可以派生出来的属性，比如：用户实体的年龄可以从出生日期属性中派生出来，通常派生属性不是必须的。但有时也可以一用，比如：地址字段比较长，如果想要对其进行检索，建立索引的话太宠大，这时可以派生SEARCH_CODE属性，比如：用地址的汉字首字母、五笔码等；

（3）分类属性
实体的分类，可以理解成特殊的一般属性，如用户实体性别属性，可分为男、女；

（4）瞬态属性
实体在某个瞬间或某个时间段内的属性值，也可以理解成特殊的一般属性。

瞬态属性与事实的关系非常诡异，关于事实的概念在下面会讲到。
A)、在事实中一定要注意瞬态属性的记载，比如：商品的单价，在商品销售纪录中需要记载，因为单价是会变的，应该记载售出时的单价；

B)、有的时候，需要单独的事实来记载实体瞬态属性的变化过程，比如：1号单价
为1元、2 号变为1.5元、3号变为1.2元（也许一天内就会有多次变化），而不是到当
天的商品销售事实表中去翻查当天的销售单价；

C)、有的时候事实发生了，会带来实体的瞬态的变化，比如：商品销售事实会影响，商品的最后售出时间、最新累计销售量等属性；

（5）行集属性
行集，顾名思义，就是多行。通常，行集属性用于表达多对多关系的属性，如用户和角色关系，有一个权限属性列表；

（6）关联属性
同样，关联属性也用于描述多对多的关系，但这里的关系通常没有属性，关联属性用来描述实体与实体间发生关系的状态，这样在对实体进行删除的时候，就可以避免到事实表中去检查实体有无被使用。因为被使用的实体是不能被物理删除的，会破坏实体的参照完整性（可通过外键实现参照完整性）。

通常，不对系统中已与其他实体发生关系的实体的某行进行物理地删除，只是进行逻辑删除：改变其分类属性，如：是否已删除(IS_DELETED)、状态(STATUS)；

如果用户新建的实体与已被逻辑删除的实体重名时，可以给出页面提示，是否“激活”已存在的实体，当然，也可以不作提示直接新建，通常情况下，影响不大。

1.2、关系的描述

关系不同于实体（一个实体一个表），通常，只有多对多的关系才需要建立一个表。对于关系的多样性，如何对关系进行逻辑结构设计：

（1）1:1的关系(一对一)
1对1的关系最为复杂，通常不需要建立关系表，那么实体间的关系如何建立？

首先，看一下什么是强实体、弱实体？
强实体：不依赖其他实体主键的实体；
弱实体：依赖其他一个或多个实体主键的实体；

以用户与汽车实体为例：
a)如果要求每个用户必须配一辆车，而汽车不一定配给用户，那么用户是弱实体，汽车是强实体，此时将汽车的标识属性作为用户的一般属性存放；

b)如果要求每个用户必须配一辆车，而汽车也一定配给某用户，这时用户、汽车都为弱实体，那么，此时可以考虑将两个实体合并为一个实体，因为双方必须为1:1的关系，不可能出现1:0的关系，当然并不一定非要合并实体，详见《数据库设计（4）逻辑结构设计常用技巧》中，关于使用实体还是使用属性的分析；

c) 如果每个用户不一定配车，而汽车也一定配给某用户，这时父子关系的建立是随意的，无论是用户引用汽车编号、还是汽车引用用户编号，作为自己的一般属性都是可以的。

（2）1:N的关系（一对多）
在N方的实体中用一般属性来刻画1方的实体的标识属性，比如：一个用户可以在银行开多个账户，账户实体中就包含着用户的身份证号（用户的标识属性）；

（3）M:N的关系（多对多）
这时需要建立一个关系表，比如：用户与角色，无法将关系属性作为某个实体的属性存放。上面的行集属性、关联属性就是对实体多对多关系的描述。

1.3、数据完整性

数据完整性通过约束来实现，在前面的E-R模型中已进行了的初步定义，这里是把键和域的定义落实到表中。通常约束在创建实体、关系表的时候一并建立。

数据完整性可分为以下几种：
（1）实体完整性
主键约束用于实现实体完整性，它用于标识一个实体（标识属性），不可以为NULL，对于可为NULL的唯一属性，可定义成唯一键；

（2）参照完整性
外键约束用于实现参照完整性，以保持主从表之间的数据的一致性。当然，也可以通过关联属性或触发器等方式来实现参照完整性；

（3）用户定义完整性
有时，也叫做业务规则。因为用户定义完整性基本是源于业务规则的，通常包括：UNIQUE约束、CHECK约束、NULL约束。

在ANSI-SQL中，主键、外键、唯一健、CHECK、NULL被定义为5个基本的约束，但有的DBMS中（比如SQL SERVER，在sysconstraints中可查看到），将默认值定义为约束，NULL却不作为约束，当然这也没什么好去争议，只是DBMS的实现方式而已。

在这些约束当中，有些是列级的约束，即只能对单列定义，比如：NULL约束、CHECK约束，有些既可以是列级约束也可以是表级约束，即可以对单列定义，也可以对多列定义，比如：主键、外键、唯一键约束

1.4、事实

回头看一下数据模型的三要素：数据结构、数据操作、数据完整性，不难发现，数据操作尚未得到体现，而事实就是实体或关系属性的变化、以及系统中以实体和关系为基础展开的业务过程的纪录。也就是数据操作的纪录。

对于事实的发现，可以参考概要设计阶段的数据流图。

事实分可简单分为系统日志和业务事实，像记叙文一样，事实至少要包括六要素：时间、地点、人物、事情的起因、经过、结果，表示什么时间、什么人、在哪里，干了什么事。

（1）系统日志
是指系统自身运行状况的纪录，与业务不相干（做法类似操作系统的日志），所以，有时系统日志的事实是可选的。但如果记录这样的事实，对于做用户行为分析、以及系统自我的改进是很有意义的。

（1）正常：系统日常事务的纪录，比如登入、退出；
（2）警告：非正常操作的记录，比如非正常退出、数据越界操作；
（3）错误：记录错误事件的发生（程序名、错误内容），有时，错误级别很高的时候，比如，程序直接崩溃，这时错误日志往往只能到应用程序后台去查看了；

（2）业务事实
是指系统业务数据的纪录，对业务事实的发现，可采用如下步骤：
（1）对业务类型进行分类：大类（比如：到银行存款）、小类（比如：开户、发卡）；
（2）对业务类型下的业务操作进行分类，行成业务事实（比如：开户纪录）；
（3）对业务事实的信息进行汇总，以作业务统计和决策分析，这里严格来讲已经不是真正的事实了，而是一种统计报表。

二、检查表结构

2.1、规范性检查

规范性检查，通过范式来进行，范式很多，有1，2，3，4，5，BC范式等，通常只需要检查1，2，3范式即可。

第一范式：是对属性（表中字段）单值的约束，要求一个属性不可对应多个值，也就是上面提到的单值属性，如果遇到多值属性，则需要将其抽象成一个实体，上面已经讲过；

第二范式：是对纪录（表中行）唯一性的约束，要求能够唯一标识一行纪录，无论是通过单列，还是多列来建立主键或唯一键，很多时候找不到能唯一标识实体的属性，那么就会建一个XXX_ID的列来实现；

第三范式：是对表中数据冗余的约束，要求单表中不存在派生属性，多个表中不存在相同非主键属性值，也就是说其他表中用于关联当前实体的主键属性列不算是冗余。

3范式，归纳下来就是：属性单值、纪录唯一、数据无冗余，完全符合3范式的数据结构设计通常不存在，尤其是数据冗余，有时根据业务或性能的需要，会故意做一些冗余，只要注意保持冗余数据与源数据的一致性也是可以的，把握好度即可。

另外，满足第2范式的要求是必须满足第1范式，依次类推。

再补充以前老师的一句话：数据库设计要做到不重、不漏。我的理解是：不重是指避免数据冗余，不漏是指要以E-R模型为基础，否则凭空想象需要哪几个表，难免会有疏漏，甚至错误。

2.2、可用性检查

可用性检查，就是看表结构能否满足业务场景及业务规则（约束）。那么如何检查呢？

这项检查工作是个跨度比较大的过程，参考《软件工程 - 5、数据库设计与开发》会发现，数据库设计不是一蹴而就的，而是在需求分析阶段建立E-R模型，在概要设计、详细设计阶段逐步细化、落实数据结构的一个过程，随着项目推进而去不断地完善并实现数据结构，这就是最好的可用性检查。

最后，概念结构设计、逻辑结构设计的结果都并不是唯一的。只要简单（实体个体不多、关系清晰、属性不冗余）、符合业务需要，都是合理的设计。