数据库设计规范

数据库设计规范 （oracle版本）

V1.0

2004 年 10 月 12 日

目录

1、目的 ........................................................................................................................... 3 2、概述 ........................................................................................................................... 3 3、数据库物理设计原则 ................................................................................................... 3

3.1、数据库环境配置原则 ........................................................................................ 3 3.2、数据库设计原则 ............................................................................................... 4 3.3、数据库表空间设计原则 ..................................................................................... 7 4、数据库逻辑设计原则 ................................................................................................... 8

4.1、命名规范 .......................................................................................................... 8 4.2、命名 ................................................................................................................. 9 4.3、数据类型 ........................................................................................................ 10 4.4、设计 ................................................................................................................11 4.5、SQL编写......................................................................................................... 12

1、目的

定义数据库设计设计规范，作为数据库设计、规划、开发以及维护人员的技术参考资料。

2、概述

本文主要根据oracle9i数据库性能特点，描述数据库环境配置、数据库物理设计、数据库逻辑设计、视图、存储过程、应用程序设计等方面的设计规范。

3、数据库物理设计原则

3.1、数据库环境配置原则

 操作系统环境：

对于中小型数据库系统，采用linux操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统，可以采用Oracle9i RAC的集群数据库的方法，集群节点数范围在2—64个。

对于大型数据库系统，可以采用Sun Solaris SPARC 64位小型机系统或HP 9000 系列小型机系统。  内存要求

对于linux操作系统下的数据库,由于在正常情况下Oracle对SGA的管理能力不超过1.7G。所以总的物理内存在4G以下。SGA的大小为物理内存的50%—75%。

对于64位的小型系统，Oracle数据库对SGA的管理超过2G的限制，SGA设计在一个合适的范围内：物理内存的50%—70%，当SGA过大的时候会导致内存分页，影响系统性能。  交换区设计

当物理内存在2G以下的情况下，交换分区swap为物理内存的3倍，当物理内存>2G的情况下，swap大小为物理内存的1—2倍。  其他环境变量

参考Oracle相关的安装文档和随机文档。

3.2、数据库设计原则

 数据库SID

数据库SID是唯一标志数据库的符号，命名长度不能超过5个字符。对于单节点数据库，以字符开头的5个长度以内字串作为SID的命名。

对于集群数据库，当命名SID后，各节点SID自动命名为SIDnn，其中nn为节点号：1，2，…,64。例如rac1、rac2、rac24。

 数据库全局名

数据库全局名称：.domain

 数据库类型选择

对于海量数据库系统，采用data warehouse的类型。对于小型数据库或OLTP类型的数据库，采用Transaction Processing类型。

 数据库连接类型选择

Oracle数据库有专用服务器连接类型和多线程服务器MTS连接类型。对于批处理服务，需要专用服务器连接方式，而对于OLTP服务则MTS的连接方式比较合适。由于采用MTS后，可以通过配置网络服务实现某些特定批处理服务采用专用服务器连接方式，所以数据库设计时一般采用MTS类型。

 数据库SGA配置

数据库SGA可以采用手工配置或按物理内存比例配置，在数据库初始设计阶段采用按比例配置方式，在实际应用中按系统调优方式修改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 或 16384 OLTP类型： db_block_size 用比较小的取值范围： 2048 或 4096

 数据库控制文件配置

 

控制文件镜象

多个控制文件存放在不同的物理位置。 控制文件配置

控制文件中参数设置，最大的数据文件数量不能小于数据库参数db_files。

 数据库日志文件配置

 日志文件大小

日志文件的大小由数据库事务处理量决定，在设计过程中，确保每20分钟切换一个日志文件。所以对于批处理系统，日志文件大小为几百M 到几G的大小。对于OLTP系统，日志文件大小为几百M以内。

 日志文件组数量

对于批处理系统：日志文件组为5—10组。 对于OLTP系统：日志文件组为 3—5组。 每组日志大小保持一致。

对于集群数据库系统，每节点有各自独立的日志组。

 日志成员数量

为了确保日志能够镜象作用，每日志组的成员为2个。

 数据库回滚段配置

在Oracle9i数据库中，设计Undo表空间取代以前版本的回滚段表空间。

Undo 表空间大小的设计规范由以下公司计算： Undospace = UR * UPS *db_block_size+ 冗余量

UR： 表示在undo中保持的最长时间数（秒），由数据库参数UNDO_RETENTION值决定。

UPS：表示在undo中，每秒产生的数据库块数量。 例如：在数据库中保留2小时的回退数据，假定每小时产生200个数据库块。则Undospace = 2 * 3600 * 200 * 4K = 5.8G

 数据库临时段表空间配置

数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。

 数据库系统表空间配置

系统表空间大小1G左右，除了存放数据库数据字典的数据外，其他数据不得存储在系统表空间。

3.3、数据库表空间设计原则

 表空间大小定义原则

