数据库设计 概念结构设计（以权限系统为例） - QA龙

数据库设计 概念结构设计（以权限系统为例）

2011-04-23 13:35  QA龙  阅读(2649)  评论(0)  编辑  收藏  举报

转载来自http://yiyihuazi.blog.163.com/blog/static/151874052010637254799/小小宇梦
      转载请注明出处尊重别人也是尊重自己

一、数据模型

以下概念在一些教科书中都会有讲到，比如：《数据库原理与应用》。这里作了一下总结。

 

1.1、概念

模型，是对现实世界的抽象，数据模型，就是描述数据结构（静态特征）、数据操作（动态特征）、数据完整性（动静交互的约束）的概念的集合。而数据模型也是数据库管理系统（DBMS）的核心和基础，各种DBMS软件的实现都是基于数据模型的。

 

1.2、分类

数据模型可分为两种：概念数据模型、结构数据模型。

（1）概念数据模型，是面向现象世界的数据模型，它独立于计算机系统和DBMS；

常用的概念模型有：E-R模型、面向对象模型等。但DBMS发展至今，主流的仍然是关系型数据库，所以目前对于概念模型的设计依旧是使用E-R模型为主。

 

也许哪天面向对象型数据库成为主流，那我们的概念模型设计就也可以采用面向对象的方法了。其实ER模型就是面向对象模型的雏形，面向对象模型一定程度上是从ER模型演变过来的。

 

（2）结构数据模型，是面向数据库的数据模型，又可分为逻辑数据模型（逻辑结构）、物理数据模型（物理结构）；

逻辑数据模型：这是用户在DBMS中看到的模型。DBMS的逻辑模型先后经历了层状模型（树状模型）、网状模型、关系模型，以及现在发展中的面向对象模型，之所以关系型经久不衰，就是因为它简单的逻辑结构：表，无论是设计、维护都比较容易，尽管在处理效率上略次于前两者。面向对象模型虽然结构清晰、设计方便，但查询功能太弱，因此在DBMS中暂还没有取代关系模型的地位；

 

物理数据模型：是指数据在存储介质上的组织结构，它与相应的DBMS及OS相关，通常不需要手动去管理，而是通过用户在DBMS中指定存储的方式，由DBMS自动完成在相应OS上的存储结构。

 

上面数据模型的三种分类，也正是数据从现实世界到计算机世界的具体表示所要经历的过程，无论DBMS的发展如何，这个过程是不会改变的。

 

相应的，数据库设计可分为以下三步：

（1）概念结构设计：利用概念模型对现实世界进行抽象；

（2）逻辑结构设计：将概念模型转化为逻辑模型，也就是将对现实世界的抽象转化为计算机上DBMS的数据结构；

（3）物理结构设计：定制逻辑模型中实现的数据结构在物理介质的存储结构；

 

至于数据库设计在整个软件工程生命周期中所处的位置，详见《软件工程 - 5、数据库设计与开发》。

 

二、概念结构设计

概念结构设计的过程，就是建立E-R模型的过程。

2.1、E-R图

E-R图的组件有很多，但概括起来说，可分为以下四种：

矩形：表示实体

菱形：表示实体间的关系

椭圆：表示实体的属性

线段：用于将实体、关系相连接

 

对于双矩形、双菱形、双椭圆、双线段等等一些组件，可以不用去管，通常用以上四种组件就可以表达清楚实体及实体间的关系。

 

2.2、建立E-R模型

以下过程为建立E-R模型的一般步骤，这里以权限管理模块为例：

2.2.1、标识实体

 

 这是权限管理中常用的基于角色的访问控制（

RBAC），通常有用户、角色这两个实体。

 

2.2.2、标识关系

 

 用户与角色间为多对多的互相拥有关系。

 

2.2.3、标识实体、关系的属性

 

 不仅仅是实体有属性，关系同样也有属性，这些属性在实体间建立关系时才会存在。

有时属性太多，无法在图上一一列出，可以用表格，在后面的步骤中这个表格同样会用到，如下：

 

