数据库设计规范

数据库设计规范

（一）、命名规范

1、 表名

使用每个中文字的汉语拼音首字母。

备份表：BAK_表名_姓名拼音日期（BAK_YJBYSMD_ZHJ0619）

临时表：TMP_表名_姓名拼音日期（TMP_YJBYSMD_ZHJ0619）

2、 键名

前缀为PK_。主键名称应是 前缀+表名+构成的字段名。如果复合主键的构成字段较多，则只包含第一个字段。

alter table BJMCDMB

add constraint PK_BJMCDMB_BJDM primary key (BJDM)

3、 外键名

前缀为FK_。外键名称应是 前缀+ 外键表名 + 主键表名 + 外键表构成的字段名。

alter table BJMCDMB

add constraint FK_BJMCDMB_ZYDMB_SSZYDM foreign key (SSZYDM)

references ZYDMB (ZYDM);

4、 索引

前缀为IDX_。索引名称应是 前缀+表名+构成的字段名。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。

5、 视图

前缀为View_。按业务操作命名视图。

6、 存储过程

前缀为Proc_ 。按业务操作命名存储过程。

7、 触发器

前缀为Trig_ 。触发器名应是 前缀 + 表名 + 触发器名(方式I,U,D)。

8、 函数

前缀为Func_ 。按业务操作命名函数

9、 序列

前缀为Seq_ 。按业务属性命名。

10、普通变量

前缀为Var_ 。 存放字符、数字、日期型变量。

11、游标变量

前缀为Cur_ 。存放游标记录集。

（二）、命名

1、 大小写

名称一律大写，以方便不同数据库移植，以及避免程序调用问题。

2、 命名长度

表名、字段名、视图名长度应限制在20个字符内(含前缀)。

3、 字段名称

同一个字段名在一个数据库中只能代表一个意思。比如XH在一个表中代表“学号”的意思，在另外一个表中就不能代表“学号”的意思。 不同的表用于相同内容的字段应该

采用同样的名称，字段类型定义。

4、 字符型

固定长度的字串类型采用char，长度不固定的字串类型采用varchar2。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。

5、 数字型

数字型字段尽量采用number类型。

6、 系统时间

由数据库产生的系统时间首选数据库的日期型，如DATE类型。

7、 外部时间

由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：YYYY-MM-DD HH24:MI:SS。

8、 大字段

如无特别需要，避免使用大字段(blob，clob，long，text，image等)。如使用时必须使用 blob,或 clob 字段。

9、 唯一键

对于数字型唯一键值，尽可能用系列sequence产生。 （如果做为唯一键，不能作为索引）

（三）、完整性设计原则

1、 主键约束

关联表的父表要求有主健，主健字段或组合字段必须满足非空属性和唯一性要求。对于数据量比较大的父表，要求指定索引段。

2、 外键关联

对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量比较大的标要求对外健建立索引。对于有要求级联删除属性的外键，必须指定on delete cascade 。

3、 NULL值

对于字段能否null，应该在sql建表脚本中明确指明，不应使用缺省。由于NULL值在参加任何运算中，结果均为NULL。所以在应用程序中必须利用nvl()函数把可能为NULL值得字段或变量转换为非NULL的默认值。例如：NVL（sale,0）。

4、 Check条件

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

5、 包设计

存储过程、函数、外部游标必须在指定的数据包对象PACKAGE中实现。存储过程、函数的建立如同其它语言形式的编程过程，适合采用模块化设计方法；当具体算法改变时，只需要修改需要存储过程即可，不需要修改其它语言的源程序。当和数据库频繁交换数据是通过存储过程可以提高运行速度，由于只有被授权的用户才能执行存储过程，所以存储过程有利于提高系统的安全性。存储过程、函数必须检索数据库表记录或数据库其他对象，甚至修改（执行Insert、Delete、Update、Drop、Create等操作）数据库信息。如果某项功能不需要和数据库打交道，则不得通过数据库存储过程或函数的方式实现。在函数中避免采用DML或DDL语句。

在数据包采用存储过程、函数重载的方法，简化数据包设计，提高代码效率。存储过程、函数必须有相应的出错处理功能。

6、 触发器

触发器是一种特殊的存储过程，通过数据表的DML操作而触发执行，起作用是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。

触发器的before或after事务属性的选择时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。在大型导入表中，尽量避免使用触发器。

7、 注释

表、字段等应该有中文名称注释，以及需要说明的内容。

8、 索引设计

对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。

9、 视图设计