当表空间 大小小于操作系统对最大文件限制时，表空间由一个文件组成。如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：

Tablespace + sum (数据段+索引段)*150%

 表空间扩展性设计原则

表空间采用自动扩展的方式、表空间采用local管理方式。

4、数据库逻辑设计原则

4.1、命名规范

 表

Tbl_ 。数据表必须以有特征含义的单词或缩写组成，中间可以用“_”分割，例如：tbl_pstn_detail。

 表分区

p 。分区名必须有特定含义的单词或字串。例如 :

tbl_pstn_detail 的分区p2004100101表示该分区存储 2004100101时段的数据。

 字段

无。

 主键

PK_。主键名称应是 前缀+表名+构成的字段名。如果复合主键的构成字段较多，则只包含第一个字段。表名可以去掉前缀。

 索引

IDX_。索引名称应是 前缀+表名+构成的字段名。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。表名可以去掉前缀。

 外键

FK_。外键名称应是 前缀+ 外键表名 + 主键表名 + 外键表构成的字段名。表名可以去掉前缀。

 索引分区

无。由系统自动产生

 视图

V_。按业务操作命名视图。

 实体化视图

MV_。按业务操作命名实体化视图。

 存储过程

Proc_ 。按业务操作命名存储过程

 触发器

Trig_ 。触发器名应是 前缀 + 表名 + 触发器名。

 函数

Func_ 。按业务操作命名函数

 数据包

Pkg_ 。按业务操作集合命名数据包。

 序列

Seq_ 。按业务属性命名。

 表空间

 公用表空间

Tbs_ 。 根据存储的特性命名，例如： tbs_parameter 。

 专用表空间

Tbs_表名称_nn。该表空间存储特定表，或表分区的数据

 数据文件

表空间nn.dbf 。nn =1，2，3，4，…

 普通变量

Var_ 。

 游标变量

Cur_ 。存放游标记录集。

 记录型变量

Rec_ 。 存放记录型数据。

 表类型变量

Tab_ 。 存放表类型数据。

4.2、命名

 语言

命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。

英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名

当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。

当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。

 大小写

名称一律大写，以方便不同数据库移植，以及避免程序调用问题。

 单词分隔

命名的各单词之间可以使用下划线进行分隔。

 保留字

命名不允许使用SQL保留字。

 命名长度

表名、字段名、视图名长度应限制在20个字符内(含前缀)。

 字段名称

同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。

不同的表用于相同内容的字段应该采用同样的名称，字段类型

定义。

4.3、数据类型

 字符型

固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。

 数字型

数字型字段尽量采用number类型。

 日期和时间

 系统时间

由数据库产生的系统时间首选数据库的日期型，如DATE类型。

 外部时间

由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：YYYYMMDDHH24MISS。

 大字段

如无特别需要，避免使用大字段(blob，clob，long，text，image

等)。

 唯一键

对于数字型唯一键值，尽可能用系列sequence产生。

4.4、设计

 范式

如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3NF.

如非确实必要，避免一个字段中存储多个标志的做法。如11101表示5个标志的一种取值。这往往是增加复杂度，降低性能的地方。

 表设计

每个表必须包含以下属性参数: TABLESPACE，PCTUSED，STORAGE。根据表的不同属性进行表类型设计：

 分区表

对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能：

如果表按某些字段进行增长，则采用按字段值范围进行范围分区。

如果表按某个字段的几个关键值进行分布，则采用列表分区。

对于静态表，则采用hash分区或列表分区。

在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。  聚蔟表

如果某几个静态表关系比较密切，则可以采用聚蔟表的方法。

 索引

对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。

对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。

在分区表中，尽量采用local分区索引以方便分区维护。

 主键

关联表的父表要求有主健。

 外键

对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，要求对外健

建立索引。

 NULL值

对于字段能否null，应该在sql建表脚本中明确指明，不应使用

缺省。由于NULL值在参加任何运算中，结果均为NULL。所以在应用程序中必须利用nvl()函数把可能为NULL值得字段或变量转换为非NULL的默认值。例如：NVL（sale,0）。

 注释

表、字段等应该有中文名称注释，以及需要说明的内容

4.5、SQL编写

 字符类型数据

SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用trim(),lower()等函数格式化匹配条件。

 复杂sql

对于非常复杂的sql(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

 高效性

 避免In子句

使用In 或 not In子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。  避免嵌套的Select子句

这个实际上是In子句的特例。  避免使用Select * 语句

如果不是必要取出所有数据，不要用*来代替，应给出字段列表。

 避免不必要的排序

不必要的数据排序大大的降低系统性能。

 健壮性

 Insert语句

使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。

 安全性

 Where 条件

无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失.如果不确定，最好先用Select语句带上相同条件来果一下结果集，来检验条件是否正确.

4、设计工具

统一使用sybase power designer设计工具，在该工具上完成物理模型的设计，并且由该工具产生数据库脚本程序。