实体	属性	描述	…
用户	性别 年龄 电话 …	男/女 多大了 联系方式 …	…

 

2.2.4、确定属性域

属性域就是属性的取值范围。

这时，可以用表格将属性的数据类型、数据长度、取值范围及是否可为空、简单/复合、单值/多值、是否为派生属性等域信息定义出来。

这个过程，事实上包含了逻辑结构设计中的数据类型、NULL、CHECK、DEFAULT等信息。

 

实体	属性	描述	数据类型及长度	是否可为空
用户	性别 年龄 电话 …	男/女 多大了 联系方式 …	1字节的短整形或布尔型 1字节的短整形 20字节的字符型或长整形 …	NO NO YES

 

2.2.5、确定键

键就是可用于标识实体的属性，有：主键、唯一键、外键。

 

实体	属性	描述	键
用户	用户编号 性别 年龄 电话 …	男/女 多大了 联系方式 …	主键

 

2.2.6、实体的特化/泛化

也就是面向对象模型中父类和子类的概念，这是个可选的步骤。

举个例子，用户中大部分人都是普通员工，但有一小部分是从事销售的，销售人员有个负责区域的属性，如果将这个属性放在用户实体中，如下：

 

  

这时我们会发现，除了销售人员外，其他非销售人员这个属性全都不存在，这就是特化的过程。可以另建一个销售人员的实体来泛化用户实体，如下：

 

 这样就完成了对用户实体的泛化，泛化的过程也就是抽出实体间公共属性的过程，但通常，除非特化的部分太多，才会考虑将一个实体抽象成两个

1对1关系的实体，所有这个步骤是可选的。

 

2.2.7、检查模型

（1）检查冗余

首先检查实体：1对1关系的实体中有没有非外键的重复属性，或者就是同一个实体；

其次检查关系：有没有通过其他关系也可以得到的重复属性；

 

当然有时，需要考虑时间维度，因为有些属性是有时效性的，也就是虽然是同一个属性，但不同的时间表示的却是不同的内容，这一点在后面的逻辑结构设计中会提到，这并不是真正的冗余。

 

（2）检查业务

检查当前的E-R模型是否满足当前业务的场景。可以从某个实体开始，沿着当前E-R模型的各个节点去模拟业务场景。尤其需要和《需求规格说明书》去做校验。

 

到这里，也就完成了E-R模型建立的全过程，有时，对于比较复杂的E-R模型，一张图可能显得太过局促，可以建立全局、局部E-R模型图，以便于查看和分析

逻辑结构设计是将概念模型转换成逻辑模型的过程，也就是将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来表示；

 

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

 

多值属性：一行纪录有多个值的属性，比如：公司实体的电话[phone]），每个公司都可能有多个电话，这时，可以将电话属性抽象成一个实体，按照下面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模型，在概要设计、详细设计阶段逐步细化、落实数据结构的一个过程，随着项目推进而去不断地完善并实现数据结构，这就是最好的可用性检查。

 

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

下面列出了在项目开发过程中，经常遇到的数据库设计模块。

一、常用实体
1.1、全局实体
（1）独立参数
记录系统中独立存在的参数，通常使用整形和字符型即可兼顾所有常用类型的参数。

结构如下：

参数编号 参数名称/代码 参数值整形 参数值字符型
1 首页标语 0 我爱北京*
2 无操作自动退出时长 10 N/A
… … … …

注：N/A意为不可用（not available），如果觉得不习惯可用NULL，在《数据库设计（4）_逻辑结构设计_常用技巧》会讲到关于NULL值与默认值的问题。

（2）序号申请
用于实体编号、事实编号等的唯一序号申请，对于OLTP系统中，事务并发量较大的时候，使用这种方式来申请事实编号，可能会带来blocking，可以使用SQL SERVER的标识列、或ORACLE的SEQUENCE以标识事实，来代替手动序号申请。

有效日期字段用于第二天序号重新置为1的情况，在申请序号时，需要加一个有效日期与当前日期比较的逻辑。

