表设计规范

2019独角兽企业重金招聘Python工程师标准>>>

此处请填入文档中的专业术语及解释。

序号	术语/缩略语	全称和说明
1	表(TABLE)	数据库中用于存储数据的基本存储单位
2	派生授权	给用户授权时，使用with grant option，使得被授权的用户可以将被授权的对象授权给其它用户
3	接口表	当通过goldengate或者ETL方式进行数据同步的时候, 在目标端创建的与源表结构一致的同构表。
4	基表	存放基础数据, 配置数据的表, 这些数据仅提供给业务处理过程使用, 而不会在业务处理过程中被修改。 基表中的数据非常稳定, 极少修改,也几乎不会外键关联到其它表。

2、设计要求规范#

2.1、表设计原则#

2.1.1 表的设计必须要满足第一范式。

在表设计中,不允许字段出现二义性。例如，表中不能有这样的字段，字段的值是由几位数字组成的代码，第一位表示客户类型，第二位表示渠道类型……，这种设计不符合第一范式，不允许出现。

2.1.2 建议1：表的设计应尽量满足第三范式。

数据库三范式的说明请参见 附录3.2 数据库三范式说明。

2.2、表创建参数规定#

2.2.1 create table语句参数不能包含storage选项，不能包含nologging选项, nologging 会影响数据库的灾备和恢复。

例如：以下建表语句，tablespace后面的部分不能包含。

create table AREA_DEFINE (   created_by      varchar2(100),   created_date    date,   updated_by      varchar2(100),   updated_date    date,   area_code       varchar2(4) ) tablespace PCISBASE_DATA
  pctfree 10   initrans 1   maxtrans 255   storage
  (     initial 5M     next 5M     minextents 1     maxextents unlimited
    pctincrease 0   ) Nologging;

正确的应该为：

create table AREA_DEFINE (   created_by      varchar2(100),   created_date    date,   updated_by      varchar2(100),   updated_date    date,   area_code       varchar2(4) ) ;

而在生产数据库版本为Oracle 9i时, 则在建表时需要加上MONITORING参数。
MONITORING参数说明：创建表时设置MONITORING参数, 使得Oracle跟踪对表的DML操作，预估DML操作所影响的记录数量。在Oracle 9i中, MONITORING参数不是默认的,需要手工执行,而在Oracle 10g及以上版本中, 该参数为默认设置。

2.3、对表字段的设计规范#

2.3.1 表中一条记录所有字段的长度不能超过该数据库的db_block_size大小

当字段总长度大于数据的db_block_size时,会产生大量的行链接,影响到表访问的效率,应当避免。因此在对表设计时,要求字段总长度不能超过数据的db_block_size大小。

字段的总长度计算方法为所有字段长度相加的总和。 如以C_INFO_TEMPLATE表为例,该表有8个字段, 它所在的库的db_block_size为8192.

COLUMN_NAME	DATA_TYPE	DATA_LENGTH
INFO_TEMPLATE_CODE	NUMBER	22
CREATED_BY	VARCHAR2	100
CREATED_TIME	DATE	7
UPDATED_BY	VARCHAR2	50
UPDATED_TIME	DATE	7
INFO_TEMPLATE_NAME	VARCHAR2	100
SPECIAL_CASE_CODE	VARCHAR2	6
IS_TEMP	VARCHAR2	2

则该表的字段长度为 22+100+7+100+7+50+6+2 = 294字节,满足规范。
可以在生产环境中使用如下sql查询表的长度:

select sum(data_length) as "total column length"   from user_tab_columns
 where table_name='&table_name';

可以在生产环境中使用如下sql获取数据块大小:

SQL> show parameter db_block_size

2.3.2 字段必须定义正确的数据类型

在设计表结构时，对于只存储数字的字段应定义成数字类型，只存储字符的字段定义成字符类型，只存储日期的字段定义成日期类型，以减少使用过程中的数据类型转换。
比如有Customer表, 字段定义如下：

Column name	Type	Is Null?
Cust_no	Varchar2(50)	N
Cust_name	Varchar2(255)	Y
Date_birth	Varchar2(50)	Y
sex	Varchar2(50)	Y

