NC数据库设计规范.doc

数据库设计规范
----NC数据库小组----
版本
V5
注意：
标有 ★ 的条目表示强制性规范。
变更记录
日期
变更内容
变更原因
变更人
2003-06-23
数据类型中对定长字符由使用varchar2 改为 char
就应该用char,原来笔误
段玲
2005-8-30
增加支持的操作系统和数据库版本
产品发展
林大瀚、李奎阳、陈岑
2005-8-30
重新整理模块命名规范，分离资金、预算、人力资源
产品发展
陈岑
2005-8-30
增加一些对数据类型的值域范围界定
以前没有精确的界定
林大瀚、陈岑
2005-8-30
增加“空值”规范
新版不允许使用“空值”
李奎阳
2005-8-30
增加两条索引规范
产品实施需要
李奎阳
陈岑
2005-8-30
精简产品配置数据库脚本规范
其他规范中已经涉及
陈岑

一、 概述
本手册涉及两个方面的内容：
A、指导软件开发过程中对数据库设计文档的管理。数据库设计文档主要包括以下部分：
l 数据库设计的概念数据模型，即CDM文档
l 数据库设计的物理数据模型，即PDM文档
l 由PDM生成的数据库建库脚本。
l 其他不在PDM文档中表达的数据库脚本，比如视图、触发器、索引等。
B、规范数据库设计文档中的内容，如命名规则、数据类型的使用等。
本手册会随着NC的发展、开发技术的提高、各种系统平台的更新而逐步调整。

二、 支持数据库及版本★
目前主要支持ORACLE,DB2,SQLSERVER三种数据库.

操作系统
ORACLE 8I(8.1.7)
ORACLE 9I（9.2.0.4或更高）
ORACLE 9I（10G）
Win NT
考虑升级操作系统
考虑升级操作系统
考虑升级操作系统
Win 2000
√
√
√
Win 2003
√
√
√
Sun Solaris 2.6 or late
√
√
√
IBM AIX 4.2 and later
√
√
√
SCO UnixWare 7.0
√
√
√
Linux
√
√
√
HP Unix 11.00 or later
√
√
√

操作系统
SQL SERVER 7.0
SQL SERVER 2000（sp3）
SQL SERVER 2005
Win NT
考虑升级数据库
√
√
Win 2000
考虑升级数据库
√
√
Win 2003
考虑升级数据库
√
√

操作系统
DB2(7.2)
DB2(8.2)
Win NT
考虑升级数据库
√
Win 2000
考虑升级数据库
√
Win 2003
考虑升级数据库
√
Sun Solaris 2.6 or later.
考虑升级数据库
√
IBM AIX 4.2 and later
考虑升级数据库
√
Linux kernel 2.2.12 or higher;
考虑升级数据库
√
HP Unix 11.00 or later
考虑升级数据库
√

三、 数据库文档的管理★
1、 采用PowerDesigner 8.0作为数据库设计工具。
2、 使用ClearCase作为数据库设计文档的版本控制工具。
3、 凡是用在PDM里面表达的数据库设计，统一由配置管理员产生厂商数据库的建库脚本。不在PDM中表达的SQL语句需要设计人员自己翻译为不同厂商数据库的建库脚本。
4、 PDM里面有关各种数据库对象的comment 字段不能为空,必须对各种数据库对象作出有助与理解的注释。

四、命名规范★
1． 模块名称规则表,一般数据库表设计的前缀默认采用以下规则,以下表的详细信息请参考NC规范中的NC产品形态文档.
模块名称
系统规则名
模块名称
系统规则名
模块名称
系统规则名
UAP
系统管理
sm
UAP
pub
财务会计平台
dap
基础数据
bd
外部交换平台
xx
管理会计平台
dmp

报表&BPM
报表
iufo
BI平台
bi
BPM
bpm
财务
总账管理
gl
收付报
arap
固定资产
fa
存货核算
ia
项目成本
jc

资金
资金结算
fts
资金监控
fvm
资金计划
fp
票据管理
fbm
信贷管理
fi
资金预测
fi
资金计息
fi
网上银行
ebank