结构如下：

序号 序号代码 下一序号值 有效日期
1 NEXT_USER_ID 3 N/A
2 NEXT_FACT_ID 888 2000-01-01
… … … …

（3）消息
系统中各种提示、错误等的定义，比如：弹出的提示或错误框里的文本。

0、30是SQL SERVER中对语言的编号，0为英语，30为简体中文，是否记日志是指：当前消息是否存储为日志，即上一节里事实分中的系统日志；错误级别在一般的系统中通常不需要，可以省略。

结构如下：

消息编号 语言编号 消息文本 是否记日志 错误级别
1 0 Username or password error 1 3
2 30 用户名或密码错误 1 3
… … … … 

（4）字典
有时，还会出现与业务无关的一些公共实体，比如：国家、民族、省、市、某某类型等，通常由于太多，不方便写在程序里，所以放到表中，在查询时根据ID关联下即可。

对于大型平台，这部分公共信息通常为多个子系统所用，会将它们单独存放，比如：登录、权限相关的实体，一起放在一台服务器上，作为公共服务器或身份认证服务器。
这种情况，逻辑结构不一定是关系型，比如：WINDOWS系统的目录服务就是树状的；物理存储也不一定在磁盘上，比如：memcache、内存数据库都是将数据存放在内存中。

1.2、权限实体
目前的权限控制多采用基于角色的访问控制（RBAC），有时也会结合物理位置，如：MAC地址、IP等。

（1）子系统
一个大型的平台，通过一个入口进去后可能有多个子系统，那么每个子系统就应该独立管理起来。

结构如下：

系统编号 版本 图标 背景图片 菜单风格 是否放工具栏 …
1 1.0.0.1 /…/ /…/ WINXP 0 …
2 2.3.0.1 /…/ /…/ VISTA 1 …
… … … … … … …

（2）菜单
以下菜单属性只是UI方面的，还要有菜单对应的页面：C/S方式的DLL、FORMNAME，B/S方式的URL都要设计进来。最后菜单属于某个子系统。

结构如下：

菜单编号 菜单名称 热键 加速键 是否分组 是否分隔 是否工具栏显示 工具按钮图标 工具按钮提示 微帮助 子系统 …
1 查询 F8 Q 0 1 1 /…/ 查询 查询报表 1 …
2 删除 F9 D 1 0 0 /…/ 删除 删除数据 1 …
…  … … …     … … …

（3）页面元素
页面元素，即页面每个控件来定义，在控制权限时，简单的做法就是根据角色权限配置把页面上的控件隐藏掉。最后控件属于某个菜单页面。

结构如下：

控件编号 控件名称 事件 菜单编号 …
1 edt_text chk_username.do 10 …
2 btn_user sav_userinfo.dll 10 …
… … … … …

（4）数据内容
数据权限的控制比较麻烦，通常数据集以表格的形式来展示，表格也是个页面控件（这里是一个特化/泛化的设计，在页面元素实体中也存在数据集展示的控件，单独将数据集相关的属性抽了出来作为一个实体），通过定义这个控件所展示的数据列清单，及行的范围来控制数据权限。

中文列表示表格的列名、英文名表示所查询的数据库中的表字段，行表示用于控制范围的字段：USER_ID表示只显示当前用户编号的数据（我们知道在事实中会记载操作员编号），空表示显示所有数据，即所有用户看到的数据是一样的。

结构如下：

数据集编号 控件编号 中文列 英文列 行 …
1 20 列1,列2,列3 col1,col2,col3 USER_ID …
2 21 列1,列2,列3 col1,col2,col3  …
… … … … … …

（5）用户
这里的用户包括业务用户、管理员。
业务用户：使用系统中的业务功能；
管理员：对系统进行管理，不一定使用系统中的业务功能；

对于业务用户和管理员，根据系统的业务需求可以作为一个实体，也可以进行特化/泛化作为两个实体来处理。如果作为一个实体对于实现单点登录更为方便些。如果作为两个实体则通常需要两个登录入口。下面是作为一个实体的情况。

