数据库设计规范

数据库设计规范
[日期：2011-12-02] 来源：Linux社区  作者：xujinyang

 

 
1、目的
     定义数据库设计设计规范，作为数据库设计、规划、开发以及维护人员的技术参考资料。
2、概述
     本文主要描述数据库环境配置、数据库物理设计、数据库逻辑设计、视图、存储过程、应用程序设计等方面的设计规范。

 

3、数据库物理设计原则
3.1、数据库环境配置原则
* 操作系统环境：
     对于中小型数据库系统，采用linux操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统或者大型的数据库系统，可以采用Oracle RAC的集群数据库的方法。
* 内存要求
     对于linux操作系统下的数据库,SGA设计在一个合适的范围内：物理内存的50%—70%。
* 交换区设计
     当物理内存在2G以下的情况下，交换分区swap为物理内存的3倍，当物理内存>2G的情况下，swap大小为物理内存的1—2倍。
* 其他环境变量

 

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

 

3.2、数据库设计原则
* 约定
     我们约定：采用26个英文字母（区分大小写）和0－9这十个自然数，加上下划线_组成，共63个字符。不能出现其他字符（注释除外）。
* 数据库SID
     数据库SID是唯一标志数据库的符号，命名长度控制在10个字符内。对于单节点数据库。一般用项目名称的英文简缩写，比如教育办公自动化，可用EduOA表示
     对于集群数据库，当命名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、数据库表空间设计原则
* 表空间大小定义原则
     当表空间?大小小于操作系统对最大文件限制时，表空间由一个文件组成。如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：
     Tablespace + sum (数据段+索引段)*150%

 

* 表空间扩展性设计原则
     表空间采用自动扩展的方式、表空间采用local管理方式。

 

4、数据库逻辑设计原则
4.1、命名规范
* 表
     数据表必须以有特征含义的单词或缩写组成，中间可以用“_”分割，一般用子系统(或者业务块)_表业务含义的英文单词组，子系统不要与系统的前缀相同，否则可能引起去扫描系统库表，很多其他同行的规范中可能会什么t_、tb_、tbl_开头，此规范不建议这么做，因为数据库基本的对象应该就是表，没必要再画蛇添足，至于其它跟表不同类型的对象，倒需要加个类型前缀，以示区分，引用时清晰。
* 表分区
     p_：分区名必须有特定含义的单词或字串。例如 :
分区p_200410表示该分区存储 2004年10月份的数据。
* 字段
     对表内的字段不要加前缀，因为在一个表内，这些肯定是固定的，一般只要能看明白就行了，如果是外键，采用法则是字段能够表明引用自哪张表。
* 主键
     pk_：pk_表名。
* 索引
     idx_：idx_表名_构成的字段首字母（最后一个单词全字母）。
* 外键
     fk_：fk_来源表_来源字段单词首字母(最后一个单词全字母)。
* 索引分区
     无。由系统自动产生
* 视图
      v_：按业务操作命名视图。
* 实体化视图
     mv_：按业务操作命名实体化视图。
* 存储过程
     up_：不要用系统过程的前缀，防止在系统库中查询一次，比如不用sp做为前缀，因为如果是sp，有的数据库系统会认为是系统过程，在系统库中会扫描一次，降低了数据库效能。比如可用up_过程行为含义的英文单词组。
* 触发器
     trg_：trg_促发行为含义的英文单词组
* 函数
     uf_：同样不要使用系统函数的前缀，可用uf_函数行为含义的英文单词组。
* 数据包
     pkg_：按业务操作集合命名数据包。
* 序列
     seq_：按业务属性命名。
* 表空间
* 公用表空间
     tbs_：根据存储的特性命名，例如： tbs_parameter 。
* 专用表空间
     tbs_表名称_nn。该表空间存储特定表，或表分区的数据。
* 数据文件
     表空间名称.dbf 。
* 普通变量
     var_ ：
* 游标变量
     cur_：存放游标记录集。
* 记录型变量
     rec_： 存放记录型数据。
* 表类型变量
tab_： 存放表类型数据。

 

4.2、命名
* 语言
     命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。
     英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名
     当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。
     当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。
* 大小写
     名称一律小写，以方便不同数据库移植，以及避免程序调用问题。
* 单词分隔
     命名的各单词之间可以使用下划线进行分隔。
* 保留字
     命名不允许使用SQL保留字。
* 命名长度
     表名、字段名、视图名长度应限制在30个字符内(含前缀)。变量名的长度限制为29(不包括标识字符@)。除了前缀，组成单词一般控制在三个单词之内。如果有明确的英文缩写的可以采用英文缩写。
* 字段名称
　　同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。
　　不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。
* 重复性
　　以上的命名不要与数据库的保留字重复，也不要跟系统里的对象重名，虽然在定义重复或者数据库访问时候不一定引起数据库系统的报错。
* 命名范例：

 

4.3、数据类型
* 字符型
     固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。
* 数字型
     数字型字段尽量采用number类型。
* 日期和时间
         * 系统时间
                  由数据库产生的系统时间首选数据库的日期型，如DATE类型。
         * 外部时间
                  由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：YYYYMMDDHH24MISS。
* 大字段
     如无特别需要，避免使用大字段(blob，clob，long，text，image等)。
* 唯一键
　　对于数字型唯一键值，尽可能用系列sequence产生。
* 不同类型的数据库数据类型对照表：

 

 

* 不同类型的数据库数据类型差异说明：
　　在SQLServer版本为2000版本，Sybase版本为12.5版本，Oracle版本10g版本下的差异
　　1.SQLServer2000的字符类型长度最大均为8K，而Sybase字符类型的最大长度与页的大小相等。
　　2.SQLServer2000 中没有unichar和univarchar字符类型;在Sybase中nchar和nvarchar代表本地语言字符集，而unichar和 univarchar代表unicode方式;而SQLServer2000中nchar和nvarchar才代表unicode方式。
　　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中新增varchar2类型
　　9.Oracle中无unichar和univarchar类型，但是nchar和nvarchar表示unicode方式
　　10.Oracle中只有date类型
　　11.Oracle中的Number类型可以代替Sybase和SQLServer中的所有数字类型
　　12.Oracle中没有货币类型
　　13.Oracle中有文件指针类型bfile
　　14.Oracle中没有bit类型

 

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语句带上相同条件来果一下结果集，来检验条件是否正确.
5、设计工具
     统一使用sybase power designer设计工具，在该工具上完成物理模型的设计，并且由该工具产生数据库脚本程序。

 

本篇文章来源于 Linux公社网站()  原文链接：