数据库设计说明.docx
《数据库设计说明.docx》由会员分享，可在线阅读，更多相关《数据库设计说明.docx（19页珍藏版）》请在冰点文库上搜索。
数据库设计说明
数据库设计规范说明
1.概述3
1.1.目的3
1.2.适用范围3
1.3.术语定义3
1.4.标准词汇3
2.数据库设计原则4
3.数据库设计目标4
4.命名规范5
4.1.基本原则5
4.2.具体数据对象命名规范6
4.3.数据类型7
4.3.1.字符型7
4.3.2.数字型7
4.3.3.日期和时间7
4.3.3.1.系统时间7
4.3.3.2.外部时间7
4.3.4.大字段7
4.3.5.唯一键7
4.4. 触发器（Trigger）命名规范7
4.5. 代码缩进约定8
4.6. 函数（存储过程）头头注释8
4.7. 函数（存储过程）体内编写规范9
4.8. 数据字典书写格式9
4.9. E-R图的绘制模式10
4.10.数据库后期维护的要求10
5.设计过程规范10
5.1.数据分析阶段11
5.2.概念设计阶段11
5.3.逻辑设计阶段12
5.4.物理设计阶段13
6.SQL编写14
6.1.1.字符类型数据14
6.1.2.复杂sql14
6.1.3.高效性14
6.1.3.1.避免In子句14
6.1.3.2.避免嵌套的Select子句15
6.1.3.3.避免使用Select*语句15
6.1.3.4.避免不必要的排序15
6.1.4.健壮性15
6.1.4.1.Insert语句15
6.1.4.2.Count（*）、Count（id）、count（distinctid）的区别15
6.1.4.3.Notnull为字段类型性质的约束15
6.1.4.4.外键值可用null的问题16
6.1.4.5.序列sequence跳号的问题16
6.1.4.6.unicn\intersect\minus使用ordeyby的注意事项16
6.1.5.安全性16
6.1.6.完整性16
7.特殊说明17
7.1.编码17
7.2.注释17
7.3.引擎17
1.概述
1.1.目的
软件研发数据库设计规范作为数据库设计的操作规范，详细描述了数据库设计过程及结果，用于指导系统设计人员正确理解和开展数据库设计。
1.2.适用范围
1.3.术语定义
DBMS：
数据库管理系统，常用的商业DBMS有Oracle,SQLServer,DB2等。
数据库设计：
数据库设计是在给定的应用场景下，构造适用的数据库模式，建立数据库及其应用系统，有效存储数据，满足用户信息要求和处理要求。
概念数据模型：
概念数据模型以实体-关系（Entity-RelationShip,简称E-R）理论为基础，并对这一理论进行了扩充。
它从用户的观点出发对信息进行建模，主要用于数据库概念级别的设计，独立于机器和各DBMS产品。
可以用SybasePowerDesigner工具来建立概念数据模型（CDM）。
逻辑数据模型：
将概念数据模型转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式。
可以用SybasePowerDesigner或者MysqlWorkbench工具直接建立逻辑数据模型（LDM），或者通过CDM转换得到。
物理数据模型：
在逻辑数据模型基础上，根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。
可以用SybasePowerDesigner工具直接建立物理数据模型（PDM），或者通过CDM/LDM转换得到。
1.4.标准词汇
为使相关的设计文档可读性强，消除歧义，请使用标准词汇。
1.表（Table）
2.严格区分“表”和“库”，禁止“代码库”等不规范用法，而应使用“代码表”等词汇。
例如：
应命名为‘门诊信息表’，不能命名为‘门诊信息库’。
3.字段（Field）
字段为行和列的交。
严格区分“列（column）”和“字段”的用法。
4.代码和编码
代码与编码是有区别的，编码作动词使用，由用户自己确定编码规则，例如：
应称‘邮政编码’，不能称‘邮政代码’。
代码是在业务中已存在的名词，不需要再确定编码规则，例如：
应称‘股票代码’，不能称‘股票编码’。
5.序号
避免使用“编号”等用法，如果是系统自动产生的值，都应称‘序号’。
例如：
‘流水序号’。
如果是用户自已编的号，一律称‘编号’，不能称‘编码’。
6.数据类型（DataType）
指datatype时，一律用“数据类型”，不要简称“类型”等。
7.行（Row）
行和记录（record）含义相同，在文档中应尽量使用“行”。
8.行数、列数
避免使用“记录数”、“字段数”等用法。
2.数据库设计原则
按阶段实施并形成该阶段的成果物
一般符合3NF范式要求；兼顾规范与效率
使用公司规定的数据库设计软件工具
命名符合公司标准和项目标准
3.数据库设计目标
规范性：
一般符合3NF范式要求，减少冗余数据。
高效率：
兼顾规范与效率，适当进行反范式化，满足应用系统的性能要求。
紧凑性：
例如能用char（10）的就不要用char（20），提高存储的利用率和系统性能，但同时也要兼顾扩展性和可移植性。
易用性：
数据库设计清晰易用，用户和开发人员均能容易地理解。
4.命名规范
4.1.基本原则
1.所有数据库的实体命名都以字母、下划线或数字组成。
2.有的数据库，有索引名长度18个字节限制，但考虑标准产品跨数据库的一致性，因此索引、主键、外键名统一遵循18个字节长度限制。
3.使用可以准确说明数据表/数据列/函数/存储过程/触发器/序列/包/包体/作业的英文描述符。
例如，采用类似FIRST_NAME、PRODUCT_TYPE_ID这样的名字。
虽然象x1，y1或fn这样的名字很简短，输入起来容易，但是我们难以知道它们代表什么、结果是什么含义，因而使编码难以理解、维护和改进。
4.数据对象命名中，使用“_”连接符。
如：
PRODUCT_TYPE_ID、pub_user。
5.采用该领域的术语。
如果用户称他们的“客户”（clients）为“顾客”（customers），那么就采用术语Customer来命名这个数据字段，而不用Client。
6.尽量少用缩写，但如果一定要使用，就要谨慎地使用。
这意味着应该保留一个标准缩写的列表，明智地从中选取，并且在使用时保持一致。
例如，想对
单词“number”采用缩写，那么可从nbr，no或者num中选取一个，说明一下采用了哪一个（具体是哪个倒无所谓），并且只使用这一种形式。
7.避免使用长名字（最好不超过15个字母）。
虽然PhysicalOrVirtualProductOrService看起来似乎是个不错的类名，但是这个名字太长了，应该考虑重新给它起个短一点的名字。
8.设计初首先对项目进行分析，设计子系统（或子模块的前缀），用于表、视图等名称的前缀。
如：
aut_权限管理
rep_报表管理
pub_公共模块
4.2.具体数据对象命名规范
数据对象
最大长度
命名规范
视图
30
子系统view_前缀_标题如果是缩写则全部大写，否则用小写view_pub_user
表
30
子系统前缀_标题如果是缩写则全部大写，否则用小写pub_user 用户表rep_daily_bill账单日报表
表列
30
1.列名必须用一个或多个能表示其意思的英语单词来表示其中列名中不可包括“_X_”字符串。
2.用户自定义定段N_X_00 （number）C_X_01 （char）
D_X_02 （date）
主键
18
PK_表名
外键
18
FK_表名_列名+被参照（referenced）表名（缩写）
索引
18
INDEX_表名_列名（缩写）
唯一索引
18
UINDEX_表名_列名（缩写）
check约束
18
CK_表名_列名（缩写）
序列
30
SEQ_表名_列名（缩写）
存储过程
30
PRO_子系统前缀_标题存储过程名必须用一个或多个能表示其意思的英语单词来表示，每个单词首字母为大写，其他字母为小写。
同时，前面要加前缀（PRO_）；例如：
PRO_pub_GetAllCustomer
函数
30
_子系统前缀_标题函数名必须用一个或多个能表示其意思的英语单词来表示，每个单词首字母为大写，其他字母为小写。
同时，前面要加前缀（fun_）；例如：
FUN_pub_ReturnCustomer
包
30
PCK_标题
包体
30
PCK_BODY_标题
触发器
30
TRIG_规则1_规则2_表名触发器名将在后边有进一步的说明
JOB
30
JOB_名称
4.3.数据类型
4.3.1.字符型
固定长度的字串类型采用char，长度不固定的字串类型采用varchar。
避免在长度不固定的情况下采用char类型。
如果在数据迁移等出现以上情况，则必须使用trim（）函数截去字串后的空格。
4.3.2.数字型
数字型字段尽量采用number类型，要注意精度。
4.3.3.日期和时间
4.3.3.1.系统时间
由数据库产生的系统时间首选数据库的日期型，如DATE类型。
4.3.3.2.外部时间
由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：
YYYYMMDDHH24MISS。
4.3.4.大字段
如无特别需要，避免使用大字段（blob，clob，long，text，image等）。
4.3.5.唯一键
对于数字型唯一键值，尽可能用系列sequence产生。
4.4. 触发器（Trigger）命名规范
1.Insert触发器命名规则
BEFORE型：
’TRIG_BE_IN_’+表名
AFTER型：
’TRIG_AF_IN_’+表名
2.Update触发器命名规则
BEFORE型：
’TRIG_BE_UP_’+表名
AFTER型：
’TRIG_AF_UP_’+表名
3.Delete触发器命名规则
BEFORE型：
’TRIG_BF_DE_’+表名
AFTER型：
’TRIG_AF_DE_’+表名
4.5. 代码缩进约定
列之间存在上下关系时，必须缩进4个空格（一个TAB）的间距；
一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进；
where子句书写时，换行时将and、or等写在换行首列，语句另起一行时，以保留字或者连接符开始，连接符右对齐；
例：
select/*+ORDEREDUSE_NL（ip）*/
u.namepnd_index_owner,
idx_namepnd_index_name,
ixp_namepnd_partition_name,
pnd_rowid,
pnd_timestamp
fromdr$pending,dr$indexi,dr$index_partitionp,sys.user$u
whereidx_owner#=u.user#
andidx_id=ixp_idx_id
andpnd_pid=ixp_id
andpnd_pid!
=0
andpnd_cid=idx_id
4.6. 函数（存储过程）头头注释
编写函数文本–如视图、函数、触发器、存储过程以及其他数据对象时，必须为每个函数增加适当注释。
该注释以多行注释为主，主要结构如下：
/************************************************************
*name:
–函数名
*function:
–函数功能
*input:
–输入参数
*output:
–输出参数
*author:
–作者
*CreatedDate:
–创建时间
*UpdateDate:
–函数更改信息（包括作者、时间、更改内容等）–（更改记录多行，以最新记录在最前面）（基本成型时写更改信息）
************************************************************/
4.7. 函数（存储过程）体内编写规范
当在函数体内有条件判断语法、循环判断语法、异常判断语法时，必须被BEGIN和END语法包含，例如：
IF<条件表达式>
BEGIN
<命令行或程序块>
END
ELSIF<条件表达式>
BEGIN
<命令行或程序块>
END
4.8. 数据字典书写格式
例：
表名：
sms_CP_info
字段名称
数据类型
空值
缺省值
规则
说明
CP_ID
Integer（4,0）
N
CP标识
CP_NAME
Varchar2（200）
N
CP名称
CP_CODE
Varchar2（40）
N
企业编码
4.9. E-R图的绘制模式
4.10.数据库后期维护的要求
1.当数据库表或程序需要改动的时候，请务必做好文档的相关修改工作;
2.当数据库表或程序需要改动的时候，请通知数据库管理人员完善数据库文档;
3.数据变更要做好备份工作；
4.要充分发挥好测试数据库的作用。
5.设计过程规范
5.1.数据分析阶段
在数据分析阶段（一般在项目的需求分析或者系统设计阶段进行），应注意搜集和分析数据相关的内容，并形成相关成果物，包括数据流图和数据字典等，以此作为数据库设计的基础和依据。
数据流图从数据传递和加工的角度，以图形的方式刻画数据流从输入到输出的移动变换过程。
数据字典则对数据流图中的各种成分进行详细说明，作为数据流图的细节补充。
数据字典一般应包括对数据项，数据结构、数据存储和数据处理的说明。
数据流图示例：
5.2.概念设计阶段
在数据分析的基础上，使用E-R模型技术，将现实世界中的客观对象抽象为实体和关系，形成概念数据模型（CDM）。
CDM可以从更高层次地理解系统、以及技术人员可用于和用户交流，和用户达成共识，所以必须完成这一阶段的工作。
CDM示例：
5.3.逻辑设计阶段
将E-R模型转换DBMS支持的数据模型，包括关系模型、网状模型、层次模型、对象模型等等。
常用的DBMS是关系数据库，因此要转换为关系模型
可以用SybasePowerDesigner直接将CDM转换为LDM。
从理论上来说，转换过程一般有7个步骤：
转换强实体
转换弱实体
转换1：
1关系
转换1：
N关系
转换M：
N关系
转换多值属性（Multi-ValuedAttribute）
转换n元关系（n-aryRelation）
E-R模型和关系模型的映射如下：
E-R模型
关系模型
实体类型
关系
1：
1或1：
N关系类型
外键
M：
N关系类型
两个外键
n元关系类型
n个外键
简单属性
属性
复合属性
简单属性的集合
多值属性
关系和外键
关键属性
主键/唯一键
5.4.物理设计阶段
基于给定的DBMS建立面向计算机物理表示的模型，描述了数据在储存介质上的组织结构，它不但与具体的DBMS有关，而且还与操作系统和硬件有关。
方法和过程
可以用SybasePowerDesigner直接将CDM/LDM转换为PDM。
对于关系模型来说，进行物理数据库设计主要包括：
使用逻辑模型建立一系列的表；
使用索引以提升性能；
实施约束和安全限制；
对数据进行分区和分布式处理等。
6.SQL编写
6.1.1.字符类型数据
SQL中的字符类型数据应该统一使用单引号。
特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。
利用trim（）,lower（）等函数格式化匹配条件。
6.1.2.复杂sql
对于非常复杂的sql（特别是有多层嵌套，带子句或相关查询的），应该先考虑是否设计不当引起的。
对于一些复杂SQL可以考虑使用程序实现。
USER_TAB_COMMENTS数据字典
Commenton可加注解
6.1.3.高效性
6.1.3.1.避免In子句
使用In或notIn子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。
可以采用连接查询或外连接查询来提高性能。
Char比varchar查询时高效。
在进行查询及建立索引时，char比varchar的效率要高，当然varchar在存储上比char要好。
6.1.3.2.避免嵌套的Select子句
这个实际上是In子句的特例。
6.1.3.3.避免使用Select*语句
如果不是必要取出所有数据，不要用*来代替，应给出字段列表，注：
不含selectcount（*）。
6.1.3.4.避免不必要的排序
不必要的数据排序大大的降低系统性能。
6.1.4.健壮性
6.1.4.1.Insert语句
使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。
6.1.4.2.Count（*）、Count（id）、count（distinctid）的区别
Selectcount（*）fromtesttab
得到表testtab的记录数
selectcount（id）fromtesttab
得到表testtabid字段非空记录数
selectcount（distinctid）fromtesttab
得到表testtabid字段值非相同记录数
6.1.4.3.Notnull为字段类型性质的约束
本约束功能在后期无语法使期失效，可使用修改字段类型方式。
altertablemodify字段名类型notnull
altertablemodify字段名类型
6.1.4.4.外键值可用null的问题
外键列如没有明确说明notnull，可插入null记录（而null是在外部表的记录中没有的），如无可插null记录的想法，要对外键字段加notnull约束。
6.1.4.5.序列sequence跳号的问题
sequence因回滚，系统崩溃（使用cache内的值将认为已用），多表引用都将使其跳号，所以不能用于为连续序号utl_row.cast_to_row
6.1.4.6.unicn\intersect\minus使用ordeyby的注意事项
以上语句进行连表操作，而表同表的字段顺序的类型相同但字段标题名可不同，使用ordeyby时后面如果是字段名，要求所有的表的字段标题名相同，否则用字段的顺序号
selectid,name,yearfromuser1
union
selectno,name,to_number（null）yearfromuser2
orderby1,name,year
CNOUG博客首页`*A'H*CYY2c3O
6.1.5.安全性
无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失。
如果不确定，最好先用Select语句带上相同条件来验证一下结果集，来检验条件是否正确。
6.1.6.完整性
有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数据，或则按照某种业务规则转移该数据。
9I中表中字段缩小及变类型，字段为空或表空，varchar和char长度不变可任意改，字段名和表名可字段可用ALTERTABLEtableSETUNUSED（column）设定为不可用，注意无命令再设为可用。
7.特殊说明
7.1.编码
我们遵循的标准是，数据库，表，字段和页面或文本的编码要统一起来很多mysql数据库工具（除了phpmyadmin，我偶尔用，功能强速度慢）都不支持创建时指定数据库编码，当然可以改my.ini来解决这个问题,但是需要重新启动mysql，不过用下面的语句会更有效。
目前我们所有平台使用的编码统一都是UTF-8编码。
创建表的时候默认为UTF-8编码：
DEFAULTCHARSET=utf8。
创建的时候指定所有的编码为UTF-8。
通过Java链接数据库设置的编码也为：
utf-8。
即如下：
useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true。
通过这个参数来指定。
7.2.注释
数据库中所有的表名，以及字段、视图、索引、触发器、主键、外键等都必须加注释。
1、给表名加注释如下：
COMMENT='文章分类'
2、给表中字段加注释
`name`varchar（255）NOTNULLCOMMENT'名称'
红色即为字段的注释。
7.3.引擎
目前所用mysql数据库引擎都是InnoDB。
在创建数据库的时候指定数据库编码为InnoDB,创建表的时候指定编码也为UTF-8.通过语句指定：
ENGINE=InnoDB。
升级会员 