结构如下：

用户编号 用户名称 用户密码 创建时间 上次登录时间 登录次数 …
abc 张六子 密文 2000-01-01 00:11:22 2000-01-01 11:22:33 2 …
admin 王二小 密文 2000-01-01 22:33:44 2000-02-02 22:33:44 10 …
… … … … … … …

如果系统中有用户账户存在的话，应该独立管理，而不应该和用户实体混为一谈。这里可能会遇到用户名/密码的问题，用户登录究竟是以用户编号/用户密码，还是用账户编号/账户密码，这两种其实都有，比如：目前的网上银行，有的银行使用前者，有的银行使用后者。
用户编号/用户密码：用户的身份证号+用户密码来登录；
账户编号/账户密码：当前账户的卡号+账户密码来登录；

需要注意的是，在数据库中的密码一定要存储为密文，通常使用不可逆加密算法，在验证密码的时候，使用同样的算法将明文加密成密文，与数据库中的密文进行比较即可，但这种方法不太安全，有可能会出现在密文传输过程中被捕获，进而出现模拟用户发包以通过验证的情况，所以银行方面通常会使用不对称加密算法，使用公钥和私钥以保证安全，这里不再赘述。

（6）角色

结构如下：

角色编号 角色名称 …
1 财务 …
2 销售 
3 管理员 …
… … …

（7）权限
权限实体是可选的，可以直接把权限列表配置给角色，这里把权限定义好，然后指派给角色。

权限里只配置控件编号，子系统、菜单、数据集编号可以关联获得。

结构如下：

权限编号 控件编号 …
1 1 …
2 2 …
3 100 
3 101 
… … …

（8）角色权限
在权限配置的时候可以配置多个小的权限，一个角色可以拥有多个权限，角色的总权限为所有权限的并集。

结构如下：

角色编号 权限编号 …
1 1 …
1 2 
2 2 …
… … …

（9）用户角色
一个用户可以拥有多个角色，用户的总权限为所有角色权限的并集。

结构如下：

用户编号 角色编号 …
abc 1 …
abc 2 …
admin 3 …
… … …

有的系统中还会有这样的功能：用户组（给用户组分配权限，新加入组的用户直接拥有组的权限）、新建的用户从已存在的用户复制角色权限，这里不一一列举，其实权限有时真没必要做那么复杂，配置起来会很麻烦的。

二、树型结构
计算机世界里数据结构有很多种：链表、树、图等等，每种数据结构都有其优点，有时同样需要在数据库中实现这样的结构。比如：树形结构的可扩展性。

下面以windows系统的开始菜单为例，看下树形结构的设计。
（1）整形
使用整形编号，标识父子关系。想要得到某节点的所有父节点，或所有子节点，使用SQL语句实现递归即可，比如：自定义函数，或者使用CTE。

结构如下：

菜单编号 菜单名称 父亲 节点深度 …
1 开始 0 1 …
2 程序 1 2 …
3 设置 1 2 …
4 控制面板 3 3 …
… … … … …

（2）字符型
使用字符串编号，标识父子关系。想要遍历父子节点，根据前缀全用模糊查询即可，缺点在于：变动某个中间节点的位置时，需要变更节点自身及其所有子节点的编号，维护起来不太方便。

结构如下：

菜单编号 菜单名称 节点深度 …
001 开始 1 …
00101 程序 2 …
00102 设置 2 …
0010201 控制面板 3 …
… … … …

三、多语言支持
（1）语言
首先对语言进行编号，作为列表，在用户登录入口供用户选择。

结构如下：

语言编号 语言名称 …
0 ENGLISH  …
28 繁體中文 …
30 简体中文 …
… … …

（2）名字空间域
对系统中所有名称存放多个语言的版本，比如：菜单名。

结构如下：

名字_语言_编号 名字编号 语言编号 名字
1 1 0 Query
2 1 30 查询
… … … …

（3）实体表
在实体表中仅使用（2）中的名字编号，没有名称，结合用户登入时的语言编号以得到相应语言的名字。