其中错误的定义有:
Cust_no字段中只存储数字。所以需要定义为number, 不能定义为varchar2。
date_birth字段应该定义为date, 不能定义为varchar2。
sex字段的定义过长,只需要定义varchar2(1)就可以了，我们记录性别为F（女）或M（男）。

2.3.3 在表中不允许使用Long类型字段; 可以使用Lob数据类型字段, 但在Oracle 11g中, 必须使用secure file。
LOBs数据对象分为两类:

• 1)内部LOBs, 内部LOB是指存储在数据库中的大对象,包括BLOB,CLOB,NCLOB.
  
  • BLOB : 使用二进制存储的大对象,比如存储图像,音像和视频,或者文件.
    
  • CLOB : 使用数据库字符集存储的字符型数据的大对象.
    
  • NCLOB: 使用数据国家字符集存储的字符型数据的大对象.
    

• 2)外部LOBs, 外部LOB是指存储在操作系统,而非存储在数据库中。数据库通过BFILE来获取外部LOB。
  
  • BFILE : 指向存储在操作系统的外部LOBs的一种数据类型。
    

使用LOB类型, 有如下参数需要设置:

• 1. LOB数据必须存储在单独的LOB表空间

因为对LOB字段进行update和Delete操作时候,它的Undo信息不是放在Undo 表空间中, 而是放在自己的LOB段中。 否则如果LOB数据存放在业务表空间时，当对LOB数据更新操作时, 只要业务表空间还有足够剩余, 这样对与业务表空间的剩余存储空间预估带来很大不确定性。

• 2. ENABLE/DISABLE STORAGE IN ROW的设置
  • ENABLE STORAGE IN ROW: 表示允许小于4000 bytes 的LOB存放在数据行中,而只将大于4000 bytes的数据存放在LOB段中。
    
    • 利: LOB数据总是随表block一同读取,无需对每一行进行额外访问LOB段来获取LOB数据。
      
    • 弊: 会使得每个block所包含的rows大为下降, 当访问相同量的数据时,需要访问更多的块。对执行效率有较大影响。
      

• • DISABLE STORAGE IN ROW: 表示无论LOB 数据是否大于4000 bytes, 都存储在LOB段中。
    
    • 利: 由于LOB数据不与LOB行一起存储,使得表中每个block中可包含较多的rows, 当在不需要读取LOB字段时(包括SQL执行计划中子步骤返回的行数),会大大减少所需读取的block数量。
      
    • 弊: 每一行LOB数据的读取都需要额外访问LOB段的3个block, 当需返回较多LOB数据时,会读取大量的block块。
      

因此,在使用LOB字段时必须设置DISABLE STORAGE IN ROW。
仅在满足以下条件:
只有在最终结果集中会返回该数据行的LOB数据时, 才会对该数据行所在的block进行读取。 且满足以下两个条件之一
a) 该表从不会与其它表关联使用.
b) 如果有与其它表关联,则总是通过主键或者索引进行关联.
才使用ENABLE STORAGE IN ROW。

• 3. 合理设置storage as (CHUNK bytes) 参数

该参数是针对LOB存储段的设置,最小的LOB CHUNK的大小必须是数据库块(DB_BLOCK_SIZE)的整数倍。CHUNK的最大值为32k, 且须小于表空间的extend中NEXT的值。由于在一个CHUNK块中最多只能存储一行LOB数据,因此CHUNK的设置会影响到LOB数据的读取效率和磁盘使用率。
分别以LOB size为5K 和25K大小为例：
LOB size为5K :

chuck设置	存储LOB所占用磁盘大小	磁盘利用率(%)	I/O读取次数
8K	8K	62.5	1
16K	16K	31.25	1
32K	32K	15.63	1

LOB size为25K :

chuck设置	存储LOB所占用磁盘大小	磁盘利用率(%)	I/O读取次数
8K	32K	78.13	4
16K	32K	78.13	2
32K	32K	78.13	1

因此,基于性能方面的考虑, 在新建表时可参考如下对Chunk参数进行配置:

	Db_Block_size: 8K	Db_Block_size: 16K	Db_Block_size: 32K
