ORACLE设计规范34页doc.docx

ORACLE设计规范34页doc.docx

《ORACLE设计规范34页doc.docx》由会员分享，可在线阅读，更多相关《ORACLE设计规范34页doc.docx（27页珍藏版）》请在冰豆网上搜索。

ORACLE设计规范34页doc

ORACLE设计规范

1、数据库模型设计方法规范

1.1、数据建模原则性规范

原则

对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑模型图是项目组成员之间在数据库层面沟通交互的依据，必须规范画图（表，主键，外键，关系）

衡量

对于表的个数在20个以上的模型，需要数据组参与设计，并需DBA作最终审核

方法

对于OLTP系统，采用范式化思想进行模型设计，对于OLAP系统，采用面向问题及多级颗粒度的思想进行模型设计

实施

采用主流的模型设计软件工具PowerDesigner，ERStudio，ERWin

1.2、实体型之间关系认定规范

原则

所有实体型间的业务逻辑关系，除了语义上保留其原有的业务关系外，本质上都要转化成关系数据库的三种关系（1:

1）（1:

N）（N:

M）

衡量

对于3个及以上实体型之间的“多元关系”，需要数据组参与设计

方法

比如实体型A和实体型B之间的关系，可以通过问两个问题来确定他们之间的关系：

一个A可以对应几个B？

一个B可以对应几个A?

（1）一个A对应一个B，相反一个B对应一个A，那么A对B就是1:

1关系；

（2）一个A对应多个B，相反一个B对应一个A，那么A对B就是1:

N关系；

（3）一个A对应多个B，相反一个B对应对个A，那么A对B就是N:

M关系；

实施

（1）1:

1关系选取任何一个表的主键到另一个表中作为外键来体现；

（2）1：

N关系将1表的主键在N表中以外键形式存在来体现；

（2）N:

M关系采用“关系表”来体现，该关系表的主键是由相关实体表的主键组成的符合主键，各实体表主键不但组成了该关系表的主键，同时也被看作外键在该关系表中存在；

（4）对于三个以上表之间的“多元关系”常需要和反范式化冗余字段结合起来设计，以保证查询速度；

1.3、范式化1NF的规范

原则

OLTP系统的模型，需要符合第三范式

衡量

对于表在20个以上的模型，需要数据组参与设计

方法

范式化要求：

INF：

列是访问的最小单位，具有原子性，不可再被分割；

实施

依据具体情况对相应属性进行拆分或者合并。

范式化1NF常见现象：

现象一：

同一个属性值的不同细度把握，比如，常见的“姓名”这个属性，设计一：

“姓名”是一个列，设计二：

“姓”是一个列，“名”是一个列，两个列的值组合起来才表达一个“姓名”语义。

两种设计方法，在不同的系统中都有应用，这主要是依据需求的细度来确定，灵活把握；

现象二：

把多个属性值错误的作为一个属性值存储，比如：

常见的OA系统要存储员工的各种属性，包括技能信息，技能范围：

Oracle，JAVA，.NET,C#,Perl,UNIX等等，一种常见的错误设计是：

设计一张员工表，其中有一个技能属性字段，然后某员工所掌握的多种技能用逗号（，）间隔，然后将这个字符串存储到这个员工表的技能属性字段中。

这里的错误在于将多个属性值作为一个属性值存储在一个字段中，不能满足直接遍历员工对某个技能掌握情况，而且如果再要求说明员工对个技能的掌握程度（精通，熟悉，一般等等），则再增加字段，里面的对应关系将很容易错乱，这是严重违反1NF的情况。

正确的设计应该是：

两个实体表：

一张是员工表，一张是技能字典表，一个员工可以掌握多个技能，也就是（1:

N）关系，相反一个技能可以被多个员工掌握，也是（1:

N）关系，双向都是（1:

N）关系，那么综合起来员工和技能之间就是“多对多关系（N:

M）”，依据前述规范，应该设计一张“关系表”来存储“多对多关系”，主键为复合主键（员工主键+技能主键），该关系有一个属性“技能掌握程度”。

1.4、范式化2NF的规范

原则

OLTP系统的模型，需要符合第三范式

衡量

对于表在20个以上的模型，需要数据组参与设计

方法

范式化要求：

2NF：

满足1NF,不存在非主键属性对主键属性的部分依赖；

实施

范式化2NF常见现象：

实体表中一般不会出现违反2NF的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反2NF的情况。

主要是该关系表非主键外的属性，本该属于相关的某个实体表的，却放到了该关系表中，这使得该属性不能通过该关系表的复合主键唯一确定，DML操作会发生错误；如果违反了2NF，那么应该把这个属性从关系表中拆分，也许会单独形成一个表，绝大部分情况下是将该属性归并到某个相关的实体表中；

违反2NF的例子：

学生考试情况中，有两个实体表：

学生表和学科表，学生与学科之间的考试关系就是N:

M的关系，就要创建一张关系表存储该多对多的考试关系，表的主键为学生编号和学科编号，属性为考试分数；那么“任课老师”该放在那里呢？

如果放到考试关系表中，那么安排任课老师，必须先进行考试，这显然不符合实际，也就是任课老师不该依赖于学生编号和学科编号，只是依赖于学科编号，也就是说任课教师信息应该放在学科表中；

1.5、范式化3NF的规范

原则

OLTP系统的模型，需要符合第三范式

衡量

对于表在20个以上的模型，需要数据组参与设计

方法

范式化3NF要求：

3NF：

满足2NF，不存在非主键属性对主键属性的传递依赖；

实施

范式化3NF常见现象：

违反3NF的情况，绝大多数是在含有外键的表中；比如A表中的外键字段Bkey是B的主键，那么依赖于Bkey的属性应当属于B表的属性，而不是A表，如果放入A表，则这些对A表的主键Akey的依赖，首先是依赖于A（BKey），而后通过A（BKey）对A（AKey）的依赖，传递依赖于A（Akey）；三种关系（1:

1,1:

N，N:

M）都含有外键，都很可能发生违反3NF的情况。

违反3NF的后果：

会导致那些问题属性插入异常，或者被误删。

违反3NF的例子：

教师和学科之间，存在着上课关系，假设一个教师上一门课而且一门课只有一个教师上，那么该关系为1:

1关系，将教师表的主键教师编号在学科表中以外键形式存在就表达了该1:

1关系，那么教师的“联系电话”属性该放哪里呢？

如果看到“教师编号”出现在了学科表中，就将联系电话放入学科表中，那么联系电话首先是对表中的教师编号依赖，再依据教师编号对学科的依赖，达到了学科编号的依赖，那么联系电话对学科编号的依赖就是传递依赖，违反了3NF，应该将其从学科表中拆出来放入教师表中，不然的话，会发生操作异常，比如，假设一个教师已经存在但是还没有为其分配科目，那么他的电话就无法存入库中。

1.6、反范式化冗余字段使用规范

原则

OLTP系统中在完成范式化工作之后，对某些表，可以适当反范式化增加冗余字段以提高数据访问性能；在OLAP中采用的是面向问题的设计思想，应该大量使用反范式化冗余信息

衡量

当SQL关连查询涉及到4张表时可考虑采用冗余字段

方法

常用在两个地方：

（1）关系表中的冗余：

在关系表中增加相关实体表的相关属性，以达到关连查询时减少表的关联数量的目的

（2）层次关系中的冗余：

在多层次的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”中；

反范式化冗余字段实例：

（1）关系表中的冗余：

比如在考试关系中，原本在学科表中的学分信息，可以冗余添加到考试关系表中，这样，每个学生得了多少学分，就可以直接从考试表得到，而无需关联学科表来得到；

（2）多层关系中的冗余：