结构如下：

菜单编号 热键 加速键 是否分组 是否分隔 是否工具栏显示 …
1 F8 Q 0 1 1 …
2 F9 D 1 0 0 …
… … … …   …

四、不改变现有数据结构的结构扩展
（1）扩展现有表的字段
通常情况下，可以用特化/泛化来扩展实体表的属性。以用户实体为例。

原结构如下：
用户编号 用户名称 …
abc 张三 …
efg 李四 …
… … …

现需要在这些用户中标识一部分销售人员，也就是在概念结构设计中的例子。

扩展的结构如下：

用户编号 负责区域 …
abc southeast …
… … …

那是不是随着系统中应用的开展，逐渐地去建立很多的扩展表呢？也可以用一个表，这样建：

用户编号 属性 属性值 …
abc 性别 男 …
abc 负责区域 southeast 
efg 性别 男 
efg 负责区域 all 
… … … …

但如果实体的数据量很大时，这样的方式进行行列转换的成本还是比较大的。所以甚用。

（2）扩展新的实体
扩展新的实体，通常无法做到完全通用，只能针对某一固定行业，在按照属性分类定义了基本的表结构后，进行扩展，比如：

这里统一存放所有实体的名字空间，每一棵树代表一个实体。
树编号 节点编号 名称 父亲 …
1 1 属性1 0 …
1 2 属性2 1 …
1 3 属性3 1 …
… … … … …
100 1000 属性1 0 …
100 1001 属性2 100 …
100 1002 属性3 100 …
… … … … …

每一类型的实体对应着自己的属性表：
实体编号 节点编号 列1 列2 列3 …
1 1 1 a abc …
1 2 2 b efg …
1 3 3 c hij …
… … … … … …
100 1000 4 x abcd …
100 1001 5 y efgh …
100 1002 6 Z hijk …
… … … … … …

这样的属性表有很多张，对应着各种可能出现的实体类型，这样在进行每种类型的实体扩展时，只需要在表中配置数据，就可以实现实体的扩展，当然，前提是要对行业有相当的理解。

总结一下这些年在项目中一些设计技巧，有些在前面的章节已经提到过。

 

一、属性定义

1.1、数据类型

（1）整型还是字符型

在可以为整型的情况下尽量使用整型，通常情况下整型占的空间小，可以提高I/O及缓存命中率。

 

（2）定长还是变长字符型

定长的好处（也就是变长的坏处）：

a)定长字段不需要额外维护和计算行偏移量，当然这个成本不是很大，可以忽略；

b)如果用变长的话，很有可能会由于更新行数据而带来页拆分，这个成本是很大的；

 

变长的好处：

节省存储空间，可以提高I/O及缓存命中率；

 

那究竟使用定长还是变长呢？规则就是：同一属性列的数据长度有明显差异、且更新不太频繁的情况，使用变长字符，否则建议使用定长字符，比如：订单号、MAC地址，这类长度固定的属性应该毫不犹豫地使用定长。

 

（3）数据类型的长度

业务的发展总是会出乎人为意料的，所以不要太小气，在长度不是太大的情况下，大胆去放大数据类型的长度，比如：把smallint（占2字节）改为int（占4字节）。

 

（4）数据类型一致性

避免出现：同一个字段，在A表中为整型，在B表中为字符型，保证数据类型的一致。

 

1.2、NULL

建议将所有列都设为not null，原因如下：

（1）记录中存在允许为null的列时，每次都在读取该行时都需要去检查null列是否有值；

（2）索引中不会记录null值，因为这个值根本不存在，所有无论单表还是多表的查询中，is null/is not null无法使用索引，关于索引的使用可参见《SQL Server 查询优化（3）_索引的设计与使用》；

（3）null值无法进行比较运算（比如：单表查询时id=1/id<>1都得不到id为null的行，多表关联时null与null也是无法对等的）、数学运算、连接运算等，当然可以通过设置一些选项来改变null的运算行为；