LOB数据主要分布区间在【0,8k】	8K	16K	32K
LOB数据主要分布区间在【8k,16k】	16K	16K	32K
LOB数据主要分布区间在【16k,32k】	32K	32K	32K
LOB数据主要分布区间大于32k	32K	32K	32K

如果有需要对存储和性能进行综合考虑,可在该表使用一段时间后, 对LOB字段数据情况进行分析
可使用下面的sql对LOB字段进行全表分析
《数据库开发管理规范_全表分析LOB.rar》
如果LOB表数据量较大,也可使用下面的sql对LOB字段进行采样的分析
《采样分析LOB说明.rar》
在Oracle 11g的Securefile 方式中, CHUNK 的最大值为64MB,且会自动适应LOB数据的大小, 因此无需设置CHUNK参数。

• 4. 必须设置为storage as (nocache)

storage as (nocahce) 表示LOB段不经过buffer cache缓存，直接读与直接写。这样可以提高对其它数据块在buffer cache的命中率，减少因为使用LOB而对整个业务的影响。

• 5. 必须设置为storage as (logging)

Logging属性只对nocache方式生效。
因为nologging方式对LOB段做的DML操作不记录redo日志，会影响Data Guard的同步，所以必须采用logging方式。

• 6. 必须设置PCTVERSION为默认值10

PCTVERSION用来管理LOB字段镜像数据的空间百分比，在LOB数据的更新过程中，ORACLE没有用UNDO TABLESPACE，而是从LOB字段所在的表空间里划分一段空间来做镜像空间的, 这个空间的大小由PCTVERSION参数控制，默认值为10，代表划分表空间的10%作为镜像空间, 每个镜像空间的单元大小由CHUNK参数决定。

举例: 创建带有LOB字段的表

create table customer (  id_customer       raw(32),    customer_name    varchar2(100),    customer_photo    clob
 )  
lob (customer_photo) store as      ( tablespace  hrmslobdata
       disable storage in row
       chunk  32k        pctversion 10        nocache logging
     );

• 7. 如何预估存储在数据库中LOB数据的字节数

使用LOB类型时,对存储在LOB字段数据的字节数进行预估很重要,对Storage 参数设置,以及占用磁盘空间估算都有影响。
主要有两方面的影响:

• • a) 字符集设置的影响, 当数据库使用AL32UTF8 时,会使用3 个byte 存储汉字, 1个byte 存储英文字符。而如果数据库使用GBK字符集, 则使用2个byte存储汉字，1个字节存储英文字符。
  • b) 如果LOB数据会大于一个Block size , 还需要考虑PCT_FREE 设置的影响。PCT_FREE 表示在每个Block中保留的字节数, 默认为10%。
    

因此: 数据库使用AL32UTF8字符集时：
LOB数据字节数= (汉字字符数*3 + 英文字符数*1 )/ ( 1 - PCT_FREE )
数据库使用GBK时: LOB数据字节数= (汉字字符数*2 + 英文字符数*1 )/ ( 1 - PCT_FREE )
如果使用BLOB字段类型,BLOB数据长度与原二进制文件大小一致。

• 8. 在Oracle 11g 中,必须使用secure file 方式

SecureFiles功能是Oracle11g数据库对LOB数据类型的重新设计，可大幅度的提高性能、可管理性和简化应用的开发。SecureFiles 集外部文件和数据库 LOB 方法的优点于一身，可以存储非结构化数据，允许加密、压缩、重复消除等等。
使用SecureFiles 方式无需设置CHUNK, PCTVERSION 参数. 在Oracle 11g中CHUNK的大小可变,且最大值为64MB。
可以参考例子如下:

create table customer (  id_customer       raw(32),    customer_name    varchar2(100),    customer_photo   blob
 )  
lob (customer_photo) store as securefile
     ( tablespace  hrmslobdata
       disable storage in row
       nocache logging
    );

Securefile用法和测试按列请见：附录3.3。

2.3.4 表和字段必须有comment中文注释