预算
预算管理
ntb

人力资源
人力资源规划
HRP
职务职能管理
OM
员工信息管理
HI
招聘甄选管理
RM 
员工调配管理
HI
员工离职管理
HI
政策制度管理
HRPM
劳动合同管理
HRCM
培训开发管理
TRM
考勤管理
TBM
出差管理
HRBT
休假管理
HRLM
加班管理
HROM
绩效管理
PE
薪酬管理
WA
福利管理
BM
经理自助
MSS
基础设置
HR
人力资源取数函数
RPT
员工自助
ESS

供应链
采购管理
po
销售/分销管理
so
库存管理
ic
销售价格
prm
内部交易
to
供应商管理
vrm
合同管理
ct
委外加工
sc
发运管理
dm
供应链公共
scm

制造
设备管理
fm
成本管理
cm

制造基础数据
pd
需求管理
mm
生产计划
mm
生产定单
mm
车间作业
sf
能力计划
crp
装配计划
mm
检修管理
er

质量管理
质量管理
qc

其他

cdm

pm

2． 所有表、视图、触发器、索引、函数、约束、主外键必须指定名称，规则如下：
对于各种数据库对象的命名规则，目前的NC系统中存在两种风格，采用拼音和采用英文的，目前这两种规范都可以，但是注释一定要清楚.
数据库对象
规则
最大长度
例子
表
系统规则名_名称
18
sm_firm
字段名

18
acc_code, acc_name
视图
v_系统规则名_名称（*）
18
ac_accsub
触发器
t_表名_名称（*）
18
t_firm
函数
f_名称
18
f_fun()
Check
ck_表名_字段名（*）
18
ck_gl_accsub_code
主键
pk_表名
18
pk_gl_accsub
外键
fk_表名_字段名（*）--此处超过18位处理
18
fk_gl_vouch_accsub_code
索引
i_表名_字段名（*）
18
i_gl_accsub_code
表空间
ts_名称
18
ts_sys

（*）注：由于数据库对象之间的命名规则与最大长度限制可能造成命名存在冲突。如存在，可以通过缩减表名或字段字符来实现。

3． 所有的外键、约束、索引、函数、触发器、存储过程名不允许重复。

4． 业务中主子表的命名规则：
系统规则名_名称
或 主表：系统规则名_名称_h
5． 子表：系统规则名_英文名称_b。业务中主子子表的命名规则：
主表：系统规则名_英文名称_h
子表：系统规则名_英文名称_b
子子表：系统规则名_英文名称_bs

五、数据类型★
1． 可用数据类型
类型
范围
Sqlserver
Oracle
Db2
字符型
长度固定(注意：长度小于255)
Char
Char
Char
可变字符型
长度不固定(最大4000)
Varchar
Varchar2
Varchar

Oracle只能到4000，以此为准

16位整型
-32,768~32,767
Smallint
Smallint
Smallint
32位整型
-2,147,483,648 ~2,147,483,647
Int
Int
integer
精确数值型
pppppppppppppp.ss
Decimal(p,s)
Number（p,s）
Decimal(p,s)
近似数值型
-1.79E+308 ~1.79E+308
Float
Float
Float
日期型
yyyy-mm-dd。
Char(10)
Char (10)
Char(10)
日期时间型
yyyy-mm-dd hh_mm_ss
Char(19)
Char (19)
Char(19)
时间戳
System date and time
date
Datetime
date
布尔型
‘Y’ or ‘N’
Char(1)
Char (1)
Char(1)
ID/PK

Char(20)
Char (20)
Char (20)
大文本

text
Clob
clob
二进制

image
Blob
blob

2． 业务数据类型规范
这个规范需要落实到CDM/PDM的domain的标准化上，尽量使用已经定义好的域，如UFPK、UFDate等，参见pdm手册。

