数据库设计规范.doc

数据库设计规范（一）、命名规范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）、 对数据表与任何索引主列相对应的行无限定条件。例如，在CityStateZip列上创建了三列复合索引，那么仅对State列有限定条件的查询不能使用这个索引，因为State不是索引的主列。（4）、 对索引主列的行有限定条件，但条件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情况下都不会使用索引：Where city is nullWhere city is not nullWhere 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、反规范化反规范的必要性是否规范化的程度越高越好呢？答案是否定的，应根据实际需要来决定，因为“分离”越深，产生的关系越多，结构越复杂。关系越多，连接操作越频繁，而连接操作是最费时间的，在数据库设计中特别对以查询为主的数据库设计来说，频繁的连接会严重影响查询速度。所以，在数据库的设计过程中有时故意保留非规范化约束，或者规范化以后又反规范，这样做通常是为了改进数据库的查询性能，加快数据库系统的响应速度。反规范技术在进行反规范设计之前，要充分考虑数据的存取需求，常用表的大小、特殊的计算、数据的物理存储等。常用的反规范技术有合理增加冗余列、派生列，或重新组表几种。反规范化的好处是降低连接操作的需求、降低外码和索引数目，减少表的个数，从而提高查询速度，这对于性能要