表和字段必须有中文注释，注释采用comment on的形式，如：
Comment on table AREA_DEFINE is ‘地区定义表’;
Comment on column AREA_DEFINE.AREA_CODE is ‘地区代码’;
2.3.5 为了保证表中数据的完整性，在设计表时，必须考虑给各字段加上适当的约束

约束的类型有非空约束，唯一性约束，主键约束，外键约束，check约束等;
Check 约束仅适用于Boolean类型字段的检查需求, 或者是一些二元属性的字段,比如表示性别字段,只有’F’,’M’ 两种值,也可使用Check约束。而对于其它的列值检查必须采用基表+主外键的方式。

2.3.6 除日志表、临时表外，其它新建表中，必须有数据创建人，创建时间，修改人，修改时间这4个字段，四个必须字段异动的内容必须通过trigger方式实现，不能通过程序代码实现，字段名及数据类型、长度须与下面的保持一致。

创建人	created_by	varchar2(100)	not null;
创建时间	created_date	date	not null;
修改人	updated_by	varchar2(100)	not null;
修改时间	updated_date	date	not null;

注意：这四个审计字段仅适用于记录该表自身数据的变化，如果以该表为目标表的源表需要同步审计字段信息，则要求该目标表新建四个审计字段存放源表数据，新字段命名需和目标表审计字段区分开来，参考示例：

源创建人	src_created_by	varchar2(100)	not null;
源创建时间	src_created_date	date	not null;
源修改人	src_updated_by	varchar2(100)	not null;
源修改时间	src_updated_date	date	not null;

2.3.7 对于通过UM登陆系统或者直接用脚本来操作数据的情况，created_by，updated_by必须插入员工UM编码 。

2.3.8 所有外键上都必须创建索引。

若没有对外键建立索引，则在对父表DELETE操作或者UPDATE关联父表的键值操作时，会对子表产生全表独占锁，引发性能问题。

2.3.9 所有表必须要主键，除了temparory tablespace 中的临时表以外.

2.3.10 创建主键时必须先创建索引,再创建主键。

先创建唯一索引,再基于该索引创建主键。
在删除主键时若遗忘指定keep index，则删除主键的同时会将主键索引也一并删除。若仍然有SQL需使用该索引时就会引发性能问题，而且要重建该索引也可能需要花费较长时间。
因此需采用先创建索引再创建主键的方式，避免上述情况的发生。
以CUSTOMER表为例,现在要在ID字段创建主键

col_name	type	is_null
id_cust	varchar2(32)	N
cust_name	varchar2(255)	Y
date_birth	Date	Y
sex	varchar2(1)	Y

正确的脚本:

create unique index pk_customer_id on customer (id_cust); alter table customer add constraint pk_customer_id primary key (id_cust) using index pk_customer_id;

错误的脚本:

alter table customer add constraint pk_customer_id primary key (id_cust) ;

2.3.11 除接口表和基表的主键字段以外, 所有主键字段必须使用DB级 sys_guid值作为pk值, 字段类型统一为VARCHAR2(32)。对应子表的外键字段也应该使用VARCHAR2(32)，保持类型相同。

例:

Insert into customer (id_cust, cust_name, date_birth, sex) values ( sys_guid() ,’Jimmy’,null,’M’);

接口表和基表的命名规则请参考命名规范。

2.3.12 选择主键必须遵行的原则

• 1、 主键字段必须不能为空值

这个是主键约束的一个基本前提，主键所在的字段上，不允许有空值出现。

• 2、 永远不会改变.

主键设计的初衷就是为了唯一标示一条记录，因此它本身是不应该发生变化的。一个好的主键设计是，所有主键字段的值决不会随着以后业务需求的变化而发生改变，而是一成不变的，一旦生成就不再改变。

• 3、 本身不是识别值

主键字段的值并不应该有其它任何含义，仅仅是其作为主键的标示意义。因为具有含义的值通常会随着业务需求的变化而发生改变。也就是说, 在业务系统中, 主键除了作为表关联以外,不会出现在任何过滤条件中。
例如, 有一张表ims_phone_number记录了客户接收短信的手机号码。在这张表中,手机号码字段phone_number是唯一的,不能重复。 如下:

