数据库设计规范

设计标准文档设计标准文档文档ID：XIU-DB-01数据库设计标准版本号1.0

修订历史记录日期版本说明作者

目录TOC\o"1-3"\h\z修订历史记录 2目录 3数据库设计指南 41.概述 4目的 4范围 42.设计标准 4表 4字段设计原那么 4域 6键设计原那么 7索引设计原那么 7什么是索引 7使用索引的目的 8何时使用索引 8索引的种类 8数据完整性 8实体完整性 8域完整性 8引用完整性 8用户定义完整性 9关系设计原那么 92一对一关系 9一对多关系 9多对多关系 93.命名标准 10表 10视图 10域〔Domains〕 10字段 10索引 11存储过程 11触发器 11主键命名 11业务规那么 11约束 11其他 11

数据库设计指南概述目的本文档用于指导系统设计员进行数据库设计，标准数据库设计文档，提出工件的评审标准。范围适用于产品与技术中心所有工程/产品的数据库设计。设计标准表为在性能、扩展性和数据完整性方面到达了最好平衡，表的设计应该满足第三范式〔3NF〕，即：表内的每一个值都只能被表达一次。表内的每一行都应该被唯一的标识〔有唯一键〕。表内不应该存储传递依赖于主键的非键信息。实际使用中考虑到编码效率、执行效率等问题，往往需要对表的设计进行反标准化，通常是对表进行合并，以下是几种常见的合并情况：根底代码表的合并。当根底代码表的数据行数不多时，可考虑把多个表进行合并，使用标记项区分数据，并对标记项进行聚集索引，在物理上把数据放在一起，可以提高执行效率，例如：职称、文化程度、职务等就可以合并在一起。一对一表的合并。增加数据冗余，可以提高执行效率。数据库查询的瓶颈是多表联合查询，如果数目增大，运行性能就显著下降。把所以列放在一个表，只要有适当的字段索引，其速度就很快。当然表格的严谨性那么下降。普遍和个别〔类似于面向对象中的父类和子类〕的合并，如采购合同、销售合同可以合并在一起放到根本的合同表中，这样可以大大提高数据处理性能。不把数据表的非标准化当作当然的设计理念。而具体的操作不过是一种派生。所以如果表出了问题重新产生非标准化的表是完全可能的。字段设计原那么字段应该保持全局一致性，即无论一个字段在哪个表出现，其名称、类型等都应该保持不变，如定单表Order中有一个OrderID，那么定单细目表OrderItem中在引用时应该还命名为OrderID并保持类型不变字段的数据类型应使用标准〔抽象〕数据类型来定义，以使其含义不受目标数据库数据类型差异的影响，便于沟通理解，尽量使用域〔Domains〕来定义字段的类型，以便于保持一致性和提高可维护性。在选择数据类型时应尽量使用标准数据类型，而不使用个别数据库系统提供的个性化类型，以保持数据库定义具有良好的可移植性。考虑到目前物理数据模型的设计都采用PD，为了保持数据类型命名的独立性，在PD环境下定义了一个抽象的目标数据库AbstractDB，并定义了标准〔抽象〕的数据类型，物理数据模型应在AbstractDB下进行设计，然后再生成实际的目标数据库〔Sqlserver、Oracle等〕的创立脚本。标准数据类型定义表2-1：表2-1标准数据类型定义表类型说明使用建议Char(n)固定长度的非Unicode字符数据，在MSSqlServer中，最大长度为8,000个字符。当字段长度差异不是很明显时使用，如邮政编码、姓名等varchar(n)类似Char(n)，但长度可变当字段长度较大〔>50〕，表的记录数很多，且长短差异较大时，由于可以显著降低磁盘空间占用，所以推荐使用，如备注，多数情况少于200字节，个别情况下可能会到达1000字节。int1616位带符号整数从-2^15(-32,768)到2^15-1(32,767)的整数数据。int3232位带符号整数从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据〔所有数字〕。某些情况可作为主键Decimal十进制数,默认长度最大位数38位，有效值从-10^38+1到10^38-1。精度可调，当对准确度要求较高时使用，如金额、单价等财务数据。decimal(n)总长度为n的十进制数decimal(p,s)总长度为p，小数位数为s的十进制数Single单精度浮点数，长度4字节有效数字7位，从-3.40E+38到3.40E+38之间的浮点数字。存储大小为4字节。在SQLServer中，其他见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布尔型，不同数据库存储策略不同，在MSSqlServer中会将每8个布尔型字段保存到一个字节中，每个字段占1个Bit。存储真/假，是/否等逻辑值，不能对该类型的字段使用索引longbinary长二进制字符串，可变长度二进制数据，介于0与231-1(2,147,483,647)字节之间。可保存图象、视频、音频、OLE对象等大的二进制数据longtext长文本字符串，可变长度非Unicode数据的最大长度为231-1(2,147,483,647)个字节。可保存大的纯文本数据。表2-2数据类型对照表标准类型MSSql7/2000Oracle8Access2000Char(n)Char(8000)Char(2000)Char(255)Text(255)varchar(n)Varchar(8000)Varchar(4000)Varchar(4000)longtextint16SmallintSmallintsmallintSmallintint32IntIntegerintegerIntegerDecimaldecimalnumericNumberDecimalnumericdecimal(n)DecimalnumericNumber(m)Decimal(n)Numericdecimal(p,s)decimalnumericNumber(m,n)Decimal(p,s)NumericSingleRealFloatRealSingleDoubleFloatFloatDoubleprecisionDoubleDatetimeDatetimeDateDateDatetimeTimestampTimestampTimestampTimestampBooleanBitSmallintSmallintYesnobinary(n)binary(8000)binaryvarbinary(n)varbinary(8000)Row(2000)binarylongbinaryImageLongLongrowLONGVARGRAPHICLONGBINARYGENERALlongtextTextLongrowLONGVARCHARlongtext域域可以帮助识别模型中信息的类型，它定义了列的一组有效值，使用域来定义列可以使得不同表中列的数据特征更容易标准化及便于修改。域分为标准域和方案域两种，标准域由域名前加“_”构成，各方案工程通用，方案域由各方案工程根据实际情况自行定义。标准域的定义见下表：〔可扩展〕名称编码说明类型长度精度addressAddressvarchar(100)100eMailEMail电子邮件varchar(30)30memomemo备注varchar(1000)1000PathNamepathName路径名varchar(250)250Percentpercent百分比decimal(10,2)102moneyMoney金额decimal(18,2)182pricePrice单价intSexSex性别char(1)1telTelephone号码char(16)16unitunitOfMeasure计量单位char(16)16wwwwww网址varchar(30)30键设计原那么键〔主键〕标注字段的唯一并保证表之间〔外键〕的完整性，以下方法有助于进行键的设计：尽量防止使用复合键作为主键；为关联字段创立外键；所有的键都必须唯一；一个外键总是关联唯一的键字段；防止选择用户可编辑的字段作为键，因为一旦输入错误，唯一能做的就是删除整个记录然后从头开始；使用流水号表〔?〕主键定义方案：〔供讨论〕主键生成算法1：根据公司现行状况Int类型，采用序列,长度为11位；Varchar类型，采用java生成，长度为32位；索引设计原那么什么是索引索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B*树，位图索引也是如此，只不过是叶子节点不同B*数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。使用索引的目的加快查询速度减少I/O操作消除磁盘排序何时使用索引查询返回的记录数排序表<40%非排序表<7%表的碎片较多〔频繁增加、删除〕索引的种类非唯一索引〔最常用〕唯一索引位图索引局部有前缀分区索引局部无前缀分区索引全局有前缀分区索引散列分区索引基于函数的索引数据完整性数据完整性有四种类型：实体完整性、域完整性、引用完整性和用户定义完整性等几种。大型数据库一般都提供丰富的约束机制，以保证数据的完整性，通常采用的有键、规那么、约束和触发器等几种。实体完整性实体完整性将行定义为特定表的唯一实体。实体完整性强制表的标识符列或主键的完整性〔通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性〕。域完整性域完整性是指给定列的输入有效性。强制域有效性的方法有：限制类型〔通过数据类型〕、格式〔通过CHECK约束和规那么〕或可能值的范围〔通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规那么〕。引用完整性在输入或删除记录时，引用完整性保持表之间已定义的关系。引用完整性基于外键与主键之间或外键与唯一键之间的关系〔通过FOREIGNKEY和CHECK约束〕。引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。如果定义了强制引用完整性时，那么禁止用户进行以下操作：当主表中没有关联的记录时，将记录添加到相关表中。更改主表中的值并导致相关表中的记录孤立。从主表中删除记录，但仍存在与该记录匹配的相关记录。用户定义完整性用户定义完整性使您得以定义不属于其它任何完整性分类的特定业务规那么。所有的完整性类型都支持用户定义完整性〔包括CREATETABLE中的所有列级和表级约束、存储过程和触发器〕。关系设计原那么在关系数据库中，关系能防止冗余的数据。关系是通过匹配键列中的数据而工作的，而键列通常是两个表中具有相同名称的列。在大多数情况下，关系将一个表中为每个行提供唯一标识符的主键与另一个表中外键内的项相匹配。表与表之间存在三种类型的关系，即一对一关系、一对多关系和多对多关系。一对一关系在一对一关系中，表A中的一行最多只能与表B中的一行相匹配，反之亦然。如果两个相关列都是主键或具有唯一约束，那么创立的是一对一关系。存在一对一关系时通常会将列保存在同一个表中，但出于以下目的，可以将列保存在一个以上的表中：分割一个含有许多列的表。出于平安考虑而隔离表的某一局部。存储可以很容易删除的临时数据，只需删除表即可删除这些数据。存储只应用于主表子集的信息。一对多关系一对多关系是最常见的关系类型，俗称主从表。在这种关系类型中，表A中的行可以在表B中有许多匹配行，但是表B中的行只能在表A中有一个匹配行。多对多关系在多对多关系中，表A中的一行可与表B中的多行相匹配，反之亦然。通过定义称为连接表的第三方表创立这样的关系，该连接表的主键包括表A和表B中的外键。如果两个实体之间存在一对多关系，而且有可能会转化为多对多关系，那么最好一开始就设置成多对多关系。这可以防止日后的很多麻烦。命名标准数据库对象的命名应遵循统一的标准，以便于管理和系统之间的集成。表如果表名是一个和两个单词，名字就取全名；如果表的名字由3个单词组成，就各取单词缩写。对关联表来说，表名加上RELATE_作为中缀，后面加代表此关联的实意单词。对工作用表(查询、报表)来说，表名加上TMP_、QRY_或者REP_等中缀加以区别。表名长度不能超过20，ORACLE默认最大长度30.对全局用表，表名前缀使用GBL_。例如：数据表T_ACCOUNT临时表T_TEMP_ACCOUNT关联表 T_ACCOUNT_RELATE_Company视图命名标准和表类似，前缀为VIEW。例如：T_VIEW_ACCOUNT。域〔Domains〕域名定义使用有具体意义英文单词，如果是一个或两个单词，名字就取全名；如果名字由3个单词组成，就各取单词缩写。数据库系统保存字不能用作域名。例如：金额money numeric(18,2)日期date datetime地址address varchar(100)字段字段定义参照“表”名定义，无须加子系统前缀，