数据库编码规范

数据库编码规范 V1.2 2004 年 11 月 12 日 类别 文件名： 数据库编码规范 版本号： V1.2 版本日期： 2004-11-12 作者： Nick 日期： 2004-11-12 目 录 1 目的. 2 2 范围. 3 3 术语. 3 4 设计概要. 3 4.1 设计环境. 3 4.2 设计使用工具. 4 4.3 设计原则. 4 4.4 设计的更新. 4 5 命名总体原则. 4 6 命名规范（逻辑对象）. 5 6.1 数据库结构命名. 5 6.2 数据库对象命名. 6 7 脚本注释. 8 7.1 存储过程或触发器. 8 7.2 自定义函数. 9 8 数据库操作原则. 10 8.1 建立、删除、修改库表操作. 10 8.2 添加、删除、修改表数据. 10 9 常用字段命名. 10 9.1 系统信息常用字段. 10 9.2 客户个人信息常用字段. 10 9.3 铺助信息常用字段. 11 9.4 业务信息常用字段. 11 10 设计实例（SQL SERVER 2000）. 12 10.1 表、主键、外键、索引、规则、约束. 12 附件1：数据库数据字典. 14 附件2：数据库类型简写. 15 1 目的 为了统一公司软件开发的设计过程中关于数据库设计时的命名规范和具体工作时的编程规范，便于交流和维护，特制定此规范。 2 范围 本规范适用于开发组全体人员，作用于软件项目开发的数据库设计、维护阶段。 3 术语 Ø 数据库对象：在数据库软件开发中，数据库服务器端涉及的对象包括物理结构和逻辑结构的对象。 Ø 物理结构对象：是指设备管理元素，包括数据文件和事务日志文件的名称、大小、目录规划、所在的服务器计算极名称、镜像等，应该有具体的配置规划。一般对数据库服务器物理设备的管理规程，在整个项目/产品的概要设计阶段予以规划。 Ø 逻辑结构对象：是指数据库对象的管理元素，包括数据库名称、表空间、表、字段/域、视图、索引、触发器、存储过程、函数、数据类型、数据库安全性相关的设计、数据库配置有关的设计以及数据库中其他特性处理相关的设计等。 4 设计概要 4.1 设计环境 a) ORACLE 9I 数据库 ORACLE 9i 操作系统 SUSE LINUX 7.1以上版本，显示图形操作界面; RedHat 9 以上版本 CPU P III 1.7G 以上 内存 512M以上 硬盘空间 5GB以上的剩余空间，如要建立较多的、大型的数据库需要更多的硬盘空间 b) MS SQL SERVER 2000 数据库 SQL SERVER 2000 企业版 打sp3以上补丁和安全补丁 操作系统 WINDOWS 2000 SERVER 或 WINDOWS 2000 Advanced Server 打sp4以上补丁 CPU P III 1G以上 内存 256M以上 硬盘空间 1G以上剩余空间如要建立较多的、大型的数据库需要更多的硬盘空间 4.2 设计使用工具 a) 使用PowerDesigner 做为数据库的设计工具，要求为主要字段做详尽说明。 b) 通过PowerDesigner 定制word格式报表，并导出word文档，作为数据字典保存，格式可参看附件1。（PowerDesigner v10 才具有定制导出word格式报表的功能） c) 编写数据库建数据库、建数据库对象、初始化数据脚本文件 4.3 设计原则 a) 采用多数据文件 b) 禁止使用过大的数据文件，unix系统不大于2GB,window系统不超过500MB c) oracle数据库中必须将索引建立在索引表空间里。 d) 基本信息表在建立时就分配足够的存储空间，禁止其自动扩展功能 e) 大文本字列、blob列要独立出一张表，此表只有id和blob(或大文本)列 4.4 设计的更新 a) 在设计阶段，由数据库管理员或指定的项目组其一成员进行维护。 b) 运行阶段，由数据库管理员进行维护。 c) 如对表结构进行修改，应先在PowerDesigner中进行修改，再重新导出WORD文档，最后在数据库中进行修改。如果修改的是数据库字典表，必须由数据库管理员进行。 d) 使用PowerDesigner生成SQL代码，禁止由PowerDesigner直接连数据库进行数据库操作 e) 修改数据库要通过SQL，禁止其它方式对数据进行修改 f) 修改数据库的SQL要添加说明后保存备查 5 命名总体原则 Ø 设定的前缀一律用小写字母 Ø 标识名称命名全部小写 Ø 整个命名的全长不得超过30个字母 Ø 全部使用字母和下划线‘_’，不能使用中文和其他字符，有特别情况允许使用末尾数字编号。例如：t_Finace1, t_Finace2... Ø 命名名称来自于业务，全部采用英文单词 Ø 英文单词过长可以采用通用的缩写，尽量表达出业务的含义 Ø 如需要两个以上的英文单词做标识名称，单词之间要用下划线‘_’连接 Ø 名称全是由名词组成的，名词由大范围到小范围排序取名 Ø 完成某功能的名称，如函数和过程，以动宾形式取名 6 命名规范（逻辑对象） 6.1 数据库结构命名 a) 数据库命名 数据库的命名要求使用与数据库意义相关联的英文字母，并且以“db_”打头， 即db_ <业务系统名称> 。 例如：china care 数据库的命名为 db_ccnet； 客户资料数据库的命名为db_Customer_Info。 b) 数据库日志设计命名 数据库日志的的命名以 <数据库名> _ <日志名> .log格式命名。其中， <日志名> 为有意义的数据库日志名称。 例如：db_ccnet_logredo.log c) 数据库配置设计命名 数据库配置设计方案是以文件形式保存的，其内容是关于特定数据库的配置项目的具体值。 数据库配置文件的命名以: <数据库类型简写〉_> <应用系统标志> _cfg.ini格式命名。 其中，数据库类型简写见附件2《数据库类型简写》，cfq表示该文件是数据库配置文件。 例如：ORA_ ccnet_cfg.ini d) 数据库复制与存储设计命名 数据库复制与存储设计方案是以文件形式保存的，其内容是关于特定数据库之间的复制策略的具体细节。 数据库复制与存储设计文件的命名以： <数据库类型简写> _ <应用系统标志> _rep.txt格式命名。 其中，数据库类型简写见附件2《数据库类型简写》，rep表示该文件是数据库复制与存储文件。 e) 数据库连接设计命名 数据库连接设计方案是以文件形式保存的，其内容是关于特定分布式数据库之间的连接设计的具体细节。 数据库连接设计文件的命名以： <数据库类型简写> _ <应用系统标志> _dbl. sql格式命名。其中，数据库类型简写见附件2《数据库类型简写》，dbl表示该文件是数据库连接设计文件。 f) 表空间、数据文件命名(主要针对Oracle) 索引表空间： 表空间命名格式：ts <系统标识> _i 数据库文件命名格式：ts <系统标识> _i[n].dbf 临时表空间： 表空间命名格式：ts <系统标识> _t 数据库文件命名格式：ts <系统标识> _t[n].dbf 回滚表空间： 表空间命名格式：ts <系统标识> _r 数据库文件命名格式：ts <系统标识> _r[n].dbf 数据表空间： 表空间命名格式：ts <系统标识> _d 数据库文件命名格式：ts <系统标识> _d[n].dbf 注：表空间名不超过8位，n 可取00-99或0-9，根据系统数据量确定。 6.2 数据库对象命名 a) 表 表的命名必须以“t_”（Table缩写）开头，格式为：t_[系统标识]_ <数据表类型标识> _ <表标识> 。 其中，[ ]表示可选项，依据实际情况而增加； <表标识> 要求与表意义相关联的英文字母，例如：t_Customers。 数据表大致分为：业务数据表、基本编码表、辅助编码表、系统信息表、累计数据表、结算数据表、决策数据表； 基本编码表用base标志 累计数据表用count标志 系统信息表用info标志 ... 例如：t_trade_base_trade_code,t_trade_info_help ... b) 字段/域 根据业务要求进行命名，不需设定固定的前缀。 c) 索引 针对数据库表中一个或多个字段建立的索引的命名格式应以“idx_”开头，索引列名间用_隔开，即为idx_ColumnName1_ColumnName2_… 其中，ColumnName1是数据库表中（第一个）索引字段的名称或名称简写；ColumnName2是数据库表中（第二个）索引字段的名称或名称简写；索引名的总长必需符合数据库的规定。 例：idx_cert_number (表示在字段cert_number上创建索引) d) 视图 视图的命名必须以“v_”(View 缩写)开头，格式为： v_ <视图类型> _[系统标识] _ <视图标识> 。 其中，视图类型参见“表的分类说明”；[系统标识_]是可选项，依据情况而增加； <视图标识> 应与视图意义相关联的英文字母。 例：v_user_detail_info e) 存储过程 存储过程的命名必须符合sp_[系统标识]_ <存储过程标识> 格式。 其中，sp表示是存储过程；[系统标识]为可选项，依据情况而增加； <存储过程标识> 是与存储过程意义相关联的英文字母，例如：USP_Query_Write_to_Disk。 例：sp_check_user_auth（以动宾方式取名） f) 触发器 触发器的的命名必须符合tr_ <表名> _ 格式。 其中,tr表示是触发器； 是与触发器意义相关联的英文字母。 例：tr_user_info_iu （表示对user_info表进行插入、更新的触发器） g) 函数 函数的命名必须符合fn_[系统标识]_ <函数标识> 格式。 其中，fn表示是函数，[系统标识]为可选项，依情况而定； <函数标识> 是与函数意义相关联的英文字母。 例：fn_create_id（以动宾方式取名） h) 自定义数据类型 自定义数据类型的命名格式为：ud_ <自定义数据类型标识> _ <数据类型> i) Default（缺省） Default（缺省）的命名格式一般为：df_ ；对于非绑定的默认可取系统默认的名字，请参见第8.1点实例绑定具体字段。 例：df_begin_date 缺省开始日期 ‘20030101’ if exists (select * from sysobjects where type = 'D' and name = 'df_begin_date') drop default dbo.df_begin_date go create default df_begin_date as '20030101' go j) Check、Constraint（约束） 约束的命名格式一般为：ck_ <表名> _ ；一些约束可直接放在生成表的语句中。 例：ck_flag 参见第8.1点实例，约束字段flag只能取字符‘0’到‘9’： constraint ck_flag CHECK (flag between '0’ and '9’ ) k) Rule（规则） 规则的命名格式一般为：rl_ ；对于非绑定规则(约束) 可取系统默认的名字，参见第10点设计实例绑定具体字段。 例：rl_not_zero （定义一个不等于0的规则） if exists (select * from sysobjects where type = 'R' and name = 'rl_not_zero') drop rule dbo.rl_not_zero go create rule rl_not_zero as @i <> 0 go l) 主键 主键的命名格式为pk_ <表名> _ <主键标识> 。 例：pk_user_info_userid （表user_info以字段userid创建主键） m) 外键 外键的命名格式为fk_ <表名> _ <主表名> _ <外键标识> 。 例：fk_user_info_department_deptid （在表user_info的字段department_id上创建外键，参照主表department） n) 同义词（ORACLE） 同义词的命名格式为：sy_ <同义词标识> 例：sy_user_info （为所有权属于ben的表user_info的公共同义词） 7 脚本注释 7.1 存储过程或触发器 a) 每一个存储过程或触发器都要在最前面写注释，注释如下 /* writer： create date： ver： Depiction： remark： */ 另外，过程中声明的重要变量要有注释，例如： @iActionFlag int = 0 /* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */ b) 如果只对存储过程或触发器进行部分修改时须添加以下注释： /*rewriter： add（rewriter）： date： <格式：yyyy-mm-dd> start1： 修改描述： */ /* 原代码内容*/（修改时） /*rewriter： date： <格式：yyyy-mm-dd> end1： */ /*rewriter： add（rewriter）： date： <格式：yyyy-mm-dd> start2： */ 新代码内容 /*rewriter： date： <格式：yyyy-mm-dd> end2： */ c) 如果对存储过程或触发器有较大的修改，可增加修改内容的注释。 /*Log Id： rewriter： rewrite date： <格式：yyyy-mm-dd> Depiction： */ 7.2 自定义函数 a) 每一个自定义函数都要在其前面写注释，注释如下 /* function name:xxxx Depiction： <对此函数的描述> param（a,b） a 功能或描述.... b 功能或描述 output: x x=0表示..... x=1 表示...... writer： create date： <创建日期，格式：yyyy-mm-dd> ver： remark： * / 另外，函数中声明的重要变量要有注释，例如： @iActionFlag int = 0 /* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */ b) 如果只对函数进行部分修改时须添加以下注释： /*rewriter： add（rewriter）： date： <格式：yyyy-mm-dd> start1： 修改描述： */ /* 原代码内容*/（修改时） /*rewriter： date： <格式：yyyy-mm-dd> end1：*/ /*rewriter： add（rewriter）： date： <格式：yyyy-mm-dd> start2： */ 新代码内容 /*rewriter： date： <格式：yyyy-mm-dd> end2： */ c) 如果对函数有较大的修改，可增加修改内容的注释。 /*Log id： rewriter： rewrite date： <修改日期，格式：yyyy-mm-dd> Depiction： */ 8 数据库操作原则 8.1 建立、删除、修改库表操作 Ø 在开发环境中，对于自己的库表可任意进行修改、删除操作；但需保留相应的建表语句和说明，与建表人建表时间。 8.2 添加、删除、修改表数据 Ø 在开发环境中，开发人员所开发模块独自使用的库表，可自由操作表中数据； Ø 对其他模块关联的库表，应取得其他模块的开发人员同意后再执行操作； Ø 系统的信息表、字典表的修改应向数据库的管理员提出操作需求，由数据库的管理员执行操作。 9 常用字段命名 9.1 系统信息常用字段 字段名称 字段代码 ORACLE SQLSERVER 值 说明 用户标识 user_id char(8) char(8) 用于登录应用系统的标识, 填写数字。一般做主键,要填满长度。 用户名称 user_name varchar2(20) varchar(20) 用户标识对应的名称 组标识 grp_id char(4) char(4) 用户所属的组标识，填写数字。一般做主键，要填满长度。 组名称 grp_name varchar2(50) varchar (50) 组标识对应名称 组权限标识 grp_auth_id char(2) char(2) 由低到高，从00到99。要填满长度 组权限名称 grp_auth_name varchar2(50) varchar (50) 组权限标识对应名称 密码 password varchar2(30) varchar(30) 9.2 客户个人信息常用字段 字段名称 字段代码 ORACLE SQLSERVER 值 说明 客户标识 customer_id char(?) char(?) 业务系统使用的客户标识,一般做主键，不用可变长类型。要填满长度。 客户姓名 customer_name varchar2(50) varchar(50) 部门标识 dept_id char(?) char(?) 一般做主键，不用可变长类型。要填满长度。 部门名称 dept_name varchar2(50) varchar(50) 性别 sex char(1) char(1) F/M 以下没有标识的，直接在字段中填写具体值 婚姻状况 marriagestatus varchar2(50) varchar(50) 出生日期 birthday date smalldatetime 身高 hight number(5,2) dec(5,2) 单位为厘米 体重 weight number(5,2) dec(5,2) 单位为公斤 民族 nationality varchar2(20) varchar(20) 联系电话 phone varchar2(50) varchar(50) 家庭电话 home_phone varchar2(50) varchar(50) 办公电话1 office_phone1 varchar2(50) varchar(50) 办公电话2 office_phone2 varchar2(50) varchar(50) 区号 zone varchar2(20) varchar(20) 传真号码 office_fax varchar2(50) varchar(50) 移动电话 mobil_phone varchar2(50) varchar(50) 电子信箱 email varchar2(50) varchar(50) 邮政编码 post_code varchar2(20) varchar(20) 国籍 country varchar2(50) varchar(50) 居住省 province varchar2(20) varchar(20) 居住市 city varchar2(20) varchar(20) 家庭住址 home_addr varchar2(80) varchar(80) 办公地点 office_addr varchar2(80) varchar(80) 证件号码 cert_number varchar2(50) varchar(50) 证件名称 cert_name varchar2(20) varchar(20) 职业标识 occupation_id char(4) char(4) 要填满长度 职业名称 occupation varchar2(50) varchar(50) 教育程度 education varchar2(20) varchar(20) 9.3 铺助信息常用字段 字段名称 字段代码 ORACLE SQLSERVER 值 说明 记录状态标识 flag char(1) char(1) A/D… A:有效；D：删除；S：暂停… 记录序号 id number int 9.4 业务信息常用字段 字段名称 字段代码 ORACLE SQLSERVER 值 说明 业务发生日期 oper_date date smalldate 开始日期 begin_date date smalldate 结束日期 end_date date smalldate 操作日期 curr_date date smalldate 操作员编号 user_id char(4) char(4) 即为系统信息中的用户标识 发生额 number(7,2) dec(7,2) 余额 balance number(10,2) dec(10,2) 描述 description varchar2(50) varchar(50) 10 设计实例（SQL SERVER 2000） 10.1 表、主键、外键、索引、规则、约束 名称：t_base_user 中文名称：用户基本信息表 简单说明：存放用户的基本信息 名称 代码 数据类型 键、规则、约束 注释 用户ID user_id char(8) PK 用户唯一标识 用户姓名 user_name varchar(20) 用户真实姓名 身高 hight decimal（7，2） rl_not_zero 为举例子而设定此三个字段，原表没有。 体重 weight decimal（7，2） 开始日期 begin_date smalldatetime df_begin_date 部门id department_id char(4) FK 用户所属的部门id 参照主表：department 用户职务1id headship1_id char(4) FK 用户的职务id 参照主表： 用户职务2id headship2_id char(4) FK 用户的职务id 参照主表： 用户职称id title_id char(4) FK 用户的职称id 参照主表： 电话号码 phone varchar(50) 用户的电话号码 email varchar(50) 银行账号 account_no varchar(50) 用户的工资银行账号 身份ID cert_number varchar(30) UNI INDEX 用户的身份ID 停用 flag char(1) NOT NULL 识别用户是否被注销 /*==============================================================*/ /* Table: t_base_user */ /*==============================================================*/ create table t_base_user ( user_id char(8) not null, user_name varchar(20) null, hight decimal(7,2) null, weight decimal(7,2) null, begin_date smalldatetime null, department_id char(4) null, headship1_id char(4) null, headship2_id char(4) null, title_id char(4) null, phone varchar(50) null, email varchar(50) null, account_no varchar(50) null, cert_number varchar(30) null, flag char(1) null, constraint ck_flag CHECK (flag between '0' and '9' ), constraint PK_USER_BASE_USERID primary key (user_id) ) go /*==============================================================*/ /* Index: idx_cert_number */ /*==============================================================*/ create unique index idx_cert_number on user_base (cert_number) go /*==============================================================*/ /* foreign Key: fk_user_base_department_depid */ /*==============================================================*/ alter table user_base add constraint FK_USER_BASE_DEPARTMENT_DEPID foreign key (department_id) references department (department_id) go … （注：以上语句假设主表名为DEPAERMENT。依照此语句创建其他外键） /*==============================================================*/ /* 绑定规则rl_not_zero 到表 user_vase中的列hight */ /*==============================================================*/ exec sp_bindrule 'dbo.rl_not_zero', 'user_base.hight' /*==============================================================*/ /* 绑定缺省 df_begin_date 到表user_base的列begin_date */ /*==============================================================*/ exec sp_bindefault 'dbo.df_begin_date', 'user_base.begin_date' 附件1：数据库数据字典 名称：t_base_user 中文名称：用户基本信息表 简单说明：存放用户的基本信息 名称 代码 数据类型 外来键 （外键） 强制 （约束） 主要的（主键） 注释 用户ID user_id Char(8) TRUE TRUE 用户唯一标识 用户姓名 user_name Varchar(20) 用户真实姓名 部门id department_id Char(4) TRUE 用户所属的部门id 参照主表：department 用户职务1id headship1_id Char(4) TRUE 用户的职务id 参照主表： 用户职务2id headship2_id Char(4) TRUE 用户的职务id 参照主表： 用户职称id title_id Char(4) TRUE 用户的职称id 参照主表： 电话号码 Phone Varchar(50) 用户的电话号码 Email Varchar(50) 银行账号 account_no Varchar(50) 用户的工资银行账号 身份ID cert_number Varchar(30) 用户的身份ID 停用 Flag Char(1) TRUE 识别用户是否被注销 主键：PK_BASE_USER_USERID 外键：FK_BASE_USER_DEPARTMENT_DEPID，… 附件2：数据库类型简写