col_name	type	is_null
phone_number	number(20)	N
cust_id	number	N
status	varchar2(1)	N
date_register	date	N

但在选择主键时,不能使用phone_number字段作为主键, 因为在这个业务中, 手机号码具有实际意义,它在现实世界中就是一个接收终端的标识,而不仅仅是在数据库中。因此需要另外增加一个字段 Id_ims_phone_number 来作为主键, 该字段类型为varchar2(32),使用SYS_GUID值做为主键.
如下:

col_name	type	is_null
Id_ims_phone_number	varchar2(32)	N
phone_number	number(20)	N
cust_id	number	N
status	varchar2(1)	N
date_register	date	N

• 4、 选择单一字段来做主键

选择单一的字段列来作为主键，这样与其它表关联时，关联关系最简单。对于在逻辑上有多个字段才能确定唯一的情况, 不允许在多个字段创建复合类型的主键,而是需要添加一个字段作为主键, 字段类型为varchar2(32), 使用SYS_GUID值做为主键。
如以下表为例:

col_name	type	is_null	description
id_cust	varchar2(32)	N	客户ID
id_couse	varchar2(32)	N	课程ID
course_score	number	Y	选修得分

id_cust字段与id_course字段这两个字段是复合唯一的,但不能在这两个字段上创建复合主键,而是需要增加一个字段id_cust_grade字段作为主键, 使用SYS_GUID值做为主键,字段类型为varchar2(32)。而在id_cust和id_course两个字段上创建唯一索引。如下:

col_name	type	is_null	description
id_cust_grade	varchar2(32)	N	ID
id_cust	varchar2(32)	N	客户ID
id_course	varchar2(32)	N	课程ID
course_score	number	Y	选修得分

2.3.13 不允许修改表上已有的主键结构
表上的主键一旦确定，将不允许被修改，因为修改主键将会带来一系列的问题。
除非因为业务规则发生改变而导致不得不修改主键的情况发生, 此时修改主键必须满足以下条件：
a) 新主键定义必须要满足规范2.3.12
b) 原主键定义不满足规范2.3.12.3
c) 原主键不是其它任何一张表的外键
当主键是其它子表的外键时，修改主键会导致以它作为外键的子表上产生锁，如果子表在这个外键上有索引，则会产生行锁，如果没有索引，则会产生表锁。
删除原有主键约束时，必须评估主键对应的索引是否需要保留, 如果不要保留,需要使用Quest SQL Optimizer工具的impact analyzer 进行对比测试，评估变更影响。
若删除了主键而没有保留索引，则根据原主键查询的SQL语句将不再走原主键的唯一索引，执行计划发生改变，极有可能导致SQL出现性能问题，此时需要评估这种改变带来的性能问题。

2.3.14 建议3: 所有业务上有主外键关系都必须在数据库中创建主外键。

2.3.15 唯一键的创建方式，与主键相同,即先建索引，然后加唯一键

2.3.16 在修改表时，如果增加有default值的列或者修改字段的非空属性，必须先做ddl增加字段，再做dml更新值，然后再做ddl增加字段的default属性或非空属性。但如果所修改数据库是Oracle 11g版本,则无须此操作。

• 1. 对于Oracle 11g以前版本：

例如：对于某个table，增加一个有default值的列，不允许直接使用如下ddl来实现：

alter table test1 add is_lbs varchar2(6) default 'YES';

需要改为ddl,dml和ddl三个脚本：