假设为之范畴从大到小有国家表，省份表，城市表，城区表，社区表，它们之间的层次关系是通过上一级的主键在下一级中以外键形式存在来体现的，但是，如果需要问：

某个设计属于哪个国家？

这样就要关连查询所有的5张表，性能会很差，这时可以将国家编号以外键形式放入到社区表中做冗余，这样直接关联国家表和社区表即可得到答案。

一般的，每间隔一级增加一个冗余外键，比如将国家编号放入城市表中，将城市编号放入社区表中

实施

如何保证冗余字段数据的正确性（一致性）是反范式化的关键，需要对冗余字段详细添加注释，说明冗余了什么，以及该字段的维护方法，常用维护方法如下：

（1）如果在程序开发前设计的冗余字段，可以在正常的业务逻辑程序中一并处理

（2）如果是程序完成之后增加的冗余字段，可以使用触发器维护（3）对于OLAP中大量存在冗余字段，可能需要使用单独的处理任务进行维护

1.7、数据库对象命名基本规范

第一：

长度规范：

凡是需要命名的对象其标识符均不能超过30个字符，也即：

Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符；

第二：

构成规范：

数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用DUAL作表名；

第三：

大小写规范：

 构成Oracle数据库中的各种名称（表明，字段名，过程名，视图名等等）的所有字符，必须使用大写，也就是不能在脚本中，对任何名称添加双引号“”来设定字符的大小写形式，只要不采用“”限制，Oracle自动会将各名称转化成大写。

2、表的设计规范

2.1、表的主键规范

遵循如下三点原则：

第一：

有无原则：

除临时表和外部表，以及流水表，日志表外，其他表都要建立主键；第二：

构成原则：

主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；第三：

创建原则：

对于500万以上的表，请数据组参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键；

2.2、表的主键列规范

对于实体表，主键就是一列，就是没有任何语义的自增的NUMBER列，对于关系表，主键就是相关实体表主键形成的复合主键，是多列；

2.3、使用注释的规范

原则

每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段说明参照与那个表

衡量

原则上谁设计谁注释

方法

查询字典表user_tab_comments和user_col_comments可知道表和字段的注释信息

实施

对表添加注释：

SQL>comment on  table   is'xx';

对字段添加注释：

SQL>commentoncolumn. is'xx';

2.4、一个表所含字段总长度的规范

原则

一个表中的所有字段，应当能存储在一个数据块中（BLOCK），也即：

表的单行字段总长度

衡量

对不含有大对象数据类型字段的表，字段数大于50个的，请数据组参与设计

方法

查询字典表USER_TAB_COLUMNS中的字段DATA_LENGTH得到表中所有字段的总长度，再依据db_block和表的pctfree参数可以判断是否一个数据行可以存储在一个数据块（BLOCK）中

实施

如果所有字段的总长度超出了一个数据块，那么需要将该表拆分成两个（甚至多个）表，拆分的依据是字段的频繁使用程度，也就是频繁使用的字段在一个表中，很少被使用的字段放在另一个表中，他们之间使用相同的主键值，用主外键关联。

这点就是“一个表所含字段访问频繁度的规范”

2.5、一个表所含字段访问频繁度的规范

原则

一个表中的各字段的访问频繁度应该基本一致

衡量

如果一个表的字段数超过50个， 请数据组参与审核

方法

如果一个表的字段数过多超过50个，并且依据业务逻辑确定该表中一些字段频繁被访问，另一些字段则很少被访问，则该表需要做拆分处理，这在OLAP系统中比较常见；

目的：

这样可以避免读取频繁信息时多读取很少被访问的信息，可以提高IO性能，减少内存耗费；

实施

将访问频繁度相差太远的字段拆分到两个表中，一个表存频繁访问的字段，另一个表存很少被访问的字段;

2.6、一个表所含数据量的规范

原则

一个非分区表中的数据量不要超过500万

衡量

一个非分区表中