（4）null值除了增加程序处理的逻辑外，还有可能在聚合运算中导致bug，比如：count()和avg()，count(col1)如果col1存在null值，则得到的是非null值的行数，avg()也一样，用count(*)或count(col2)，假设col2不可为空，可以得到真实的行数；

 

那么对于属性列不确定的时候怎么办呢？答案就是：使用默认值，比如整型为0，字符使用

N/A或N/V，时间使用1900-01-01 00:00:00，这样以避免null值的使用。

另外，对于布尔型的0、1尽量保持逻辑一致，如：0为FALSE，1为TRUE。

 

DBMS中之所以引入null，只是为了完善数学模型，即3值逻辑，这个有点像cross join。

 

1.3、FK（外键）

为保证实体完整性，也就是第2范式，表中主键或唯一键是一定要有的，否则就可能出现重复纪录。那么外键呢？

通常矛盾在于：参照完整性及实现参照完整性的性能问题。

（1）用外键实现参照完整性，保证数据的一致性；

（2）海量数据的情况下，参照完整性检查的成本很大；

 

究竟怎么使用外键呢？对于海量数据的情况，为保证DML的效率，不建议使用外键，参照完整性可以使用after触发器或关联属性来实现；反之就是在不影响性能、或对性能要求不高的情况下使用外键。

 

1.4、CHECK

CHECK约束用于检查域完整性，不建议使用，检查工作完全可以放到界面上去做，比如：java里的正则表达式，多么强大。

 

目前，能够想到的不得不用CHECK约束的地方就是分区视图，但分区视图是SQL SERVER没有分区表的时候，一个替代品，所以有了分区表就再也没有使用CHECK约束的理由了。

 

二、实体、关系表

2.1、属性

（1）简单属性还是复合属性

举个例子：银行卡号一个长串，第一反应，它应该是一个字符串，但其实，银行卡号通常只有后四位才是唯一标识，前面都是银行编号+开户行等信息，那么这样完全可以把银行卡号作为多个整型值来存储。这要取决于业务规则；

 

再比如前面提到过的姓名，如果需要国际化时，姓名就要考虑FirstName、MiddleName、LastName字段，而不能像中国人一样直接就是一个Name字段；

 

（2）是否真的是多值属性

前面提到过多值属性需要单独抽象成实体，但有时，需要辨别是否真的是多值属性，比如：用户实体的地址、机器实体的所有元器件列表、小说实体的所有章节，它们都是单值的复合属性；

 

（3）备注或者描述

数据表设计应该越简洁越好，像备注或者描述（remark/description）这样的字段，如果可有可无时，最好省掉：一是没有人真正地去维护它，二是占存储空间；

 

（4）实体还是属性

举个例子：《数据库设计（2）_逻辑结构设计》中1:1关系里的用户与汽车。

a)如果业务需求中对于汽车的属性不作限制，只需要知道汽车编号即可，E-R模型如下图：

 

 此时可以将用户与汽车实体合并为一个用户实体，

E-R模型如下图：

 

  

b) 如果业务需求中对于汽车的属性需要作出描述，比如：产地等，E-R模型如下图：

 

 那么此时就不能实体进行合并，应该独立描述用户、汽车实体。

 

2.2、其他

（1）字典的使用

实体表中的状态、类型等属性，如果不是太多，不需要做成字典表。另外、经常变化的元数据不宜作为字典，因为字典这些信息通常会加载在CACHE中，如果经常变，那就意味着需要经常去更新CACHE。

 

（2）系统中有哪些用户

通常需要存在四种用户：管理员（全部读写权限）、业务操作用户（部分读写权限）、报表查询用户（只读权限）、测试用户（部分读写权限），测试用户通常供开发人员使用，往往在软件上线后，需要在线修改一些问题，测试用户产生的数据，不计入业务数据范围；

 

（3）大写还是小写

默认ORACLE把所有数据库对象大写，如果想指定成小写或大小写混写，需要用""，但这样带来的问题就是在引用这个对象时也要加上""，很不方便，所以索性全用大写，这对于异构数据库的互相访问很有帮助，比如：从ORACLE访问SQL SERVER；

 

