1、数据库设计规范键入文档副标题在此处键入文档的摘要。摘要通常是对文档内容的简短总结。在此处键入文档的摘要。摘要通常是对文档内容的简短总结。年微软用户微软中国选取日期目录1、目的 32、概述 33、数据库物理设计原则 33.1、数据库环境配置原则 .33.2、数据库设计原则 .33.3、数据库表空间设计原则 .64、数据库逻辑设计原则 64.1、命名规范 .64.3、数据类型 .84.4、设计 .94.5、 SQL 编写 105、设计工具 111、目的定义数据库设计设计规范，作为数据库设计、规划、开发以及维护人员的技术参考资料。2、概述本文主要描述数据库环境配置、数据库物理设计、数据库逻辑设计、视
2、图、存储过程、应用程序设计等方面的设计规范。 3、数据库物理设计原则3.1、数据库环境配置原则* 操作系统环境：对于中小型数据库系统，采用 linux 操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统或者大型的数据库系统，可以采用 Oracle RAC 的集群数据库的方法。* 内存要求对于 linux 操作系统下的数据库,SGA 设计在一个合适的范围内：物理内存的 50%70%。* 交换区设计当物理内存在 2G 以下的情况下，交换分区 swap 为物理内存的 3 倍，当物理内存2G 的情况下，swap 大小为物理内存的 12 倍。* 其他环境变量参考 Oracle 相关的安装文档
3、和随机文档。3.2、数据库设计原则* 约定我们约定：采用 26 个英文字母（区分大小写）和 09 这十个自然数，加上下划线_组成，共 63 个字符。不能出现其他字符（注释除外）。* 数据库 SID数据库 SID 是唯一标志数据库的符号，命名长度控制在 10 个字符内。对于单节点数据库。一般用项目名称的英文简缩写，比如教育办公自动化，可用EduOA 表示 对于集群数据库，当命名 SID 后，各节点 SID 自动命名为 SIDnn，其中 nn为节点号：1，2，,64。例如 rac1、rac2、rac24。* 数据库全局名数据库全局名称：.domain* 数据库类型选择对于海量数据库系统，采用 da
4、ta warehouse 的类型。对于小型数据库或OLTP 类型的数据库，采用 Transaction Processing 类型。* 数据库连接类型选择Oracle 数据库有专用服务器连接类型和多线程服务器 MTS 连接类型。对于批处理服务，需要专用服务器连接方式，而对于 OLTP 服务则 MTS 的连接方式 比较合适。由于采用 MTS 后，可以通过配置网络服务实现某些特定批处理服务采用专用服务器连接方式，所以数据库设计时一般采用 MTS 类型。* 数据库 SGA 配置数据库 SGA 可以采用手工配置或按物理内存比例配置，在数据库初始设计阶段采用按比例配置方式，在实际应用中按系统调优方式修改
5、 SGA。* 数据库字符集选择为了使数据库能够正确支持多国语言，必须配置合适的数据库字符集，采用 UTF8 字符集。* 数据库其他参数配置* DB_FILES Db_files 是数据库能够同时打开的文件数量，默认值是 200 个。当数据库规划时文件数量 FILES 接近或超过 200 个时候，按以下估计值配置:DB_FILES = FILES * 1.5 * Db_block_size Db_block_size 是数据库最小物理单元，一旦数据库创建完成，该参数无法修改，db_block_size 按以下规则调整：数据仓库类型： db_block_size 尽可能大，采用 8192 或 16
6、384OLTP 类型： db_block_size 用比较小的取值范围： 2048 或 4096* 数据库控制文件配置* 控制文件镜象多个控制文件存放在不同的物理位置。* 控制文件配置控制文件中参数设置，最大的数据文件数量不能小于数据库参数db_files。* 数据库日志文件配置* 日志文件大小日志文件的大小由数据库事务处理量决定，在设计过程中，确保每20 分钟切换一个日志文件。所以对于批处理系统，日志文件大小为几百 M 到几G 的大小。对于 OLTP 系统，日志文件大小为几百 M 以内。* 日志文件组数量对于批处理系统：日志文件组为 510 组。对于 OLTP 系统：日志文件组为 35 组。
7、每组日志大小保持一致。对于集群数据库系统，每节点有各自独立的日志组。* 日志成员数量为了确保日志能够镜象作用，每日志组的成员为 2 个。* 数据库回滚段配置在 Oracle9i 数据库中，设计 Undo 表空间取代以前版本的回滚段表空间。Undo 表空间大小的设计规范由以下公司计算：Undospace = UR * UPS *db_block_size+ 冗余量UR： 表示在 undo 中保持的最长时间数（秒），由数据库参数UNDO_RETENTION 值决定。UPS：表示在 undo 中，每秒产生的数据库块数量。例如：在数据库中保留 2 小时的回退数据，假定每秒产生 200 个数据库块。则
8、Undospace = 2 * 3600 * 200 * 4K = 5.8G* 数据库临时段表空间配置数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。* 数据库系统表空间配置系统表空间大小 1G 左右，除了存放数据库数据字典的数据外，其他数据不得存储在系统表空间。3.3、数据库表空间设计原则* 表空间大小定义原则当表空间?大小小于操作系统对最大文件限制时，表空间由一个文件组成。如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：Tablespace + sum (数据段+索引段)*150% * 表空间扩展性设计原则表空间采用自
9、动扩展的方式、表空间采用 local 管理方式。4、数据库逻辑设计原则4.1、命名规范* 表数据表必须以有特征含义的单词或缩写组成，中间可以用“_”分割，一般用子系统(或者业务块)_表业务含义的英文单词组，子系统不要与系统的前缀相同，否 则可能引起去扫描系统库表，很多其他同行的规范中可能会什么t_、tb_、tbl_开头，此规范不建议这么做，因为数据库基本的对象应该就是表，没必要再 画蛇添足，至于其它跟表不同类型的对象，倒需要加个类型前缀，以示区分，引用时清晰。* 表分区p_：分区名必须有特定含义的单词或字串。例如 :分区 p_200410 表示该分区存储 2004 年 10 月份的数据。* 字
10、段对表内的字段不要加前缀，因为在一个表内，这些肯定是固定的，一般只要能看明白就行了，如果是外键，采用法则是字段能够表明引用自哪张表。* 主键pk_：pk_表名。* 索引idx_：idx_表名_构成的字段首字母（最后一个单词全字母）。* 外键fk_：fk_来源表_来源字段单词首字母(最后一个单词全字母)。* 索引分区无。由系统自动产生* 视图v_：按业务操作命名视图。* 实体化视图mv_：按业务操作命名实体化视图。* 存储过程up_：不要用系统过程的前缀，防止在系统库中查询一次，比如不用 sp 做为前缀，因为如果是 sp，有的数据库系统会认为是系统过程，在系统库中会扫描一次，降低了数据库效能。比
11、如可用 up_过程行为含义的英文单词组。* 触发器trg_：trg_促发行为含义的英文单词组* 函数uf_：同样不要使用系统函数的前缀，可用 uf_函数行为含义的英文单词组。 * 数据包pkg_：按业务操作集合命名数据包。* 序列seq_：按业务属性命名。* 表空间* 公用表空间tbs_：根据存储的特性命名，例如： tbs_parameter 。* 专用表空间tbs_表名称_nn。该表空间存储特定表，或表分区的数据。* 数据文件表空间名称.dbf 。* 普通变量var_ ： * 游标变量cur_：存放游标记录集。* 记录型变量rec_： 存放记录型数据。* 表类型变量tab_： 存放表类型数据
12、。4.2、命名* 语言命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。* 大小写名称一律小写，以方便不同数据库移植，以及避免程序调用问题。* 单词分隔命名的各单词之间可以使用下划线进行分隔。* 保留字命名不允许使用 SQL 保留字。* 命名长度表名、字段名、视图名长度应限制在 30 个字符
13、内(含前缀)。变量名的长度限制为 29(不包括标识字符)。除了前缀，组成单词一般控制在三个单词之内。如果有明确的英文缩写的可以采用英文缩写。* 字段名称同一个字段名在一个数据库中只能代表一个意思。比如 telephone 在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。* 重复性以上的命名不要与数据库的保留字重复，也不要跟系统里的对象重名，虽然在定义重复或者数据库访问时候不一定引起数据库系统的报错。* 命名范例：4.3、数据类型* 字符型固定长度的字串类型采用 char，长度不固定的字串类型采用 varc
14、har。避免在长度不固定的情况下采用 char 类型。如果在数据迁移等出现以上情况，则必须使用 trim()函数截去字串后的空格。* 数字型数字型字段尽量采用 number 类型。* 日期和时间* 系统时间由数据库产生的系统时间首选数据库的日期型，如 DATE 类型。* 外部时间由数据导入或外部应用程序产生的日期时间类型采用varchar 类型，数据格式采用：YYYYMMDDHH24MISS。* 大字段如无特别需要，避免使用大字段(blob，clob，long，text，image 等)。* 唯一键对于数字型唯一键值，尽可能用系列 sequence 产生。* 不同类型的数据库数据类型对照表：*
15、 不同类型的数据库数据类型差异说明：在 SQLServer 版本为 2000 版本，Sybase 版本为 12.5 版本，Oracle 版本10g 版本下的差异1.SQLServer2000 的字符类型长度最大均为 8K，而 Sybase 字符类型的最大长度与页的大小相等。2.SQLServer2000 中没有 unichar 和 univarchar 字符类型;在 Sybase 中nchar 和 nvarchar 代表本地语言字符集，而 unichar 和 univarchar 代表unicode 方式;而 SQLServer2000 中 nchar 和 nvarchar 才代表 unico
16、de 方式。3.SQLServer2000 中新增 bigint 整数类型；Sybase 中没有。4.SQLServer2000 和 Sybase 中都有 numeric 和 decimal 类型，这两个类型的区别在于 numeric(n,0)可以用来表示 identity 字段，但是 decimal 不行。 5.SQLServer2000 中有 ntext 类型；而 Sybase 中没有6.SQLServer2000 中的 bit 类型可以表示为 0/1/NULL，而 Sybase 中只能为1/0 两种选择7.Sybase 中新增 date 和 time 类型8.Oracle 中新增 var
17、char2 类型9.Oracle 中无 unichar 和 univarchar 类型，但是 nchar 和 nvarchar 表示unicode 方式10.Oracle 中只有 date 类型11.Oracle 中的 Number 类型可以代替 Sybase 和 SQLServer 中的所有数字类型12.Oracle 中没有货币类型13.Oracle 中有文件指针类型 bfile14.Oracle 中没有 bit 类型 4.4、设计* 范式如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，
18、但基本上要达到 3NF.如非确实必要，避免一个字段中存储多个标志的做法。如 11101 表示 5 个标志的一种取值。这往往是增加复杂度，降低性能的地方。* 表设计每个表必须包含以下属性参数: TABLESPACE，PCTUSED，STORAGE。根据表的不同属性进行表类型设计：* 分区表对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能：如果表按某些字段进行增长，则采用按字段值范围进行范围分区。如果表按某个字段的几个关键值进行分布，则采用列表分区。对于静态表，则采用 hash 分区或列表分区。在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。* 聚蔟表如果某
19、几个静态表关系比较密切，则可以采用聚蔟表的方法。* 索引对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。在分区表中，尽量采用 local 分区索引以方便分区维护。* 主键关联表的父表要求有主健。* 外键对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，要求对外健建立索引。* NULL 值对于字段能否 null，应该在 sql 建表脚本中明确指明，不应使用缺省。由于 NULL 值在参加任何运算中，结果均为 NULL。所
20、以在应用程序中必须利用nvl()函数把可能为 NULL 值得字段或变量转换为非 NULL 的默认值。例如：NVL（sale,0）。* 注释表、字段等应该有中文名称注释，以及需要说明的内容。函数，过程，包的注释：/*/* 存储过程：行为含义 * 编 制 者 ： * 修 改 人 ： * 编制日期：* 输入参数：* 输出参数： * 调用例子： * 说 明： /*/4.5、SQL 编写* 字符类型数据SQL 中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用 trim(),lower()等函数格式化匹配条件。* 复杂 sql对于非
21、常复杂的 sql(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂 SQL 可以考虑使用程序实现。* 高效性* 避免 In 子句使用 In 或 not In 子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。* 避免嵌套的 Select 子句这个实际上是 In 子句的特例。* 避免使用 Select * 语句如果不是必要取出所有数据，不要用*来代替，应给出字段列表。* 避免不必要的排序不必要的数据排序大大的降低系统性能。* 健壮性* Insert 语句使用 Insert 语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。* 安全性* Where 条件无论在使用 Select,还是使用破坏力极大的 Update 和 Delete 语句时，一定要检查 Where 条件判断的完整性，不要在运行时出现数据的重大丢失.如果不确定，最好先用 Select 语句带上相同条件来果一下结果集，来检验条件是否正确.5、设计工具统一使用 sybase power designer 设计工具，在该工具上完成物理模型的设计，并且由该工具产生数据库脚本程序。
