TMN0704数据库设计规范.docx
《TMN0704数据库设计规范.docx》由会员分享，可在线阅读，更多相关《TMN0704数据库设计规范.docx（15页珍藏版）》请在冰点文库上搜索。
TMN0704数据库设计规范
数据库设计规范
文件状态
[√]草稿文件
[]正式文件
[]更改正式文件
文件标识：
当前版本：
V1.0
作者：
审核：
完成日期：
版本历史
版本/状态
作者
参与者
起止日期
备注
目录
1编写目的6
2数据库类型6
3设计方式6
4数据库设计6
4.1范式6
4.2索引6
4.3主键6
4.4外键7
4.5NULL值7
4.6注释7
4.7移植性7
5命名规范7
5.1命名前缀7
5.1.1表7
5.1.2字段8
5.1.3主键8
5.1.4索引8
5.1.5外键8
5.1.6视图8
5.1.7存储过程8
5.1.8触发器8
5.1.9函数8
5.1.10包9
5.1.11序列9
5.2命名9
5.2.1语言9
5.2.2大小写9
5.2.3单词分隔9
5.2.4保留字9
5.2.5命名长度10
5.2.6字段名称10
6数据类型10
6.1日期和时间10
6.2大字段10
6.3字符型10
6.4Identity和sequence10
6.5可移植数据类型11
7SQL编写规范11
7.1字符类型数据11
7.2特有函数11
7.3复杂sql11
7.4高效性11
7.4.1避免In子句11
7.4.2避免Select子句12
7.4.3避免使用Select*语句12
7.4.4Select语句中对字段使用别名12
7.5健壮性12
7.5.1Insert语句12
7.6安全性12
7.6.1Where条件12
7.7易理解性12
7.8脚本导入13
1编写目的
为了统一各个软件项目和产品的数据库设计方法和命名规范等，特指定数据库设计规范。
2数据库类型
本规范适用于不同的数据库类型,如Oracle,SQLServer等。
3数据库设计
3.1范式
如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3NF（第三范式）.
如非确实必要，避免一个字段中存储多个标志的做法。
如11101表示5个标志的一种取值。
这往往是增加复杂度，降低性能的地方。
3.2索引
对于查询中需要作为查询条件的字段，可以考虑建立索引。
最终根据性能的需要决定是否建立索引。
3.3主键
原则上要求每个表都建立主键，数据类型为varchar（40），内容为40位UUID。
3.4外键
对于关联两个表的字段，一般应该分别建立主键、外键。
实际是否建立外键，根据对数据完整性的要求决定。
3.5NULL值
对于字段能否null，应该在设计中明确指明，不应使用缺省。
3.6注释
表、字段等应该有中文名称注释，以及需要说明的内容
3.7移植性
如无特别需要，尽量避免使用某一种数据库特有的功能，如oracle的trigger有before,after之分，但其他数据库没有，应该避免使用Oracle特有的功能。
避免使用存储过程，因为存储过程难以移植。
如果程序中与存储过程中实现同样功能性能相近或者程序实现性能稍弱，首选程序实现。
如果性能确实非常重要，可以使用存储过程。
尽量使用标准SQL，而不使用DBMS本身完善后的SQL语句，因为标准SQL是流行DBMS都支持的，完善后，增加的部分往是其他DBMS中所没有的，比如SQLSERVER中的SELECT语句中的TOP关键字。
4命名规范
4.1命名前缀
4.1.1表
某一种业务的或者相关的表可以采用业务前缀（或者按模块划分），如网关使用的表可以加GW_。
业务前缀在项目设计开始的时候确定。
前缀长度应限定在三个字符以内（不含下划线）。
同一schema的表需要保证去掉前缀之后不重复。
4.1.2字段
一般不需要前缀。
4.1.3主键
PK_。
主键名称应是前缀+表名+构成的字段名。
如果复合主键的构成字段较多，则只包含第一个字段。
表名可以去掉前缀。
4.1.4索引
IDX_。
索引名称应是前缀+表名+序号。
4.1.5外键
FK_。
外键名称应是前缀+外键表名+主键表名+外键表构成的字段名。
表名可以去掉前缀。
4.1.6视图
V_。
建议使用业务前缀。
4.1.7存储过程
P_。
建议使用业务前缀。
4.1.8触发器
TR_。
触发器名应是前缀+表名+触发器名。
4.1.9函数
F_。
4.1.10包
PKG_。
4.1.11序列
SEQ_。
4.2命名
4.2.1语言
命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。
命名不允许使用中文或者特殊字符。
英文单词使用用对象本身意义相对或相近的单词，有多个时，最好选择简单的那个。
不能使用毫不相干的单词来命名。
当一个单词不能表达对象含义时，如果用词组就用词组，否则就用多个单词组合，当组合太长时，如果有简写就用简写，如果没有就采用缩写，缩写要基本能表达原单词的意义，不能随意挑。
当出现对象名重名时，是不同类型对象时，加个类型前缀或后缀，加以区别。
4.2.2大小写
名称一律大写，以方便不同数据库移植（sybase区分大小写），以及避免程序调用问题（Oracle会自动内部转换成大写）
4.2.3单词分隔
命名的各单词之间必须使用下划线进行分隔。
4.2.4保留字
命名不允许使用SQL保留字，如count。
4.2.5命名长度
表名、字段名、视图名长度应限制在20个字符内（含前缀）。
待查证各种数据库对命名标识符长度支持。
4.2.6字段名称
同一个字段名在一个数据库中只能代表一个意思。
比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。
不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。
5数据类型
5.1日期和时间
日期和时间首选数据库的日期型，除非特别原因，不应选择字符型存储，以避免繁琐的转换、运算，也避免非法日期、时间造成的影响，同时便于移植（各种数据库的转换函数是不一样的）
如果需要使用字符形式的日期数据，使用如下格式：
2003-08-1108:
11:
07
5.2大字段
如无特别需要，避免使用大字段（blob，clob，long，text，image等）。
5.3字符型
对于长度可变的文字，应使用varchar而不是较长的char，避免补齐的问题。
对于varchar，应注意长度问题，尽量限制在255字符内，以避免移植问题。
5.4Identity和sequence
各表的主键ID使用Hibernate提供的方法来产生UUID字符串；对于oracle的sequence酌情使用，除非性能上的必须，建议优先考虑程序来创建唯一标志。
5.5可移植数据类型
为了移植性，应尽量只使用以下数据类型
DOUBLEPRECISION,FLOAT,INTEGER,NUMERIC,REAL,SMALLINT,VARCHAR2,CHAR
对于标志类型的数据，统一使用INTEGER。
6SQL编写规范
6.1字符类型数据
SQL中的字符类型数据应该统一使用单引号，不使用双引号，以避免移植问题。
6.2特有函数
避免使用某种数据库特有的函数。
6.3复杂sql
对于非常复杂的sql（特别是有多层嵌套，带子句或相关查询的），应该先考虑是否设计不当引起的。
对于一些复杂SQL可以考虑使用程序实现。
6.4高效性
6.4.1避免In子句
使用In子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。
6.4.2避免Select子句
这个实际上是In子句的特例
6.4.3避免使用Select*语句
如果不是真的必要取出所有数据，不要用*来代替，应给出字段列表来。
6.4.4Select语句中对字段使用别名
特别是对多表的查询语句，这样可以避免同名字段带来的麻烦。
6.5健壮性
6.5.1Insert语句
使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。
6.6安全性
6.6.1Where条件
无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失.如果不确定，最好先用Select语句带上相同条件来果一下结果集，来检验条件是否正确.
6.7易理解性
SQL语句书写风格如下：
句法一:
SELECT字段名1as别名1，字段名2as别名2…
FROM表1，表2，…
WHERE条件
句法二:
INSERTINTO表名（字段名1，字段名2…）
VALUES（值1，值2...）
WHERE条件
句法三:
DELETE*
FROM表名
WHERE条件
句法四:
UPDATE表名
SET字段名=值
WHERE条件
7SQL优化规则（Oracle）
7.1索引的使用原则
7.1.1尽量避免对索引列进行计算
●例：
XWHEREsal*1.1>950
OWHEREsal>950/1.1
XWHERESUBSTR（name,1,7）=’CAPITAL’
OWHEREnameLIKE‘CAPITAL%’
7.1.2尽量注意比较值与索引列数据类型的一致性
●例：
emp_no：
NUMBER型
OWHEREemp_no=123（好）
WHEREemp_no=’123’（也可）
emp_type：
CHAR型
XWHEREemp_type=123（此时，查询时，不利用索引列）
OWHEREemp_type=’123’
7.1.3尽量避免使用NULL
●例：
XWHEREcommISNOTNULL
XWHEREcommISNULL
OWHEREcomm>=0
7.1.4尽量避免使用NOT=（!
=）
●例：
XWHEREdeptno!
=0
OWHEREdeptno>0
7.1.5对于复合索引，SQL语句必须使用主索引列
●例：
复合索引（deptno,job）
OWHEREdeptno=20ANDjob=’MANAGER’
OWHEREdeptno=20
OWHEREjob=’MANAGER’ANDdeptno=20
XWHEREjob=’MANAGER’
7.1.6ORDERBY子句
O子句中，列的顺序与索引列的顺序一致。
O子句中，列应为非空列。
7.1.7查询列与索引列次序（WHERE）的一致性
OSELECTempno,jobFROMempWHEREempno<100ANDjob=’MANAGER’;
7.2其他
7.2.1语句书写要规范
尽量避免相同语句由于书写格式的不同，而导致多次语法分析。
7.2.2尽量少用嵌套查询
7.2.3使用表的别名
多表连接时，使用表的别名来引用列。
●例：
XSELECTabc002,abd003
FROMab001,ab020
WHEREab001.col2=ab020.col3
......
OSELECTt1.abc002,t2.abd003
FROMab001t1,ab020t2
WHEREt1.col2=t2.col3
......
7.2.4用NOTEXISTS代替NOTIN
●例：
XSELECT......
FROMemp
WHEREdept_noNOTIN（SELECTdept_no
FROMdept
WHEREdept_cat=’A’）;
OSELECT......
FROMempe
WHERENOTEXISTS（SELECT‘X’
FROMdept
WHEREdept_no=e.dept_no
ANDdept_cat=’A’）;
7.2.5用多表连接代替EXISTS子句
●例：
XSELECT......
FROMemp
WHEREEXISTS（SELECT‘X’
FROMdept
WHEREdept_no=e.dept_no
ANDdept_cat=’A’）;
OSELECT......
FROMempe,deptd
WHEREe.dept_no=d.dept_no
ANDdept_cat=’A’;
7.2.6少用DISTINCT，用EXISTS代替
XSELECTDISTINCTd.dept_code,d.dept_name
FROMdeptd,empe
WHEREe.dept_code=d.dept_code;
OSELECTdept_code,dept_name
FROMdeptd
WHEREEXISTS（SELECT‘X’
FROMempe
WHEREe.dept_code=d.dept_code）;
7.2.7使用UNIONALL、MINUS、INTERSECT提高性能
7.2.8使用ROWID提高检索速度
对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。
例：
SELECTrowid
INTOv_rowid
FROMt1
WHEREcon1
FORUPDATEOFcol2;
......
......
UPDATEt1
SETcol2=......
WHERErowid=v_rowid;
7.2.9查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面
例：
SELECTinfo
FROMtabaa,tabbb,tabcc
WHEREa.acolbetween:
alowand:
ahigh
ANDb.bcolbetween:
blowand:
bhigh
ANDc.ccolbetween:
clowand:
chigh
ANDa.key1=b.key1
ANDa.key2=c.key2;
其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。
7.2.10尽量使用共享的SQL语句
如经常使用select*fromdeptwheredeptno=值
如果每一个‘值’都是常量，则每一次都会重新解释，不能共享内存中的SQL语句优化结果。
应把‘值’设置为一个变量，所有的共同语句都可以优化一次，高度共享语句解释优化的结果。
例：
select*fromdeptwheredeptno=:
d;
7.2.11显示光标优于隐式光标
如：
updatetarget
sett_field=（selects_information
fromsource
wheresource.key=target.key）
whereexists（select…
fromsource
wheresource.key=target.key）
不如下面的显示光标语句好：
declare
cursorcur_sourceis
select*fromsource;
begin
forrowincur_sourceloop
updatetarget
sett_field=row.s_information
wherekey=row.key;
endloop;
end;
升级会员 