另外，在作字符等值比较时，最好使用UPPER/LOWER函数将两端的字符全转换成大写/小写再作比较；

 

在后面会分别给出SQL SERVER和ORACLE数据库的命名和开发规范，

 

（4）实体行如何删除

不允许对已发生事实、或建立关系的实体行进行物理删除，会破坏实体的参照完整性，即使是没有发生事实、或建立关系的实体行，通常也不进行物理删除，只是逻辑删除；

 

（5）兼容B/S和C/S开发方式

设计实体时要考虑到B/S和C/S开发方式的元素，比如：C/S方式中的DLL、FORMNAME、根据B/S的应用使用一些新型的数据类型，比如：XML类型；

 

（6）数据权限

除了管理员有所有数据权限外，数据权限原则上是谁产生的数据谁有权限；

 

（7）如何进行数据同步

数据同步有推、拉两种方式。

推的方式：在数据发生变化后，立即发出通知给需要更新的点；

拉的方式：由定时任务，定时去数据源作增量检查，这种方式就需要在表中建立一个标识增量变化的属性，比如：时间戳；

 

（8）不能为了技术而技术

举个例子：用户申请编号时，发起申请编号的事务，事务完成时显示：您申请到的编号为XXX。

但如果并发量较大，即便是采用数据库自身的序号生成方式（IDENTITY或SEQUENCE），仍然会存在等待，那么，就可以考虑从客户端生成此次事务的唯一编号，到数据库层没有申请编号的排队过程，直接写入；

 

（9）实体还是关系

举个例子：《数据库设计（3）_逻辑结构设计_常用模块》中权限实体里的角色、权限、控件。

a)如果不使用权限实体，此时权限通过关系来描述，E-R模型如下图：

 

  

b)如果使用权限实体，此时权限以实体来描述，E-R模型如下图：

 

  

这两种方式都是可以的，但需要注意的是：一要避免由于实体或关系带来的数据多次存储，浪费存储空间；二要保证数据的一致性；

 

再重复一次：概念结构设计、逻辑结构设计的结果都不是唯一的。

 

三、事实

（1）发现及记载事实

在《数据库设计（2）_逻辑结构设计》中提到过事实的发现，通常，业务相关的事实，通过查看《需求规格说明书》、与用户直接交谈、自身的行业经验、问卷调查等方式可以获得；

 

但系统业务中用不到或者说暂时用不到的事实，是否就不作记载呢？比如：系统日志等等，建议在不影响系统性能及用户体验的情况下，记载尽量多的事实，日后，它对于做决策分析等这类事情时，会很有帮助；

 

（2）事实与报表

根据业务需要进行周期性的聚合，产生比如：日报、周报、月报这样的统计表，而不是等到需要查报表的时候才去事实表中进行汇总；

 

（3）事实的大类与小类

事实小类即某个具体的业务事实，这点通常都会有事实记载，但有些时候，需要查看事实大类的明细，是否需要去遍历各个小类呢？

 

比如：账户事实，大类为进出账，小类为：充值、提现、消费、利息、扣税等，如果想要看账户的进出账明细，可以记录一个账户进出账的大类事实，当中包括进出账及进出账的类型，如果想看某一类型的明细，再去小类事实看发生了什么，比如：消费还可以再分为交电费、交水费、交网费、饭店用餐等，同样，对于同一小类事实的不同类型，也需要加上类型字段，比如：消费类型；

 

再比如：用户使用系统功能的日志，大类为用户操作，小类为：点击各个控件所做的事情，如果想要看用户操作行为，可以记录一个用户操作的大类事实，当中包括用户点击了哪些功能，如果想看某一操作的明细去小类事实看用户操作的内容；

 

（4）事实中瞬态属性

瞬态属性的记载尤为重要，否则可能会丢失事实的真相，比如：商品的定价、账户的卡号等。

• 分类 数据库
•