视图是虚拟的数据库表，在使用时要遵循以下原则：

从一个或多个库表中查询部分数据项；

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

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

视图中如果嵌套使用视图，级数不得超过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、 Char 比 varchar2 查询时高效

在进行查询及建立索引时，char比varchar2的效率要高，当然varchar2在存储上比char要好

5、 避免嵌套的Select子句

这个实际上是In子句的特例。例如：Select * from xsjbxx where xh in (select xh from xjydb);

6、 避免使用Select * 语句

如果不是必要取出所有数据，不要用*来代替，应给出字段列表，注：不含select count(*)。

7、 避免不必要的排序

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

8、 Insert语句

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

9、 多表连接

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

10、参数的传递

sql语句的编写，变量尽量使用“？”。

11、避免无计划的全表扫描

全表扫描连续从表读取所有数据，而不管数据是否与查询有关。避免不必要的全表扫描有两个充足理由：

（1）、全表扫描没有选择性。

（2）、过全表扫描读取的数据很快从SGA的缓冲区移走（如果正在扫描的表不是“高速存储”的表）。

在基于规则优化的情况下，如果下列任何条件在SGA语句出现，就要对一个表进行全表扫描：

（1）、 该表无索引

（2）、 对返回的行无任何限定条件（如无Where语句）

（3）、 对数据表与任何索引主列相对应的行无限定条件。例如，在City－State－Zip列上创建了三列复合索引，那么仅对State列有限定条件的查询不能使用这个索引，因为State不是索引的主列。

（4）、 对索引主列的行有限定条件，但条件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情况下都不会使用索引：

Where city is null

Where city is not null

Where city!=’liaoning’

（5）、 对索引主列的行有限定条件，但条件在表达式里使用。例如，如果在City列上索引，那么限定条件

Where City=’liaoning’

可以使用索引。然而，如果限定条件是

Where UPPER(City)=’liaoning’

那么不会使用City列上的索引，因为City列在UPPER函数里。如果将City列与文本字符串联结在一起，也不会使用索引。例如，如果限定条件是Where City||’x’ like ‘liaoning%’那么不会使用City列上的索引。

（6）、对索引主列的行有限定条件，但条件使用Like操作以及值以‘％’开始或者值是一个赋值变量。例如，在所有下列情况下都不会使用索引：

Where City like ‘%aonin%’

Where City like :City_Bind_Variable

如果表小、索引列无选择性，基于开销的优化器可能决定使用全表扫描。

（7）、只使用选择性索引

索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。

(六)、规范与反规范设计数据库

1、 规范化

范式是符合某一级别的关系模式的集合，根据约束条件的不同，一般有1NF、2NF、3NF三种范式。规范化理论是围绕这些范式而建立的。规范化的基本思想是逐步消除数据依赖中不合适的部分，使模式中的各关系模式达到某种程度的“分离”，即采用“一事一地”的模式设计原则，因此，所谓规范化实质上就是概念的单一化。数据库中数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度。故有时为了提高某些查询或应用的性能而有意破坏规范规则，即反规范化。

2、反规范化

⑴反规范的必要性

是否规范化的程度越高越好呢？答案是否定的，应根据实际需要来决定，因为“分离”越深，产生的关系越多，结构越复杂。关系越多，连接操作越频繁，而连接操作是最费时间的，在数据库设计中特别对以查询为主的数据库设计来说，频繁的连接会严重影响查询速度。所以，在数据库的设计过程中有时故意保留非规范化约束，或者规范化以后又反规范，这样做通常是为了改进数据库的查询性能，加快数据库系统的响应速度。

⑵反规范技术

在进行反规范设计之前，要充分考虑数据的存取需求，常用表的大小、特殊的计算、数据的物理存储等。常用的反规范技术有合理增加冗余列、派生列，或重新组表几种。反规范化的好处是降低连接操作的需求、降低外码和索引数目，减少表的个数，从而提高查询速度，这对于性能要求相对较高的数据库系统来说，能有效地改善系统的性能，但相应的问题是可能影响数据的完整性，加快查询速度的同时降低修改速度。

3、 数据库设计中的优化策略

数据应当按两种类别进行组织：频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据，内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据，内部设计应当物理规范化。比较复杂的方法是将规范化的表作为逻辑数据库设计的基础，然后再根据整个应用系统的需要，物理地非规范化数据。规范与反规范都是建立在实际的操作基础之上的约束，脱离了实际两者都没有意义。只有把两者合理地结合在一起，才能相互补充，发挥各自的优点。