数据库设计参考标准.docx
《数据库设计参考标准.docx》由会员分享，可在线阅读，更多相关《数据库设计参考标准.docx（34页珍藏版）》请在冰豆网上搜索。
数据库设计参考标准
数据库设计参考标准
文档控制
文档属性
文档编号
作者
版本
日期
密级
EDS06
黄翠微
V1
2011年6月16
文档修订历史
版本
状态
日期
作者
修订描述
一、概述
为明确公司项目中数据库逻辑设计及物理设计的内容和流程，特制定本规范，供数据库设计、开发及维护人员参考。
数据库设计方法目前可分为四类：
直观设计法、规范设计法、计算机辅助设计法和自动化设计法。
新奥尔良法是目前公认的比较完整和权威的一种规范设计法。
新奥尔良法将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。
目前，常用的规范设计方法大多起源于新奥尔良法,并在设计的每一阶段采用一些辅助方法来具体实现.
以下是两种常用的规范设计方法：
1.基于E—R模型的数据库设计方法。
该方法是由P.P。
S。
chen于1976年提出的数据库设计方法，其基本思想是在需求分析的基础上，用E-R（实体—联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS的概念模式。
2.基于3NF的数据库设计方法。
该方法是由S·Atre提出的结构化设计方法，其基本思想是在需求分析的基础上,确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF的约束条件，将其进行投影分解，规范成若干个3NF关系模式的集合。
其具体设计步骤分为五个阶段：
（1）设计企业模式，利用规范化得到的3NF关系模式画出企业模式；
（2）设计数据库的概念模式，把企业模式转换成DBMS所能接受的概念模式,并根据概念模式导出各个应用的外模式；
（3）设计数据库的物理模式（存储模式）；
（4）对物理模式进行评价；
（5）实现数据库。
备注：
数据库设计规范、数据编程规范、数据库物理设计规范中以Oracle数据库为例，其它结构的数据库类似.
二、数据库设计流程
以规范性设计为例，把数据库设计流程分为以下几个阶段.
（一）需求分析阶段
1.需求收集和分析，得到数据字典描述的数据需求和数据流图描述的处理需求。
2.需求分析的重点：
调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性与完整性要求.
3.需求分析的方法：
调查组织机构情况、各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界。
4.常用的调查方法有：
跟班作业、开调查会、请专人介绍、询问、设计调查表请用户填写、查阅记录。
5.分析和表达用户需求的方法：
主要包括自顶向下和自底向上两类方法。
采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。
6.数据流图（DataFlowDiagram，DFD）表达了数据和处理过程的关系。
系统中的数据则借助数据字典（DataDictionary，简称DD）来描述。
（二）概念结构设计阶段
概念模型用于信息世界的建模.概念模型不依赖于某一个DBMS支持的数据模型.通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。
概念模型可以转换为计算机上某一DBMS支持的特定数据模型,其特点为:
1.具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识.
2.应该简单、清晰、易于用户理解，是用户与数据库设计人员之间进行交流的语言.
（三）逻辑设计阶段
将概念结构转换为某个DBMS所支持的数据模型（例如关系模型），并对其进行优化。
将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式，这种转换一般遵循如下原则：
1.一个实体型转换为一个关系模式，实体的属性就是关系的属性；
2.一个多对多的联系转换为一个关系模式.
数据模型的优化，确定数据依赖,消除冗余的联系，确定各关系模式分别属于第几范式。
确定是否要对它们进行合并或分解。
一般来说将关系分解为3NF的标准。
（四）物理设计阶段
为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。
根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式.
（五）数据库实施阶段
运用DBMS提供的数据语言（例如SQL）及其宿主语言（例如C），根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库,并进行试运行.
（六）数据库运行维护阶段
在数据库系统运行过程中必须不断地对其进行评价、调整与修改.内容包括：
数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。
为加快数据库设计速度，目前有很多数据库辅助工具（CASE工具），如Rational公司的RationalRose，CA公司的Erwin和Bpwin，Sybase公司的PowerDesigner以及Oracle公司的OracleDesigner等。
三、数据库设计规范
（一）数据库规范化的总体要求
1.数据表中避免可空列
虽然表中允许空列，但空字段为一种特殊的数据类型，数据库将对其进行特殊的处理,为此将增加数据库处理记录的复杂性，且当表中存在较多空字段时，在同等条件下，数据库处理的性能将降低许多。
因此,在数据库表设计时应尽量避免。
若确实需要,可通过一些折中方式处理,让其对数据库性能的影响降低至最少。
在此推荐两个方式.
A．设置默认值.默认值设置原则为业务启动后不可能达到的值,或者为原始状态的对应值.如无法从数值上区分是否为原始状态则不推荐设置默认值。
以数值型字段为例,假设其用于存放采集终端所获取的子舱位订座，由于订座数默认至少为大于等于0的数值，为此可设置默认值为-1。
另一个例子，字符型的航班状态，默认情况下航班为正常执行的正班，当因为天气、机械或其它调控等原因状态才发生改变，此时可设置默认值为“正班”。
日期型数据可默认为1900—01-01，在应用层面显示需要根据实际情况进行转换。
B．第二种方式是建立副表。
这是当一张表中允许为空的列较多时，如接近表全部列数的三分之一，且这些列数据在大多情况下均为空，此时建议另外建立一张副表,以保存这些列;主表与副表之间根据关键字关联，这样将数据分别存储于两个独立的表中使得主表设计更为简单，既保证了数据库性能，又能满足存储空值的应用需要.
如果字段默认值设置导致部分业务需求无法满足或开发代价大量增加,此时不建议设置，保留可空。
如数值型字段，如在该字段上需要与其他字段进行组合运算、或基于该字段进行统计汇总时，空值具有不可替代的重要作用。
2.数据表中不应存在重复数据值或列
如客户数据与客户经理数据存储问题，如将二者放在同一张表中,为解决多个客户经理问题，表中必须设置首选客户经理、备选客户经理相关信息。
在客户经理离职后，必须修改表中所有相关数据，造成很多不便且不利于追踪。
为此，在数据库设计的时候要尽量避免现象发生,建议改变策略，将客户信息存入一张表，客户经理信息存入一张表，而客户与客户经理之间的联系关系存入第三张表。
当联系关系发生变化时,仅需设置过期日期或状态即可。
3.数据表主键设置为无意义数值序列号
在进行数据库表设计的时候，采用一个无意义的、数值型的PKID对行记录进行唯一的标识,而不是通过航班号、航段、姓名、工号等具有业务意义的字段区分记录。
由于人工管理下很难保证PKID值的不重复，为此建议每个表对应的PKID列的值由数据库自动管理。
建议在原有业务上需要唯一区分的字段或字段组合上,建立唯一性索引。
以存储10年的航班计划表为例,业务上唯一区分一个航班的通常包含三个字段，航班号、航段、起飞日期，先前的做法是在这三个上建立复合主键。
在此推荐用一个Number（8）的PKID列作为主键,原因是以每天600个航班、一年365天，10年总计219万个航班,考虑到业务的扩展速度为10年内翻三番,657万，按理可设置7位，考虑到其它可能损耗的ID值,多设置一位，故为Number（8）；在航班数据的增删改过程中,通常需要判断是否存在重复数据，是则为修改操作,否则为插入操作，此时仅以PKID列无法区分，为此建议在航班号、航段、起飞日期上建立唯一性索引,由数据库后台执行一致性校验。
利用Sequence生成的PKID若需要用于其他数据库使用，需要在应用层面记录，以防止数据库出现问题时恢复后sequence不匹配问题。
4.数据库对象前缀名统一
一个应用系统对应的数据库表、视图、过程等对象，通常均高达千计，为对其进行规范管理与快速定位，要求遵循数据对象命名规范。
具体命名规范详见下文第
（二）点。
5.数据表上避免设置外键
外键的作用在于两方面，一是保证主从表数据一致性，这是数据库提供的、用于保证数据质量的强制性手段，二是定义主表数据删除时从表数据的级联删除方式；二者是外键的优势所在，减少了开发人员不分工作量。
但是，外键的存在同样带来困扰。
一是从应用系统的三层架构上考虑，如何设置外键关系到业务逻辑，这是将业务逻辑层部分功能后移至数据库层,导致各层职责不清的紧耦合现象，而且当业务逻辑随着需求发展发生变化将引起数据库层面与应用层面的双重修改；二是在开发过程中，开发人员不得不了解数据库物理设计细节，在项目协作分工上造成不便;三是从后续主数据、各业务方向公共数据建设上，为数据库管理员在执行数据表从“公有区”移入移出操作增加难度。
为此，建议不设置外键；如果设置了外键,则仅保留数据一致性校验功能，不建议设置级联删除的选项。
此时要求开发人员通过培养一种良好的编程习惯，从程序逻辑上实现主表、从表数据的增、删、改、查的规范操作.具体可参考如下说明。
A.从表数据增加操作
首先从主表上查询是否存在所需数据，是则可进行增加操作；否则需先增加主表数据后才可进行从表数据增加操作。
B.从表数据删除操作
直接删除从表数据.当主表数据未被任何从表引用时,根据实际业务需要判断是否级联删除主表数据.
C.从表数据修改操作
从表数据修改后的内容必须是主表内存在的数据，否则需先增加主表数据后才可进行数据修改操作。
D.主表数据增加操作
直接数据数据,与从表数据无关。
E.主表数据删除操作
删除之前判断是否存在引用的从表数据，是则根据业务规则决定是否继续，并根据业务规则判断从表引用数据的处理方式为对应数据项置空或删除从表数据记录。
如果未存在引用的从表数据，则可直接删除.注意保持事务的原子性，即主、从表数据同时提交或同时回滚。
F.主表数据修改操作
判断是否存在引用的从表数据,是则首先修改从表数据，然后进行主表数据修改。
注意保持事务的原子性，即主、从表数据同时提交或同时回滚。
为了在应用程序上能顺利完成上述六项操作,开发人员除了严格记录主从表调用关系外，还必须在程序上实现数据一致性检查功能，以及时发现可能的数据不一致现象。
如果应用程序未能实现这种一致性检查工作，则需将检查规则提交予DBA，由DBA在后台进行定期检查。
6.避免在数据库上编写存储过程、代码包、触发器
通常存储过程、代码包、触发器包含很多业务逻辑，违背了三层架构设计的松耦合原则。
（二）数据对象命名规范
1.命名规范总要求
所有数据对象命名必须遵循如下规范要求.
（1）通常采用“望文知义”的形式命名，部分对安全具有特殊要求的可以编号命名.
（2）命名格式是，以字母打头,包含具有特定含义的一个或多个英文单词，单词之间以下划线“_”间隔，长度不超过30个字符;
（3）大小写不敏感。
（4）禁止使用关键字命名。
（5）不同数据对象命名的前缀要求如下表所示.
编号
数据对象
前缀要求
1
非临时数据表
“4位模块名_表名"。
2
临时数据表
“TMP_表名”
3
分区表
“PT_”
4
主键
“PK_表名"
5
索引
“IDX_”
6
函数索引
“FIDX_”
7
视图
“VW_”
8
实体化视图
“MV_”
9
存储过程
“SP_”
10
触发器
“TRG_”
11
函数
“SF_”
12
数据包
“PKG_”
13
序列
“SEQ_"
14
数据类型
“TYP_”
15
后台任务
“JB_”
2.表命名规范
对于同一应用,在命名上区分不同功能模块的所使用的表。
假设一个系统包含三个模块，基础维护、查询、系统管理，则基础维护表名前缀为“TB_BASE_”,查询模块表名前缀为“TB_QURY_”，后面加上具体的表名。
对于一些特定作用的表，增加特定后缀表示。
如历史航班表可命名为TB_FLIGHT_HISTORY,复杂计算中间过程所产生的临时数据表可命名为TB_MIDTERM_DATA_TEMP。
3.字段命名规范
字段命名规范符合命名规范总要求。
字段类型选择满足
（1）固定长度的字符串类型采用CHAR，非固定长度的字串类型采用VARCHAR2。
（2）日期型字段采用日期类型。
（3）数字型字段采用NUMBER类型，并标明长度与小数位数。
（4）如无特殊需要，避免使用大字段（BLOB，CLOB,LONG等）。
（5）相同业务字段出现在不同表中，使用相同命名，且保证类型和长度一致。
4.索引命名规范
A.复合索引至少包含前两个字段的缩写或全名。
B.在表上创建主键时，必须显式指定主键唯一性约束名称。
举例：
为表TB_FLIGHT创建主键，正确格式为“altertableTB_FLIGHTaddconstraintPK_FLIGHTprimarykey（FLIGHT_ID）”，语句中指定了主键的唯一性约束名为PK_FLIGHT。
而错误的格式：
“altertableTB_FLIGHTaddprimarykey（FLIGHT_ID）”
此中，主键唯一性约束将被系统随机分配一个名字。
5.分区命名规范
采用可容易判断各分区内所存数据内容的名字,如对表TB_FLIGHT按季度进行分区，则各分区命名格式为“PT_XXXXQN”，其中XXXX为年份,N为1至4的季度编号.
6.触发器命名规范
命名规则：
trg_表名__〈ins/upd/del〉。
如表TB_FLIGHT行插入后的触发器名字为,TRG_TB_FLIGHT_AFTER_INS。
（三）数据库设计说明书格式
数据库设计说明书的格式至少包含如下部分。
1.概述：
从整体上说明数据库设计思想、采用的方法、作用范围、参考的文献、文档结构组成等。
2.数据表清单
编号
表英文名
表中文名称
表描述
使用的功能模块
1
表1
2
表2
…
3.ER关系描述
描述表之间存在的主外键联系等。
4.数据表定义
1）表1
字段编号
字段名称
中文
描述
数据
类型
Null
标志
是否主键
外键
是否索引
备注
1
2
…
备注
数据量增长说明、外键关系说明等
2）表2
5.各系统相关的其它信息。
要求在“数据表清单"每一行的表名上定义“超链接”指向“数据表定义”中的对应内容，以方便地在文档中浏览跳转等.
四、数据库编程规范
（一）书写规范
1.关键字大写，其他代码统一使用小写。
2.确保变量和参数在类型和长度上与表数据列类型和长度相匹配.建议采用如下格式：
vs_flightnotb_flight。
flight_no%TYPE
这样当TB_FLIGHT表的FLIGHT_NO字段定义发生变化时程序无需调整。
3.参数和变量命名前缀遵循如下规范：
项目
前缀要求
传入参数
“ip_”
传出参数
“op_"
数值型变量
“vn_”
字符型变量
“vs_”
日期型变量
“vd_”
4.程序块中的begin、end独立成行.
5.程序块采用缩进风格书写，保证代码清晰易读，缩进格数统一.
6.一行仅写一条语句。
7.同一语句占用多行时，每行的第一个关键字左对齐。
8.对于Insert…values和update语句，一行写一个字段，字段后面紧跟注释（注释语句左对齐），values和insert左对齐,左括号和右括号与insert、values左对齐
例：
insertintotable_name
（
user_id， -—用户ID,主键
user_name, ——用户名
login_name -—登录名
）
values
（
v_user_id，
v_user_name,
v_login_name
）
9.相对独立的程序块之间增加一空行.
10.超过110列的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。
（二）注释规范
1.以统一格式在头部对代码进行注释，以说明代码所要完成功能、传入参数、传出参数、作者、创建时间，并在其后从修改日期、修改者、修改内容角度描述各次修改历史。
/*
功能描述：
—--—--————-——-———
传入参数：
——---——---—-——---
传出参数:
--——---—--———-——-
返回结果:
-—-—--—--————--—-
作者：
--—---—-———------
创建日期：
—--———--—-——--—--
修改历史:
—--———---——-————-
（1）YYYY-MM-DD：
作者1，修改内容1；
（2）YYYY—MM—DD：
作者2，修改内容2；
＊/
2.在所有变量定义的右侧或上方,进行注释以说明变量的用途与含义.
3.注释内容清晰、明了，并确保无二义性.
4.对每一程序分支书写注释。
5.在代码的功能分支、子层次上注释，以帮助维护人员理解代码。
例：
casevs_type1
when1then—-里程奖励
。
。
....
when2then-—里程扣除
..。
。
..
when3then--里程兑换
。
.。
。
。
.
endcase;
6.代码注释应放在描述的代码上方或右方相近位置,不可放在下面.
7.注释与所描述的内容进行字数的缩进排列.
8.在函数中,对所返回的代码进行详细描述。
9.在程序块的结束行右方加注释,以表示程序块结束。
（三）语法规范
1.存储过程的In、out参数应按类别分开书写，不要交叉。
2.存储过程中变量的声明应集中在is和begin关键字之间申明
3.尽可能使用相关表字段类型来定义相关的变量，如%type，%rowtype.
4.删除无用的变量与参数.
5.存储过程中存在多分支时,若有事务控制，需确保各分支均有事务结束处理，且发生异常时，必须Exception中进行异常捕获。
（四）SQL性能规范
1.避免在批量操作时频繁使用commit
频繁的COMMIT会导致物理I/O增大,同时会产生logfilesync等待,但长时间不提交将带来更多的性能问题。
建议小于3秒的事务可以一次提交,大于3秒的操作尽可能3秒左右提交一次。
实际应用中使用COMMIT时必须保证事务的完整性。
2.使用动态sql时，需绑定变量,而非实际值.
为了不重复解析相同的SQL语句,在第一次解析之后，ORACLE将SQL语句缓存在内存中，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同,ORACLE就能很快获得内存中相同语句的执行计划，然后就直接执行该语句,而不需要进行解析。
如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，ORACLE还是需要对每条结构相同的语句重新解析一遍，同时在内存中缓存该语句，导致共享池中存放了N条结构相同的语句,既达不到共享的目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。
3.避免不必要的排序
对查询结果进行排序会大大的降低系统的性能
4.用WHERE子句替换HAVING子句
例如：
SELECTNAME，SUM（AGE）
FROMEMPLOYEE
GROUPBYNAMEHAVINGNAME！
=‘ABC’
修改为以下语句效果更好
SELECTNAME，SUM（AGE）
FROMEMPLOYEE
WHERENAME！
=‘ABC’GROUPBYNAME
5.用“〉="替代“>”
如:
在ID列上建有索引，则语句SELECT*FROMEMPLOYEEWHEREID>=9要比语句SELECT＊FROMEMPLOYEEWHEREID〉8高效.这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于8的记录.
6.删除表中所有记录时用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息，如果你没有COMMIT事务,ORACLE可以将数据恢复到删除之前的状态;而当运用TRUNCATE时,回滚段不存放任何可被用于恢复的信息，当命令运行后,数据不能被恢复,因此很少的资源被调用，执行时间也会很短,空间立即释放，detele操作后的空间可以被重新利用，但不会释放。
7.用UNION—ALL代替UNION
说明：
UNION-ALL不过虑重复记录,UNION过滤重复记录,所以需要先排序。
如果不需要过滤重复的记录，UNION-ALL比UNION性能更好。
8.用（NOT）EXISTS替代（NOT）IN。
在许多基于驱动表的查询中，为了满足一个条件，往往需要对另一个表进行联接。
在这种情况下，使用EXISTS（或NOTEXISTS）通常将提高查询的效率.
例如：
SELECT*
FROMEMPWHEREEMPNO〉0
ANDDEPTNOIN
（SELECTDEPTNOFROMDEPTWHERELOC='MELB’）
没有以下语句高效
SELECT＊
FROMEMPWHEREEMPNO>0
ANDEXISTS（SELECT'X'FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC='MELB’）
9.使用DECODE函数来减少处理时间.
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
例：
SELECTNAME|｜‘UNDERAGE’FROMEMPLOYEEWHEREAGE<18
UNION
SELECTNAME｜｜‘OVERAGE’FROMEMPLOYEEWHEREAGE>=18
可以改成：
SELECT
DECODE（SIGN（AGE-18），—1,NAME||‘UNDERAGE',1，NAME|｜‘OVERAGE’）
FROMEMPLOYEE
10.使用表的别名（Alias）。
当在SQL语句中连接多个表时，应使用表的别名并把别名前缀于每个列上,这样可以减少解析的时间，
升级会员 