alter table test1 add is_lbs varchar2(6);      
update test1 set is_lbs='YES' where ……; (该dml语句需要支持断点续做和分段提交） alter table test1 modify is_lbs default ‘YES’; 

修改表的某字段为 not null，应该先将该列值为空的记录update为非空，再增加not null约束。例如：对于某个table，修改字段A为 not null ：

update test set A=’’ where A is null ……; （该dml语句需要支持断点续做和分段提交） alter table test modify A not null;  

对表增加有default值且not null的列，应该先做ddl增加字段，再做dml更新值。
例如：对于某个table，增加一个有default值且not null的列，需要将如下ddl：

alter table test1 add is_lbs varchar2(6) default ‘YES' not null;

改为ddl,dml和ddl三个脚本：

alter table test1 add is_lbs varchar2(6); update test1 set is_lbs='YES' where …...;  (该dml语句需要支持断点续做和分段提交 ) alter table test1 modify is_lbs default ‘YES’ not null; 

对于表数据的备份，请参见《DML数据备份修改管理规范》。

• 2. 对于Oracle 11g,可以直接使用添加一个指定默认值的新列。如上面的修改在Oracle 11g中可直接执行下面脚本。

alter table test1 modify is_lbs default ‘YES’ not null;

Oracle 11g 会把默认值存储在数据字典中,而不需要先对表做update操作
2.3.17 应用程序中若需要truncate表中的数据，必须采用DBA统一编写的truncate_table过程来实现。不允许为任何业务用户授予drop any table的权限。

2.3.18 对于大表的定义: 将容量超过2G的表定义为大表。

在数据库设计时需要对大表考虑是否进行分区,数据清理,以及归档操作。

3 附录#

3.1. 表创建案例#

《 案例.txt 》

3.2. 数据库三范式说明#

规范化是组织数据的一个过程,当创建表以及表之间的关系时,通过消除冗余和不一致的依赖来保护数据,以及使数据库更灵活。
冗余数据浪费磁盘空间,并由此带来维护的问题。如果同一数据被存储在多个地方,那么必须按照相同的方式修改所有地方的数据。 但如果该数据只存储在一个地方,比如客户的地址只存放在客户表中,那么修改起来就非常的容易。
不一致的依赖是指这样的情况：我们从客户表中查看客户的地址,是符合通常认识的；但如果要去客户表中查看负责该客户雇员的薪水,这样的设计就不容易理解。雇员的薪水应该是在雇员表中，依赖于雇员属性。不一致的依赖会使数据难以访问,甚至在某些时候,数据访问路径可能会发生丢失或者被破坏。

• 第一范式:
  • 1. 消除单个表中重复的属性。
  • 2. 为每一组属性创建单独的表。
  • 3. 使用主键标识每一组属性。

不要在单个表中使用多个字段来存储类似的数据。如下的例子:

Student Advisor Adv-Room Class1 Class2 Class3
----------------------------------------------------------------------------------------------
1022 Jones 412 101-07 143-01 159-02
4123 Smith 216 201-01 211-02 214-01

一个student 会对应多个class, 但上表中使用class1,class2,class3 的设计方式会带来麻烦。满足第一范式的设计应该为:

Student Advisor Adv-Room Class
------------------------------------------------------------------
1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 201-01
4123 Smith 216 211-02
4123 Smith 216 214-01

• 第二范式:
  • 1. 对于可应用于多个表的属性,创建单独的表来保存。
  • 2. 使用外键对这些表进行关联。

表中的数据只应该依赖于表的主键。例如在财务系统中客户的地址, 会在客户,订单,发货,发票,收帐的环节都需要。因此应该将客户的地址放在单独的一个表中,比如客户表或者地址表。而不是将它在以上每一个表都保存一份。继续上面的例子,虽然已经满足第一范式,但注意到Class字段并不是函数依赖主键student,不满足第二范式。需要将class拿出来和student 单独组成一个表,以满足第二范式:

Student:
Student Advisor Adv-Room
-----------------------------------------------------
1022 Jones 412
4123 Smith 216

Registration:
Student Class
-------------------------------------
1022 101-07
1022 143-01
1022 159-02
4123 201-01
4123 211-02
4123 214-01

• 第三范式:
  • 1. 消除不依赖于主键的属性。

表记录中如果某个属性并不直接依赖于表的主键,那么这些属性应该被拿出来,单独放在一个表中。在上面的例子中student表中, Adv-Room (the advisor's office number) 是函数依赖于Advisor,而非student,因此需要将Adv-Room 属性放入另外的表中,以满足第三范式。

Student:
Student Advisor
----------------------------------
1022 Jones
4123 Smith

Faculty:
Name Room Dept
-----------------------------------------
Jones 412 42
Smith 216 42

转载于:https://my.oschina.net/u/729507/blog/88672