数据库设计规范.doc

设计规范文档文档ID：数据库设计规范版本号1.0修订历史记录日期版本说明作者目录修订历史记录2目录3数据库设计指南51. 概述51.1 目的51.2 范围52. 设计标准52.1 表52.2 字段设计原则52.3 域72.4 键设计原则82.5 索引设计原则92.5.1 什么是索引92.5.2 使用索引的目的92.5.3 何时使用索引92.5.4 索引的种类92.5.5 管理索引的准则102.5.6 怎样建立最佳索引112.5.7 索引的选择性122.5.8 确定索引的实际碎片142.5.9 重建索引162.6 数据完整性212.6.1 实体完整性212.6.2 域完整性212.6.3 引用完整性212.6.4 用户定义完整性212.7 关系设计原则212.7.1 一对一关系212.7.2 一对多关系222.7.3 多对多关系222.8 创建数据字典（由于目前普遍采用PD，所以考虑删除本节）223. 命名标准223.1 表223.2 视图233.3 域（Domains）233.4 字段233.5 索引233.6 存储过程233.7 触发器243.8 主键命名243.9 业务规则243.10 约束24数据库设计指南1. 概述1.1 目的本文档用于指导系统设计员进行数据库设计，规范数据库设计文档，提出工件的评审标准。1.2 范围适用于技术中心所有项目/产品的数据库设计。2. 设计标准2.1 表为在性能、扩展性和数据完整性方面达到了最好平衡，表的设计应该满足第三范式（3NF），即： 表内的每一个值都只能被表达一次。 表内的每一行都应该被唯一的标识（有唯一键）。 表内不应该存储传递依赖于主键的非键信息。实际使用中考虑到编码效率、执行效率等问题，往往需要对表的设计进行反规范化，通常是对表进行合并，以下是几种常见的合并情况：1. 基础代码表的合并。当基础代码表的数据行数不多时，可考虑把多个表进行合并，使用标记项区分数据，并对标记项进行聚集索引，在物理上把数据放在一起，可以提高执行效率，例如：职称、文化程度、职务等就可以合并在一起。2. 一对一表的合并。增加数据冗余，可以提高执行效率。数据库查询的瓶颈是多表联合查询，如果数目增大，运行性能就显著下降。把所以列放在一个表，只要有适当的字段索引，其速度就很快。当然表格的严谨性则下降。3. 普遍和个别（类似于面向对象中的父类和子类）的合并，如采购合同、销售合同可以合并在一起放到基本的合同表中，这样可以大大提高数据处理性能。不把数据表的非标准化当作当然的设计理念。而具体的操作不过是一种派生。所以如果表出了问题重新产生非标准化的表是完全可能的。2.2 字段设计原则字段应该保持全局一致性，即无论一个字段在哪个表出现，其名称、类型等都应该保持不变，如定单表Order中有一个OrderID，那么定单细目表OrderItem中在引用时应该还命名为OrderID并保持类型不变字段的数据类型应使用标准（抽象）数据类型来定义，以使其含义不受目标数据库数据类型差异的影响，便于沟通理解，尽量使用域（Domains）来定义字段的类型，以便于保持一致性和提高可维护性。在选择数据类型时应尽量使用标准数据类型，而不使用个别数据库系统提供的个性化类型，以保持数据库定义具有良好的可移植性。考虑到目前物理数据模型的设计都采用PD，为了保持数据类型命名的独立性，在PD环境下定义了一个抽象的目标数据库AbstractDB，并定义了标准（抽象）的数据类型，物理数据模型应在AbstractDB下进行设计，然后再生成实际的目标数据库（Sql server、Oracle等）的创建脚本。标准数据类型定义表2-1：表2-1 标准数据类型定义表类型说明使用建议Char(n)固定长度的非 Unicode 字符数据，在MS Sql Server中，最大长度为 8,000 个字符。当字段长度差异不是很明显时使用，如邮政编码、姓名等varchar(n)类似Char(n)，但长度可变当字段长度较大（50），表的记录数很多，且长短差异较大时，由于可以显著降低磁盘空间占用，所以推荐使用，如备注，多数情况少于200字节，个别情况下可能会达到1000字节。int1616位带符号整数从 -215 (-32,768) 到 215 - 1 (32,767) 的整数数据。int3232位带符号整数从 -231 (-2,147,483,648) 到 231 - 1 (2,147,483,647) 的整型数据（所有数字）。某些情况可作为主键Decimal十进制数,默认长度最大位数38位，有效值从 -1038 +1 到 1038 - 1。精度可调，当对准确度要求较高时使用，如金额、单价等财务数据。decimal(n)总长度为n的十进制数decimal(p,s)总长度为p，小数位数为s的十进制数Single单精度浮点数，长度4字节有效数字7位，从 -3.40E + 38 到 3.40E + 38 之间的浮点数字。存储大小为 4 字节。在 SQL Server 中，其他见doubleDouble双精度浮点数，长度8字节15位有效数字，从 - 1.79E + 308 到 1.79E + 308 之间的浮点数字。当对准确度要求不是很高时可以使用，如工程计算、图形计算等Datetime日期时间型，长度8字节从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。Timestamp时间戳，长度为8字节的二进制数数据库范围的唯一数字，一个表只能有一个 timestamp 列，每次更新行时自动对该类型字段进行更新，所以在多用户环境下该类型的字段可以用于检查更新冲突。Boolean布尔型，不同数据库存储策略不同，在MS Sql Server中会将每8个布尔型字段保存到一个字节中，每个字段占1个Bit。存储真/假，是/否等逻辑值，不能对该类型的字段使用索引long binary长二进制字符串，可变长度二进制数据，介于 0 与 231-1 (2,147,483,647) 字节之间。可保存图象、视频、音频、OLE对象等大的二进制数据long text长文本字符串，可变长度非 Unicode 数据的最大长度为 231-1 (2,147,483,647)个字节。可保存大的纯文本数据。表2-2 数据类型对照表标准类型MS Sql 7/ 2000Oracle 8IBM DB 2 7.0Access 2000Char(n)Char(8000)Char(2000)Char(255)Text(255)varchar(n)Varchar(8000)Varchar(4000)Varchar(4000)longtextint16SmallintSmallintsmallintSmallintint32IntIntegerintegerIntegerDecimaldecimal numericNumberDecimalnumericdecimal(n)Decimal numericNumber(m)Decimal(n)Numericdecimal(p,s)decimal numericNumber(m,n)Decimal(p,s)NumericSingleRealFloatRealSingleDoubleFloatFloatDouble precisionDoubleDatetimeDatetimeDateDateDatetimeTimestampTimestampTimestampTimestampBooleanBitSmallintSmallintYesnobinary(n)binary(8000)binaryvarbinary(n)varbinary(8000)Row(2000)binarylong binaryImageLongLong rowLONG VARGRAPHICLONGBINARY GENERALlong textTextLong rowLONG VARCHARlongtext2.3 域域可以帮助识别模型中信息的类型，它定义了列的一组有效值，使用域来定义列可以使得不同表中列的数据特征更容易标准化及便于修改。域分为标准域和方案域两种，标准域由域名前加“_”构成，各方案项目通用，方案域由各方案项目根据实际情况自行定义。标准域的定义见下表：（可扩展）名称编码说明类型长度精度addressAddress联系地址varchar(100)100eMailEMail电子邮件varchar(30)30Note200note200备注varchar(200)200PathNamepathName路径名varchar(250)250Percentpercent百分比decimal(10,2)102moneyMoney金额decimal(18,2)182pricePrice单价decimal(12,2)122SexSex性别char(1)1telephoneTelephone电话号码char(16)16unitOfMeasureunitOfMeasure计量单位char(16)16wwwwww网址varchar(30)302.4 键设计原则键（主键）标注字段的唯一并保证表之间（外键）的完整性，以下方法有助于进行键的设计：1. 避免使用复合键作为主键；2. 为关联字段创建外键；3. 所有的键都必须唯一；4. 一个外键总是关联唯一的键字段；5. 避免选择用户可编辑的字段作为键，因为一旦输入错误，唯一能做的就是删除整个记录然后从头开始；6. 使用流水号表。主键定义方案：（供讨论）在综合考虑性能、并发性和分布性等多种因素后，确定公司数据库中的主键统一使用CHAR(16)型的单一字段保存的10进制字符串，其内容分为两部分，前6个字节为分段号，后10个字节为段内编号，分段号全局唯一，段内编号本地唯一，宽度不足时前面用0补齐（如：0000010123456789）。主键生成算法 1：根据公司现行状况Int 类型，采用序列,长度为11位；Varchar类型，采用java生成，长度为32位；主键生成算法 2：目的：返回指定表的最大可用主键。输入：表名输出：新的主键strMaxkey步骤：1、 取表的最大主键strMaxKey（可使用序列或流水号表）。2、 若strMaxKey非空值或空字符串则继续，否则转93、 取strMaxKey的前6位送到strSectID4、 取strMaxKey的后10位送到strOffset5、 将strOffset由字符型变为长整型并加1送到lngOffset6、 将lngOffset由长整型转换为宽度为10字节的字符串，前面用0补齐。7、 将strSectID和strOffset进行字符串连接操作后送到strMaxKey中8、 转119、 取分段号strSectid，strSectid由组织统一分配，组织内的每个数据中心唯一。10、 将strSectID和“0000000001”进行字符串连接操作后送到strMaxKey中11、 算法结束 2.5 索引设计原则2.5.1 什么是索引索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶子节点不同B*数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。2.5.2 使用索引的目的加快查询速度减少I/O操作消除磁盘排序2.5.3 何时使用索引查询返回的记录数排序表40%非排序表 10order by count(*) desc/（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。set linesize 120col INDEX format a30col TABLESPACE format a20selectowner OWNER,segment_name INDEX,tablespace_name TABLESPACE,bytes BYTES/COUNT,sum(bytes) TOTAL BYTES,round(sum(bytes)/(1024*1024),0) TOTAL M,count(bytes) TOTAL COUNTfrom dba_extentswhere segment_type=INDEX and segment_name in (索引名1,索引名2,.)group by owner,segment_name,segment_type,tablespace_name,bytesorder by owner,segment_name/ （3）确定索引表空间还有足够的剩余空间。确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。select round(bytes/(1024*1024),2) free(M)from sm$ts_freewhere tablespace_name=表空间名/ （4）重建索引。重建索引时要注意以下几点：a.如果不指定tablespace名，索引将建在用户的默认表空间。b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。alter index 索引名rebuildtablespace 索引表空间名storage(initial 初始值 next 扩展值)nologging/ （5）检查索引。对重建好的索引进行检查。select *from dba_extentswhere segment_name=索引名/ （6）根据索引进行查询，检查索引是否有效使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。select *from dba_ind_columnswhere index_name like 表名%/ 然后，根据相应的索引项进行查询。select *from 表名%where ./ （6）找出有碎片的表空间，并收集其碎片。重建索引后，原有的索引被删除，这样会造成表空间的碎片。select alter tablespace |tablespace_name| coalesce;from dba_free_space_coalescedwhere percent_blocks_coalesced!=100/ 整理表空间的碎片。alter tablespace 表空间名 coalesce/ 要删除索引的原因。不再需要的索引；。索引没有针对其相关的表所发布的查询提供所期望的性能改善；。应用没有用该索引来查询数据；。该索引无效，必须在重建之前删除该索引；。该索引已经变的太碎了，必须在重建之前删除该索引；。语句：drop index idx_name;drop index idx_name drop partition partition_name;建立索引的代价基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/O上；插入、更新、删除数据产生大量db file sequential read锁等待；2.6 数据完整性数据完整性有四种类型：实体完整性、域完整性、引用完整性和用户定义完整性等几种。大型数据库一般都提供丰富的约束机制，以保证数据的完整性，通常采用的有键、规则、约束和触发器等几种。2.6.1 实体完整性实体完整性将行定义为特定表的唯一实体。实体完整性强制表的标识符列或主键的完整性（通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性）。2.6.2 域完整性域完整性是指给定列的输入有效性。强制域有效性的方法有：限制类型（通过数据类型）、格式（通过 CHECK 约束和规则）或可能值的范围（通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则）。2.6.3 引用完整性在输入或删除记录时，引用完整性保持表之间已定义的关系。引用完整性基于外键与主键之间或外键与唯一键之间的关系（通过 FOREIGN KEY 和 CHECK 约束）。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。如果定义了强制引用完整性时，则禁止用户进行下列操作： 当主表中没有关联的记录时，将记录添加到相关表中。 更改主表中的值并导致相关表中的记录孤立。 从主表中删除记录，但仍存在与该记录匹配的相关记录。 2.6.4 用户定义完整性用户定义完整性使您得以定义不属于其它任何完整性分类的特定业务规则。所有的完整性类型都支持用户定义完整性（包括CREATE TABLE 中的所有列级和表级约束、存储过程和触发器）。2.7 关系设计原则在关系数据库中，关系能防止冗余的数据。关系是通过匹配键列中的数据而工作的，而键列通常是两个表中具有相同名称的列。在大多数情况下，关系将一个表中为每个行提供唯一标识符的主键与另一个表中外键内的项相匹配。表与表之间存在三种类型的关系，即一对一关系、一对多关系和多对多关系。 2.7.1 一对一关系在一对一关系中，表 A 中的一行最多只能与表 B 中的一行相匹配，反之亦然。如果两个相关列都是主键或具有唯一约束，则创建的是一对一关系。存在一对一关系时通常会将列保存在同一个表中，但出于以下目的，可以将列保存在一个以上的表中：分割一个含有许多列的表。出于安全考虑而隔离表的某一部分。存储可以很容易删除的临时数据，只需删除表即可删除这些数据。存储只应用于主表子集的信息。 2.7.2 一对多关系一对多关系是最常见的关系类型，俗称主从表。在这种关系类型中，表 A 中的行可以在表 B 中有许多匹配行，但是表 B 中的行只能在表 A 中有一个匹配行。2.7.3 多对多关系在多对多关系中，表 A 中的一行可与表 B 中的多行相匹配，反之亦然。通过定义称为连接表的第三方表创建这样的关系，该连接表的主键包括表 A 和表 B 中的外键。如果两个实体之间存在一对多关系，而且有可能会转化为多对多关系，则最好一开始就设置成多对多关系。这可以避免日后的很多麻烦。3. 命名标准数据库对象的命名应遵循统一的标准，以便于管理和系统之间的集成。3.1 表表名前加上版本英文简称作为前缀，如E代表九城B2B英文版前台数据库表，C代表九城B2B中文版前台数据库表；EB代表九城B2B英文版后台数据库表，CB代表九城B2B中文版后台数据库表。如果表名是一个和两个单词，名字就取全名；如果表的名字由3个单词组成，就各取单词缩写。对关联表来说，表名加上RELATE_作为中缀，后面加代表此关联的实意单词。对工作用表(查询、报表)来说，表名加上TMP_、QRY_ 或者REP_ 等中缀加以区别。对全局用表，表名前缀使用GBL_。示例：数据表 E_ACCO