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 2000Win 2003Sun Solaris 2.6 or lateIBM AIX 4.2 and laterSCO UnixWare 7.0LinuxHP Unix 11.00 or later操作系统SQL SERVER 7.0SQL SERVER 2000（sp3）SQL SERVER 2005Win 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系统管理smUAPpub财务会计平台dap基础数据bd外部交换平台xx管理会计平台dmp报表&BPM报表iufoBI平台biBPMbpm财务总账管理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其他cdmpm2 所有表、视图、触发器、索引、函数、约束、主外键必须指定名称，规则如下：对于各种数据库对象的命名规则，目前的NC系统中存在两种风格，采用拼音和采用英文的，目前这两种规范都可以，但是注释一定要清楚.数据库对象规则最大长度例子表系统规则名_名称18sm_firm字段名18acc_code, acc_name视图v_系统规则名_名称（*）18ac_accsub触发器t_表名_名称（*）18t_firm函数f_名称18f_fun()Checkck_表名_字段名（*）18ck_gl_accsub_code主键pk_表名18pk_gl_accsub外键fk_表名_字段名（*）-此处超过18位处理18fk_gl_vouch_accsub_code索引i_表名_字段名（*）18i_gl_accsub_code表空间ts_名称18ts_sys （*）注：由于数据库对象之间的命名规则与最大长度限制可能造成命名存在冲突。如存在，可以通过缩减表名或字段字符来实现。 3 所有的外键、约束、索引、函数、触发器、存储过程名不允许重复。4 业务中主子表的命名规则：系统规则名_名称或主表：系统规则名_名称_h5 子表：系统规则名_英文名称_b。业务中主子子表的命名规则：主表：系统规则名_英文名称_h子表：系统规则名_英文名称_b子子表：系统规则名_英文名称_bs五、数据类型1 可用数据类型类型范围SqlserverOracleDb2字符型长度固定(注意：长度小于255)CharCharChar可变字符型长度不固定(最大4000)VarcharVarchar2VarcharOracle只能到4000，以此为准16位整型-32,76832,767SmallintSmallintSmallint32位整型-2,147,483,648 2,147,483,647IntIntinteger精确数值型pppppppppppppp.ssDecimal(p,s)Number（p,s）Decimal(p,s)近似数值型-1.79E+308 1.79E+308FloatFloatFloat日期型yyyy-mm-dd。Char(10)Char (10)Char(10)日期时间型yyyy-mm-dd hh_mm_ssChar(19)Char (19)Char(19)时间戳System date and time dateDatetimedate布尔型Y or NChar(1)Char (1)Char(1)ID/PK Char(20)Char (20)Char (20)大文本textClobclob二进制imageBlobblob2 业务数据类型规范这个规范需要落实到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： 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语句结束符采用如下符号。Db2OracleSqlserverSybase；DDL / DML ;Gogo