数据库设计（三）——数据库设计规范

一、数据库设计规范简介

1、数据库设计规范化的要求

A、表中应该避免可为空的列
B、表不应该有重复的值或者列
C、表中记录应该有一个唯一的标识符
D、数据库对象要有统一的前缀名
E、尽量只存储单一实体类型的数据

2、数据库设计规范化的目的

A、消灭重复数据。

B、避免编写不必要的，用来使重复数据同步的代码。

C、保持表的瘦身，以及减从一张表中读取数据时需要进行的读操作数量。

D、最大化聚集索引的使用，从而可以进行更优化的数据访问和联结。

E、减少每张表使用的索引数量，因为维护索引的成本很高。

二、数据库表设计规范

1、规范化与反规范化

规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度，而且范式越高性能就会越差。出于性能和方便管理的考虑，原则上表设计应满足第三范式。有时为了提高某些查询或应用的性能而可以破坏规范规则，即反规范化。

数据应当按两种类别进行组织：频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据，内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。比较复杂的方法是将规范化的表作为逻辑数据库设计的基础，然后再根据整个应用系统的需要，物理地非规范化数据。

2、数据表分类说明

根据应用的实际需要和特点，可以将数据表进行如下分类：

基本数据表：描述业务实体的基本信息。例如，人员基本信息、单位基本信息等。

标准编码表：描述属性的列表值。例如，职称、民族、状态等。

业务数据表：记录业务发生的过程和结果。例如，人员调动登记、变更通知单等。

系统信息表：存放与系统操作、业务控制有关的参数。例如，用户信息、权限、用户配置信息等。

统计数据表：存放业务数据统计值。例如，通知单统计、人员类别统计等。

临时处理表：存放业务处理过程中的中间结果。

其他类型表：存放应用层的日志、消息记录等。

3、字段设计规范

（1）一般来说，应该使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。

（2）选择更简单的数据类型。例如，比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。

（3）尽可能把字段定义为NOT NULL。对于字段能否NULL，应该在SQL建表脚本中明确指明，不应使用缺省。

（4）一个表中的字段不要太多，理论上不要超过80个。

（5）数据库中所有布尔型中数值0表示为假；数值1表示为真

（6）当字段定义为字符串类型时使用VARCHAR2而不用NVARCHAR

（7）字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值为数值0。

4、键设计规范

（1）为关联字段创建外键。

（2）所有的键都必须唯一。

（3）尽可能避免使用复合键。

（4）外键总是关联唯一的键字段。

（5）尽可能使用系统生成（如序列SEQUENCE产生）的主键。

（6）可选键有时可做主键。

（7）一个表中组合主键的字段个数尽可能少。

5、索引设计规范

（1）如果一列出现在表达式或函数中，不会使用该列上的索引

（2）要索引外键

（3）对于索引选择性高的列使用B-Tree索引

（4）对于索引选择性低的列使用位图索引

（5）HASH索引只适用于相等比较

（6）不要索引大型字段（有很多字符的字段）

（7）不要索引常用的小型表

6、LOB设计规范

如无特别需要，避免使用大字段（BLOB、CLOB、LONG等）。如使用时必须使用BLOB或CLOB类型。

三、完整性设计规范

采用数据库系统实现数据的完整性，不但包括通过标准化实现的完整性而且还包括数据的功能性。在写数据的时候还可以增加触发器来保证数据的正确性。不要依赖于应用程序保证数据完整性，它不能保证表之间（外键）的完整性。

1、主键约束

每个表要求有主健，主健字段或组合字段必须满足非空属性和唯一性要求。

2、外键约束

（1）对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。

（2）根据需要适当设置父表数据修改时对子表的影响：

父表中删除数据：级联删除；受限删除；置空值。

父表中插入数据：受限插入；递归插入。

父表中更新数据：级联更新；受限更新；置空值。

3、NULL值

由于NULL值在参加任何运算时，结果均为NULL，所以必须利用NVL()函数把可能为NULL值得字段或变量转换为非NULL的默认值。

4、CHECK条件

对于字段有检查性约束，要求指定CHECK规则。

5、触发器

触发器是一种特殊的存储过程，通过对表的DML操作而触发执行，是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。选择触发器的BEFORE或AFTER事务属性的时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。在大量修改数据时，尽量避免使用触发器。

6、视图

为了在数据库和应用程序之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问表。这样做还在处理数据库变更时提供了更多的自由。视图是虚拟的数据库表，在使用时要遵循以下原则：

为简化查询，将复杂的检索或子查询通过视图实现。

提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员。

视图中如果嵌套使用视图，级数不要超过3级。

由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的表，不宜使用视图，可以采用实体化视图代替。

除特殊需要，避免类似SELECT * FROM [TableName] 而没有检索条件的视图。

视图中尽量避免出现数据排序的SQL语句。

四、安全性设计规范规范

1、管理默认用户

在生产环境中，必须严格管理SYS和SYSTEM用户，必须修改其默认密码，禁止用该用户建立应用数据库对象。删除或锁定SCOTT等默认安装但不使用的用户。

2、数据库级用户权限设计

必须按照应用需求，设计不同的用户访问权限。包括应用系统管理用户，普通用户等，按照业务需求建立不同的应用角色。用户访问另外的用户对象时，应该通过创建同义词对象SYNONYM进行访问。