类型
规则(使用SQL Server描述)
编码类
字符型。依据规则确定用char还是varchar。
名称类
字符型。依据规则确定用char还是varchar。
布尔类
Char(1),Y/N
状态类
Smallint,用0,1,2,3……表示
级次类
16位整型Smallint
金额类
精确数值型，一般为decimal（20，8）
大金额
32,10
系统标识类
估计可能最大值<32,767： 16位整型smallint
估计可能最大值>32,767： 32位整型 smallint
摘要备注类
可变字符型。需要定义几种：200,1000
价格数量类
精确数值型，一般为decimal（20，8）
单价类
精确数值型，一般为decimal（20，8）
比率类
精确数值型，一般为decimal（20，8）

3． 不要用SQL 保留字来命名表、视图、字段、索引。数据库的保留字见各数据库厂商规范。
4． 字符型字段的确定。长度固定用char，不固定用varchar。
5． 不要将Null 与 空串“”视为相同。在不同的数据库中对这两者的理解是不相同的。在Oracle中空串与Null理解一样。如果碰到这种情况统一用Null.
6．空值问题：
所有经常用来做为查询条件的字段都不允许使用空值，引用基础档案的，在基础档案增加表示空值的档案，其他使用N/A表示。
六、注意事项
A 存储过程
1． 为了减少移植的困难，不要使用存储过程。如必须使用，需审批。
B 触发器
1． 尽可能的不使用触发器，尽量通过程序实现,有利与多数据库移植。
2． 不允许动态创建触发器。
C 视图
1． 使用静态视图，不允许动态创建视图。
2． 视图中不准包含order by 语法，影响效率。
3． 尽量不要出现视图嵌套视图的情况
D 索引
1． 为了保证索引具有提高效率的作用，估计记录数不大于100的表一律不允许建索引。
2． 只用于distinct或group by子句引用的列，不用建立索引。
3． 对于数据值变化不大的列，比如只有yes和no，那么不要建索引，否则效率降低。
4． 不允许动态创建索引。
5． 当创建复合索引时，复合索引的列不超过7列,复合索引的列的总长度不能超过128字节。
6． 在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列。
7． 复合索引为多列，同复合主键一样将变化显著的列放到复合索引的首位。
8． 在复合索引中，列的排列顺序是非常重要的。例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同。
9． 规范索引的管理，设计表的时候需要将表的主要查询场景整理形成相应文档，然后根据相应场景设计索引。
10． 某些字段（如dr、ts等）设计时不可见。在这些字段上建索引，需要申报审批。
E 表及外键
1． 表的具体设计规范原则。
以第三范式的标准严格设计，然后再考虑开发与运行的效率进行反范式规范的冗余设计。
2． 不允许动态创建表、约束、外键。
3． 每个表必须有主键(primary key)不推荐复合主键★
4． 一个表的字段在引用另外一个表带编码、名称的字段时，只需引用主键字段。
5． 复合主键必须遵守以下规则：越能成为唯一标识的字段(同值重复少)就越放在前面。
6． 没有必要对表中的每一个引用建立主外键关系。
7． 对于已经存在的表，如果要增加字段，建议一般使用可空字段，如果是非空字段，请给出default值。★
8． 只对关系非常密切的表建立外键关系，比如主子表，来源去向表。
9． 外键的建立的基本原则是不能跨模块建立外键关系。★

F 函数规范★
1． 不允许使用自定义函数。

G 产品配置数据库脚本规范★
1． 建库脚本文件分类：
删除表类：包括模块所有表的drop语句。
删除历史版本索引类: 包括对所有历史发版版本索引的统一预删除脚本。表结构类：包括模块所有表的建表语句、主键定义以及check约束。还包括数据类型以及规则定义。
外键、视图、函数、索引类（简称外键类）：包括模块所有的外键、视图、索引、函数。包括drop语句。
触发器、存储过程类： 包括模块的触发器以及存储过程。包括drop 语句。
数据类：包括模块节点以及初始化数据；包括删除数据命令。包括delete语句。

2． 安装盘脚本命名规则：
参见《NC产品配置数据规范》。
3． 书写格式：
字母全部用小写（值除外）。注释用破折号“--”
遵循各个数据库SQL执行标准。例如，每条DDL以及DML语句结束符采用如下符号。
Db2
Oracle
Sqlserver
Sybase
；
DDL /
DML ;
Go
go