数据库设计规范2.docx
- 文档编号:29312534
- 上传时间:2023-07-22
- 格式:DOCX
- 页数:28
- 大小:29.69KB
数据库设计规范2.docx
《数据库设计规范2.docx》由会员分享,可在线阅读,更多相关《数据库设计规范2.docx(28页珍藏版)》请在冰豆网上搜索。
数据库设计规范2
数据库设计规范
项目名称:
文档作者:
创建日期:
2012-07-16
确认日期:
2012-08-16
当前版本:
V1.0
修订历史记录
日期
版本
说明
作者
目录
1引言5
1.1目的5
1.2范围5
2术语5
3命名符号6
4设计文件内容6
5设计原则6
5.1模式SCHEMA6
5.2表空间6
5.3表6
5.4域DOMAIN7
5.5主键8
5.6字段8
5.7注释9
5.8其他9
6对象命名规范9
6.1数据库及模式命名规范10
6.2表空间命名规范10
6.3表命名规范10
6.4视图命名规范12
6.5索引命名规范12
6.6CHECK命名规范13
6.7字段命名规范13
6.8序列号命名规范13
6.9键命名规范13
6.10存储过程命名规范13
6.11函数命名规范14
7数据库编程规范14
7.1书写规范14
7.2注释规范14
7.3语法规范15
7.4SQL性能规范15
7.4.1批量操作,避免频繁使用commit15
7.4.2避免不必要的排序15
7.4.3用WHERE子句替换HAVING子句16
7.4.4用“>=”替代“>”16
7.4.5删除表中所有记录时用TRUNCATE替代DELETE16
7.4.6用UNIONALL代替UNION16
7.4.7用(NOT)EXISTS替代(NOT)IN。
16
7.4.8使用DECODE函数来减少处理时间。
17
7.4.9使用表的别名(Alias)。
17
7.4.10尽量减少对表的查询次数。
17
7.4.11用表连接替换EXISTS。
18
7.4.12避免使用DISTINCT,可以用EXISTS替换DISTINCT。
18
7.4.13避免使用耗费资源的操作19
7.4.14避免对索引列使用数据库函数、计算表达式等等19
7.4.15在查询条件中,避免不必要的类型转换。
19
7.4.16尽量避免字段与“NULL”比较19
7.4.17在索引列上使用<>(!
=)和like将不会使用索引。
19
7.4.18用Case语句合并多重扫描20
7.5JOB使用规范20
附录20
数据模型数据表填充颜色规范20
常用词列表21
1引言
1.1目的
为数据库设计人员提供数据库设计的规范和约定。
1.2范围
本文档主要描述数据库设计所出的设计文档、设计原则及数据库对象的命名约定。
2术语
1)LDM(LogicalDataModel,逻辑数据模型):
针对一种数据库管理系统(如关系数据库管理系统)的数据模型,powerdesigner设计出的模型文件扩展名为.ldm
2)PDM(PhysicalDataModel,物理数据模型):
针对一种具体的数据库管理系统(如oracle数据库管理系统)的数据模型,powerdesigner设计出的模型文件扩展名为.pdm
3)数据库3范式:
第一范式(1NF)无重复的列、第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]、第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]。
4)基础数据表:
描述业务实体及其属性列表以及存储与系统操作、业务控制有关的参数等数据表统称为基础数据表。
一般情况下,基础数据表建立完成后相对稳定,不变化或者变化频率很低。
5)公共基础数据表:
供系统多个模块共用的基础数据表称为公共基础数据表。
6)模块基础数据表:
仅供某个模块使用的基础数据表称为模块基础数据表。
7)业务数据表:
记录业务发生的过程和结果以及对业务的统计等数据表统称为业务数据表,一般情况下,业务数据表的数据量随着时间的推移而递增,变化频率跟业务性质、需求有关。
8)关系表:
用于记录各数据表之间关系的表。
9)历史数据表:
用于转储历史数据的表,对于数据量大的数据表,考虑到性能和操作的便捷性,需要将不用或很少使用的历史数据进行转储。
3命名符号
在命名中用到的符号:
中括号[]:
根据实际情况,中括号[]内定义的东西有就填上,没有就不填
尖括号<>:
尖括号<>内的内容必须有
4设计文件内容
数据库设计完成后,应形成并提交以下设计文件:
1)原始设计文件
数据库设计文件:
<项目名>数据库设计--子系统名.doc
物理数据模型:
项目名--子系统名.pdm
2)脚本文件
建库文件:
1_create_db.sql
建用户文件:
2_create_user.sql
建序列号文件:
3_create_sequence.sql
建序表文件:
4<建子系统表的序号>_create_table_<子系统名>.sql
建序视图文件:
5_create_view.sql
建其他对象文件:
6_create_other.sql,包括存储过程、触发器、函数等对象
系统初始化数据文件7<初始化数据的序号>_init_data[_初始化数据分类].sql
3)其他文件
5设计原则
数据库设计除特殊需要,一般要满足数据库3范式。
5.1模式SCHEMA
如果业务系统是多个应用系统,那必须为每个应用系统在数据库建立一个模式,即一个应用系统在一个数据中只能有一个模式,模式名=应用系统名。
5.2表空间
1)对于数据量非常大的表,使用单独的表空间。
2)索引使用单独的表空间。
5.3表
●业务数据表
业务数据表必须字段(都不能为空):
字段名
字段编码
类型
说明
主键
SID
VARCHAR(32)
建立时间
CREATE_TIME
TIMESTAMP
汇总表不需要;
默认为数据库当前时间
最后修改时间
LAST_MODI_TIME
TIMESTAMP
汇总表不需要;
第一次插入记录时使用记录插入时间,以后每次修改时更新为修改的时间(用数据库当前时间)
5.4域DOMAIN
定义系统自己的数据类型:
对系统中多张表都会用到的数据类型用域domain进行定义;
域定义:
域名
类型
约束
描述
业务表代理主键SID
VARCHAR(32)
编码
VARCHAR(32)
名称
VARCHAR(100)
简称
VARCHAR(40)
长名称
VARCHAR(256)
描述1024
VARCHAR(512)
长描述2048
VARCHAR(2048)
备注1024
VARCHAR(1024)
生效状态
SMALLINT
值只能是0或1
0:
失效;1:
生效
生效时间
TIMESTAMP
记录插入时间
失效时间
TIMESTAMP
缺省2999-12-3100:
00:
00
是否
SMALLINT
值只能是0或1
0:
否;1:
是
建立时间
TIMESTAMP
缺省值:
数据库当前时间
第一次插入记录后不再更新
最后修改时间
TIMESTAMP
缺省值:
数据库当前时间
以后每次修改记录,这个时间要跟着更新
YYYY-MM-DD
CHAR(10)
YYYY
CHAR(4)
4位年
MM
CHAR
(2)
2位月
DD
CHAR
(2)
2位日
HH24:
MM:
SS
CHAR(8)
24小时制的时间点:
时分秒
5.5主键
表分类
代理主键
候选键
业务数据表
主键SID,VARCHAR(32)
根据实际情况
5.6字段
1)尽量使用Domain来定义字段类型,统一使用。
2)固定长度的字串类型采用Char,长度不固定的字串类型采用Varchar2。
避免在长度不固定的情况下采用Char类型,避免使用Varchar。
3)如无特别需要,避免使用大字段(blob,clob,long,text,image等)。
项目数据表常用字段说明:
字段
类型
约束
描述
基础数据表代理主键
SID
VARCHAR(32)
业务表代理主键
SID
VARCHAR(32)
(保存历史信息的基础数据表)编号
ID
VARCHAR(32)
与下面的历史版本号组合成唯一键
(保存历史信息的基础数据表)历史版本号
VERSION
INTEGER
从1开始递增
编码
CODE
VARCHAR(32)
名称
NAME
VARCHAR(100)
可根据需要调整类型
简称
SHORT_NAME
VARCHAR(40)
可根据需要调整类型
别名
ALIAS_NAME
VARCHAR(100)
可根据需要调整类型
备注
REMARK
VARCHAR(1024)
生效状态
STATUS
SMALLINT
值只能是0或1
0:
失效;1:
生效
生效时间
EFF_TIME
TIMESTAMP
记录插入时间
失效时间
EXP_TIME
TIMESTAMP
缺省2999-12-3100:
00:
00
建立时间
CREATE_TIME
TIMESTAMP
缺省值:
数据库当前时间
第一次插入记录后不再更新
最后修改时间
LAST_MODI_TIME
TIMESTAMP
缺省值:
数据库当前时间
以后每次修改记录,这个时间要跟着更新
建立人
CREATE_MAN
VARCHAR(40)
记录建立的用户名称
最后修改人
LAST_MODI_MAN
VARCHAR(40)
最后修改此条记录的用户名称
5.7注释
1)对于采用check值类型的字段,在设计时要给出明确的注释;用字段comment与模型note两种方式注释。
2)对于有些字段内容具有一定的规则的数据,也需要说明数据的格式;用模型note方式注释。
3)主键采用序列对象生成的字段,也要说明由哪个序列对象生成;用模型note方式注释。
5.8其他
1)尽量避免使用触发器;
2)尽量避免使用多对多关系;
6对象命名规范
1)对象名称必须以A-Z的26各字母开头,名称只能包含A-Z的26个字母、0-9的十个数字、下划线_,不能包含其他字符。
2)对象名称一律用大写字母。
3)对象命名不要超过30个字符。
4)所有对象命名时,尽量用有意义的英文单词;如果单词过长可以缩写,但一定要采用标准的缩写,如果没有标准的缩写,可以使用前四个字母,实在找不到匹配的英文单词,就用汉语拼音的第一个字母拼在一起,如你好:
NH。
如果表或者是字段的名称仅有一个单词,那么建议不使用缩写,而是用完整的单词;
6.1数据库及模式命名规范
系统数据库命名为:
EXP,数据库不分多个模式,统一使用同一模式,命名为MODE1。
6.2表空间命名规范
由TBS_<单词>组成,单词根据存储的特性命名。
表空间的数据文件名称由表空间名+nn组成,n为0,1,2,3,4…等
6.3表命名规范
1)表名统一采用单数表示,如用SYS_USER表示,而不用SYS_USERES表示;
2)表名统一用大写,各单词之间用下划线_隔开;
3)基本上分为五部分,以划线“_”连接:
✓第一部分:
按子系统(业务域)
如果表不属于任一子系统(业务域),则此部分可不需要;如果子系统(业务域)下还需区分更细粒度的模块、子模块,可再细分,如系统管理功能资源表:
SYS_FUN_RES。
子系统(业务域)_模块_子模块…
命名
备注
数据集成(DataIntegration)
DI
应用集成(EnterpriseApplicationIntegration)
EAI
卡片管理(CardManagement)
CM
适用于卡片初始化、发卡、挂失解挂、换卡补卡、销户、充值、取现、密码修改等
值班管理(OnDutyManagement)
ODM
适用于行政值班管理
信息服务(InformationService)
IM
物业管理(EstateManagement)
ESM
就餐管理(DinnerManagement)
DIM
借阅管理(LoanManagement)
LM
图书借阅管理用LM_BOOK;档案借阅管理用LM_ARCH
考勤管理(AttendanceManagement)
AM
居民论坛(ResidentsForum)
RF
访客管理(VisitorManagement)
VM
经济运行(EconomicOperation)
EO
能源管理(EnergyManagement)
ENM
设备管理(EquipmentManagement)
EQM
智能会议系统(IntelligentMeetingSystem)
IMS
消息管理(MessageManagement)
MSG
消息中心、消息组件
系统管理(SystemManagement)
SYS
系统运维、其它(个人网站链接、系统链接)
工作流组件(WorkFlowComponent)
WF
短信中心(ShortMessageCenter)
SMC
主要用于与手机等无线手持设备进行信息互通
其他
OTH
✓第二部分:
按数据分类命名
表的分类
命名
简单分类表
C
[子系统(业务域)_]C
基础信息表
B
[子系统(业务域)_]B
业务表
S
[子系统(业务域)_]S
✓第三部分:
按表名称
指示表中存储的目标对象的名称或其简写名,要求命名尽量采用标准的翻译,能准确地表达该表的中文含义,能根据英文猜测到表的用途,尽量少使用划线“_”连接。
✓第四部分:
按特殊用途
对于一些特定作用的表,除了遵循普通表命名规范外,增加特定后缀表示。
特殊用途
命名
报表数据
R
[子系统(业务域)_]<数据分类_><表名称_>R
汇总数据
S
[子系统(业务域)_]<数据分类_><表名称_>S
历史数据
H
[子系统(业务域)_]<数据分类_><表名称_>H
临时数据
T
[子系统(业务域)_]<数据分类_><表名称_>T
关系数据
M
[子系统(业务域)_]<数据分类_><表名称_>M
若实际出现其他情况,再行补充。
✓第五部分:
按数据时间粒度:
该部分仅针对于汇总数据表,指示表中数据存储的时间粒度,若表中数据无时间粒度或存在多时间粒度数据,则可省略该部分。
时间粒度
命名
年
Y
[子系统(业务域)_]<数据分类_><表名称_>S_Y
半年
HY
[子系统(业务域)_]<数据分类_><表名称_>S_HY
季度
Q
[子系统(业务域)_]<数据分类_><表名称_>S_Q
月度
M
[子系统(业务域)_]<数据分类_><表名称_>S_M
旬
X
[子系统(业务域)_]<数据分类_><表名称_>S_X
周
W
[子系统(业务域)_]<数据分类_><表名称_>S_W
日
D
[子系统(业务域)_]<数据分类_><表名称_>S_D
如果实际出现其他情况,再行补充。
6.4视图命名规范
视图命名必须以V_开头,其他遵循表命名规则。
6.5索引命名规范
【规则1】:
IDX_<表名或表名缩写>_<字段名或字段名缩写>;
【规则2】:
若是复合索引,字段名或缩写至少要包含复合索引的头两个字段名或缩写
【规则3】:
若是主键索引,以“PKX”为索引前缀;
【规则4】:
给表增加主键时要显式指定约束的名字,并且要指定usingindextablespace参数。
例:
altertableSYS_USER
addconstraintPKX_SYS_USER_SID--主键约束名,也是索引名
primarykey(SID)
usingindextablespace…;
不能写成
altertableSYS_USER
addprimarykey(SID);
如果不显式指定约束名系统将随机分配一个约束名字(同时也是主键索引的名字)
如果不指定usingindextablespace参数,索引会建在该用户默认的表空间上,不利于数据库性能和系统维护。
6.6CHECK命名规范
Checkconstraint规则:
CHK_<表名或表名缩写>_<字段名或字段名缩写>
6.7字段命名规范
字段名采用有意义的英文单词表示,要求字段名能表达字段的含义,如果相同字段在不同的表中出现,要使用相同的命名,且必须保证他们的类型和长度是相同的,单词之间用下划线_连接。
下面列出常用字段命名及类型:
字段
字段英文名
字段类型
主键
SID
VARCHAR(32)
编号
ID
VARCHAR(32)
编码
CODE
VARCHAR(32)
名称
NAME
VARCHAR(256)
备注
REMARK
VARCHAR(1024)
描述/说明
REF_DESC
VARCHAR(n)
生效状态
STATUS
SMALLINT
生效时间
EFF_TIME
TIMESTAMP
失效时间
EXP_TIME
TIMESTAMP
建立时间
CREATE_TIME
TIMESTAMP
最后修改时间
LAST_MODI_TIME
TIMESTAMP
6.8序列号命名规范
规则:
SEQ_[<子系统(业务域)>]_<序列名单词>
6.9键命名规范
主键规则:
PK_<表名或表名缩写>
候选主键规则:
AK_<表名或表名缩写>
外键规则:
FK_<子表名或子表名缩写>_REF_<父字段名或父子段名缩写>
6.10存储过程命名规范
规则:
以“SP_”打头,其他规范遵循普通表的命名规范,要求名字能表达存储过程的用途。
6.11函数命名规范
规则:
以“FN_”打头,其他规范遵循普通表的命名规范,要求名字能表达函数的用途。
7数据库编程规范
7.1书写规范
【规则1】:
所有代码统一使用大写字母书写;
【规则2】:
确保变量和参数在类型和长度上与表数据列类型和长度相匹配;
说明:
如果与表数据列宽度不匹配,则当较宽或较大的数据传进来时会产生运行异常。
【规则3】:
参数和变量的命名符合如下规范:
1、传入参数以“i_”为前缀;
2、传出参数以“o_”为前缀;
3、变量以“v_”为前缀
【规则4】:
程序块中的begin、end独立成行;
【规则5】:
程序块采用缩进风格书写,保证代码清晰易读,风格一致,缩进格数统一;
【规则6】:
不允许把多个语句写在一行中,即一行只写一条语句;
【规则7】:
同一条语句占用多于一行时,每行的第一个关键字应当左对齐;
【规则8】:
对于Insert…values和update语句,一行写一个字段,字段后面紧跟注释(注释语句左对齐),values和insert左对齐,左括号和右括号与insert、values左对齐;
【规则9】:
相对独立的程序块之间需加空行;
【规则10】:
对于长表达式应在低优先级操作符处换行,操作符或关键字放在新行之首。
7.2注释规范
【规则1】:
所有变量定义都要加上注释,说明变量的用途及含义;
【规则2】:
注释内容要清晰、明了,含义准确,防止注释二义性;
【规则3】:
对存储过程的任何修改,都需要在注释最后添加修改人、修改日期及修改原因等信息;
【规则4】:
对程序分支必须书写注释;
【规则5】:
在代码的功能、意图层次上进行注释,帮助维护人员理解代码;
【规则6】:
代码注释应放在描述的代码上方或右方相近位置,不可放在下面;
【规则7】:
注释与所描述的内容进行同样的缩排;
【规则8】:
函数应对返回的代码进行详细描述;
【规则9】:
在程序块的结束行右方加注释,以表示程序块结束;
【规则10】:
统一文件头的注释,包括功能描述、参数描述、修改历史等信息;
7.3语法规范
【规则1】:
存储过程的In、out参数应按类别分开书写,不要交叉;
【规则2】:
存储过程中变量的声明应集中在存储过程名称下面;
【规则3】:
确保所有的变量和参数都用到,没有用到的变量和参数要删除;
【规则4】:
存储过程有多个分支返回时,若有事务控制,需确保各个分支都结束事务;
异常时,应该在Exception中捕捉异常,并进行事务处理。
【规则5】:
存储过程:
不要在异常部分,进行正常的业务处理;
【规则6】:
原则上不要使用动态sql,如果必须使用,需绑定变量;
【规则7】:
捕捉到异常后需要rollback回滚事务。
7.4SQL性能规范
7.4.1批量操作,避免频繁使用commit
频繁的COMMIT会导致物理I/O增大,但长时间不提交将带来更多的性能问题。
建议小于3秒的事务可以一次提交,大于3秒的操作尽可能3秒左右提交一次。
实际应用中使用COMMIT时必须保证事务的完整性。
7.4.2避免不必要的排序
对查询结果进行排序会大大的降低系统的性能。
7.4.3用WHERE子句替换HAVING子句
例如:
SELECTNAME,SUM(AGE)
FROMEMPLOYEE
GROUPBYNAMEHAVINGNAME!
=‘ABC’
修改为以下语句效果更好
SELECTNAME,SUM(AGE)
FROMEMPLOYEE
WHERENAME!
=‘ABC’GROUPBYNAME
7.4.4用“>=”替代“>”
如:
在SID列上建有索引,则语句SELECT*FROMEMPLOYEEWHERESID>=9要比语句SELECT*FROMEMPLOYEEWHERESID>8高效。
这是由于前者DBMS将直接跳到第一个SID等于9的记录,而后者将首先定位到8的记录并且向前扫描到第一个SID大于9的记录。
7.4.5删除表中所有记录时用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下,回滚段用来存放可以被恢复的信息,如果你没有COMMIT事务,DB2可以将数据恢复到删除之前的状态;而当运用TRUNCATE时,回滚段不存放任何可被用于恢复的信息,当命令运行后,数据不能被恢复,因此很少的资源被调用,执行时间也会很短,空间立即释放,detele操作后的空间可以被重新利用,但不会释放。
DB2V97及以上版本支持TRUNCATE语法。
7.4.6用UNIONALL代替UNION
说明:
UNIONALL不过虑重复记录,UNION过滤重复记录,所以需要先排序。
如果不需要过滤重复的记录,UNIONALL比UNION性能更好。
7.4.7用(NOT)EXISTS替代(NOT)IN。
在许多基于驱动表的查询中,为了满足一个条件,往往需要对
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 设计规范