3、角色与权限

确定每个角色对数据库表的操作权限，如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于他所兼角色的权限之和。

4、应用级用户设计

应用级的用户帐号密码不能与数据库相同，防止用户直接操作数据库。用户只能用帐号登录到应用软件，通过应用软件访问数据库，而没有其它途径操作数据库。

5、用户密码管理

用户帐号的密码必须进行加密处理，确保在任何地方查询都不会出现密码的明文。

五、SQL语句设计规范

1、字符类型数据

SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字符串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用TRIM(),LOWER()等函数格式化匹配条件。

2、复杂SQL

对于非常复杂的SQL（特别是有多层嵌套，带子句或相关子查询的），应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

3、避免IN子句

使用 IN 或 NOT IN 子句时，特别是当子句中有多个值且表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。

4、避免使用SELECT * 语句

如果不必要取出所有数据，不要用 * 来代替，应给出字段列表。

5、避免不必要的排序

不必要的数据排序大大的降低系统性能。

6、INSERT语句

使用INSERT语句一定要给出插入值的字段列表，这样即使表加了字段也不会影响现有系统的运行。

7、多表连接

做多表操作时，应该给每个表取一个别名，每个表字段都应该标明其所属哪个表。

8、参数的传递

SQL语句的编写，变量尽量使用“?”绑定变量。

六、数据库设计命名规范

1、数据库命名基本原则

（1）所有命名采用26个英文大小写字母和0－9这十个自然数，加上下划线_组成。不能出现其他字符（注释除外）。

（2）长度不超过30个字符。

（3）实际名字尽量描述实体的内容，由英文单词、单词组合或单词缩写组成，不以数字和_开头。

（4）命名中禁止使用SQL关键字。

（5）对象名尽量短。

2、数据库对象命名规范

数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。对象名字由前缀和实际名字组成，长度不超过30。

前缀：使用小写字母加下划线

表        tb_ 
视图        view_
存储过程    sp _
函数        fn_

触发器 trig_

局部变量 l_ 

全局变量 g_

3、表命名规范

约定：表名由前缀和实际名字组成。

前缀：使用小写字母tb_，代表表。实际名字中，一个系统尽量采取同一单词，多个后面加_来连接区分。

因此，合法的表名类似如下。

tb_Member

tb_MemberInfo

tb_ForumBoard

表

表名如Order/UserAccout

符合以下规范：

(1)统一采用单数形式，反对Orders

(2)首字母大写，多个单词的话，单词首字母大写，反对order/Useraccout/ORDER

(3)避免中文拼音，反对AgentBaoCi

(4)避免下划线连接，反对User_Accout（下划线适用Oracle数据库）

(5)避免名称过长，反对WebsiteInfomationModifyRecord

(6)多对多关系表，以Mapping结尾，如UserRoleMapping

(7)避免保留字

表以单数形式名词或名词短语命名。如果表名仅有一个单词，那么建议不使用缩写，而是用完整的单词。

主键:PK_<表名> 

外键:FK_<表名>_<主表名>_<外键字段名>

索引:IDX_<表名>_<构成索引的字段名>

如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。

4、字段命名规范

字段由表的简称，实际名字组组成。如果此字段关联另外的字段，那么加下划线_连接关联表字段的字段名。

因此，合法的字段名类似如下。

UserID_MeID
UserName
UserRegDate

字段

字段名如userID/userName/userType

符合以下规范：

(1)首个字母小写，多个单词的话，单词首字母大写，反对UserID/Userid

(2)必须有一主键，主键不直接用ID，而是表名+ID，如userID/orderID

(3)常用的字段name，不直接用name，而是表名+Name，如userName/orderName

(4)常用的字段desc，不直接用desc，而是表名+Desc，如userDesc/orderDesc

(5)大写字母前必须包含至少两个小写的字母，反对uID/oID

(6)避免中文拼音

(7)避免下划线连接

(8)避免名称过长

(9)避免保留字 

对象

(1)存储过程以SP_为前缀

(2)触发器以TR_为前缀

(3)函数以FN_为前缀

(4)主键以PK_为前缀

(5)索引以IX_为前缀

(6)前缀后的首字母大写，多个单词的话，单词首字母大写，如SP_CountFee

(7)所有的关键字的所有字母必须大写，如SELECT userID,username FROM User

采用有意义的字段名，应该是易于理解，能表达字段功能的英文单词或单词缩写，一般不超过三个英文单词。

系统中所有属于内码的字段（仅用于表示唯一性和程序内部用到的标识性字段），名称取为：ID。

系统中属于是业务范围内的编号的字段，其代表一定的业务信息，字段建议命名为CODE，其数据类型为VARCHAR，该字段需加唯一索引。

字段名不要与表名重复

不要在列的名称中包含数据类型。

5、视图命名规范

字段由前缀和实际名字组成，中间用下划线连接。

前缀：使用小写字母view，表示视图。

因此，合法的视图名类似如下。

view_User
view_UserInfo

6、存储过程命名规范

约定：字段由前缀和实际名字加操作名字组成，中间用下划线连接。

前缀：使用小写字母sp，表示存储过程。

操作名字：Insert|Delelte|Update|Caculate|Confirm

例如：

sp_User_Insert

7、SQL语句命名规范

约定：所有SQL关键词全部大写。