数据库规范Word下载.docx

数据库规范Word下载.docx

《数据库规范Word下载.docx》由会员分享，可在线阅读，更多相关《数据库规范Word下载.docx（40页珍藏版）》请在冰豆网上搜索。

3.5.1书写风格7

3.5.2性能优化8

3.5.3跨数据库支持9

3.6死锁预防规范9

4.检查制度10

5.裁剪指南10

6.层次关系10

6.1主控文件10

6.2支持文件11

6.3相关文件11

7.附录11

7.1同望数据库设计模板11

7.2规范用词用语说明11

7.3常用域一览表12

7.4保留关键字表13

7.4.1SQL关键字13

7.4.2Oracle关键字13

7.4.3SQLServer关键字14

7.4.4DB2关键字15

7.4.5ODBC关键字18

7.5数据库设计建议19

1.目的范围

●目的作用：

数据是企业的一种资产，是企业IT应用的基础和前提，通过数据挖掘等手段可以实现数据的附加值。

应用系统必须保证数据的安全和可靠。

本规范的目的就是指导和规范数据库设计，提高设计人员的数据库设计技能，以达到应用系统数据的安全和可靠，并具备高性能和易扩展。

●应用范围：

创新中心、方案项目事业部、工程造价事业部

●读者对象：

公司所有数据库设计人员、开发人员。

2.术语定义

高并发系统：

指业务处理系统吞吐率超过每秒10次的系统。

CLOB：

CharacterLargeObject，字符大对象。

BLOB：

BinaryLargeObject，二进制大对象，是一个可以存储二进制文件的容器。

实体表：

指对应实际的业务对象的表。

关系表：

指不代表一个对象而是对象间的关系的表。

3.标准规范

3.1设计准则

准则一、数据库设计必须在系统的安全性、可靠性、性能、易开发维护之间保持平衡，必须保证应用服务器与数据库服务器之间的平衡。

这是数据库设计的基本准则。

准则二、数据库设计应遵守3NF规范，表之间的关系应通过外键连接。

表只包括其本身基本的属性；

当不是它们本身所具有的属性时需进行分解；

在遵循3NF的基础上对复杂的数据查询做适当冗余，减轻编码的繁琐以及过多的表关联查询。

准则三、不应使用硬编码的方式，而应采用数据驱动方式保存常用信息。

这样可以使许多策略变更和维护都会方便得多，大大增强系统的灵活性和扩展性。

例如：

采用数据字典维护基础信息，存放数据库当前版本等信息。

准则四、严禁使用触发器。

使用触发器不能很好支持跨数据库，并且对数据库带来额外的计算开销，不利用程序调试。

准则五、严禁高并发系统在业务处理程序中使用视图和存储过程，可在批处理中使用视图和存储过程。

严禁有跨数据库要求的产品使用视图。

业务处理是指用户提交表单等操作频繁的处理，批处理是指集成执行的并发小而复杂的处理。

在高并发系统中，在频繁的处理中使用视图和存储过程，会加大数据库的加力，数据库将成为系统的瓶颈，且最大提升为三台服务器集群，而且这种成本非常高；

但如果在应用服务器中处理，则实现集群的方案更为经济可行。

视图检索速度比直接使用SQL慢，视图需要预解析。

视图对跨平台支持不佳，不同数据库对视图语法支持不太一样，特别是复杂的视图移植起来困难度上升。

准则六、预计记录数超过1万的表不宜超过30列，不宜在一个表中保持多个对象。

如果字段过多，会影响数据处理性能。

若表字段过多宜拆分为多个表。

准则七、禁止在高并发系统的业务处理事务中同时处理大字段（CLOB、BLOB）数据，禁止检索大字段，大字段不得超过10M。

超过10M的数据存储为文件。

在事务处理的同时处理大字段会延长处理时间，易导致数据库死锁。

准则八、字段长度应尽量充足；

中文字符长度当应用程序为GBK编码时应设置为每字符2个字节，当应用程序为UTF-8时应设置为每字符3个字节。

因为oracle9i及以上版本utf-8长度为3个字节。

准则九、命名宜用英文或缩写，不宜使用汉语拼音，严禁使用留空格，严禁使用保留关键字。

名称长度不得超过30个字符。

要保证字段名不与保留字、数据库系统常用访问方法冲突。

准则十、同一系统不同表中同一字段名称的数据类型必须保持一致性。

在命名字段并为其指定数据类型的时候一定要保证一致性。

数据类型在一个表里是整数，那在另一个表里不得变成字符串型。

准则十一、所有表必须设置时间戳（最后修改时间）（tts:

TooneTimeStamp）、最后修改人（tlm:

TooneLastModifier）字段，应设置删除标志（tdf:

TooneDeleteFlag）字段。

设置时间戳tts记录数据最后变化时间，便于数据后期处理ETL（抽取、转换、装载的过程），同时为事后校核数据提供参考。

设置删除标志tdf，采用软删除有利于删除数据的恢复，也可以追踪删除数据的历史，另一方面，因为数据记录删除量达到总体数据量20%以上时索引检索速度会下降，通过软删除可以防止这种情况。

采用软删除机制必须配套的在系统中设置数据维护计划，定期批量清除删除数据，以防止垃圾数据影响查询性能。

设计最后修改人tlm是为了追踪数据处理人员，便于事后审计。

准则十二、时效性数据表可设置历史记录字段（thi:

TooneHistoryInfomation），记录表关键时效信息。

通过一个字段记录历史变化，用于追踪数据操作流程信息，便于事后审讯或让用户了解数据处理过程。

相比每个表设置历史记录表，处理更简单，性能更好。

准则十三、应保持事务尽可能小，请求的资源尽可能少；

应按同一顺序访问数据对象。

不同事务写两个及以上表时，应按同一顺序操作，且不得交替写入。

比如事务T1、T2写表Table1、Table2，应按同样的顺序先Table1再Table2，或先Table2再Table1，而不能是T1先Table1再Table2，T2先Table2再Table1，这样处理在并发情况下可能出现死锁。

进一步可以引申为在同一个事务中，不得出现先写Table1再写Table2，然后写Table1的情况。

3.2命名规范

一、表命名

由于历史原因，IP、IE、OA的三条产品线形成了三套命名规范，为了确保规范的延续性和统一性，本规范将三种命名规范均视为合理，同一产品线应使用一致的命名约定。

具体如下表：

产品线

IP产品线

IE产品线

OA产品线

公式

[系统标识]_[业务模块标识]_[表标识]或者[部门标识]_[业务模块标识]_[表标识]

[系统标识]_[表标识]

[系统标识]_[表类型]_[表标识]

说明

表名一律小写，中间以下划线（_）分隔各标识段，以相同业务模块的表排列在一起为原则

系统标识：

4个小写字母，前两位是产品线标志，后两位是子系统缩写。

表标识：

如果表名的实意单词是一个或两个单词组成，名字就取全名；

如果表的名字由3个或者3个以上单词组成，就各取单词缩写。

单词首字母大写，其他字母小写

系统标识:

一个业务模块（或子系统）有多个业务表，业务模块标识（或者叫子系统标识）用于标识这些表是同一个业务模块（子系统）。

新增业务模块时，该标识必须简短唯一；

表类型（e,r）:

e表示实体表,r表示实体关系表。

表标识:

表标识可以由多部分组成，中间用下划线隔开，不使用大小写字母的方式。

表标识需要简短的说明表的作用

示例

年计划：

ipms_plan_year

季计划：

ipms_plan_season

合同表：

iemt_Contract

文档信息表：

tOA_E_MeetDoc

接收消息表：

tOA_E_Msg_Received

除此之外，表命名应满足以下约定：

1、实体表按上述标准命名；

2、基础数据表加前缀z_。

使得基础数据表排在数据库表的最后面，方便实施检索及维护。

如：

z_cr_DataDict（CRM系统的基础数据库）；

3、冗余表（主要是累计表）按[系统标识]_x_[原表标识]。

CRM的应收结算结算单冗余表：

crap_x_Account；

4、关系表按[系统标识]_r_[表1标识]_[表2标识]_[…]命名，表标识按照字母顺序罗列如果超过长度要求，则先将标识较长的改为缩写。

crwk_Depart和crwk_Employee;

存在多对多的关系;

则关联表命名为crwk_r_Depart_Employee。

二、字段命名

1、和Java变量（属性）命名规范相同，采用大小写混合的方式，第一个单词的首字母小写，其后单词的首字母大写。

字段名和最终所对应的VO对象属性名完全相同。

字段名长度不超过30个字符。

2、严禁使用保留关键字命名字段，详见附录：

保留关键字表

3、不宜使用敏感的单词做字段名。

比如：

name、index、type、status、sysDate等等。

三、域（Domain）命名

1、域按dm_[域标识][长度]命名；

2、域标识首单词首字母小写，首单词后的单词道字母大写；

3、长度部分除非必要，否则不必标识，如果需要2个长度说明则以‘-’分开。

4、常用域列表请见附录6.2常用域一览表。

四、外键约束、索引命名

外键约束命名规则：

FK_<

表1标识>

_REF_<

表2标识>

在PowerDesigner中，外键约束会被自动命名，可以自动命名为准，但要保证全局（多文档）唯一性。

索引命名规则：

INDEX_<

表标识>

_<

字段标识>

索引名要保证全局（多文档）唯一性。

外键约束、索引名称长度不超过30个字符。

五、视图、过程、函数、触发器命名

视图前面加前缀“v_”。

存储过程名以前缀“p_”开头。

函数名以前缀“f_”开头。

触发器以前缀“t_”开头。

其余部分按照表名命名规则。

3.3字段定义规范

1、字段数据类型以域来设计，但生成脚本时不生成域；

2、字段数据类型必须优先采用附录7.2常用域一览表中的域；

3、字段命名必须满足3.2.2字段命名规范；

4、标志类型、数字类型要求提供缺省值。

3.4约束条件

对象

规范

主键

一律以业务无关字段ID作为表的单一主键，字段类型为varchar2（64）/varchar（64），字段值为UUID。

外键

为保证数据完整性，为外键字段建立外键关联约束。

索引

1.经常出现在Where条件中的字段应建立索引。

比如外键字段。

Having后的条件索引是无效的。

2.仅用于distinct或groupby子句引用的列不必建立索引。

3.可以枚举出来的数据的列不宜建立索引。

比如布尔型只有T和F，则不宜建索引，因为建索引会降低效率。

4.当复合索引为多列时应将变化显著的列放到复合索引的首位。

复合索引不宜超过16列（具体可能多数据库会不同）。

索引的列太多则插入数据会变慢，当索引太多的时候，必须权衡查询和插入数据的响应时间是否满足需求。

在where或orderby子句中使用复合索引，条件字段的顺序应与建立索引时的字段顺序一致，且必须包含复合索引的第一列。

如果不包括第一列，则在不会用到该索引。

例如在建立复合索引时列的顺序是F1，F2，F3，则在where或orderby子句中只能是F1或F1，F2或F1，F2，F3。

5.严禁动态创建索引。

3.5SQL语句规范

3.5.1书写风格

1.语句关键字应全部使用小写。

2.引用字符时应使用单引号。

updatetestablesetidcol=’abcd’。

3.连接符或运算符or、in、and、＝、<

=、>

=，+，-等前后宜加上一个空格。

否则容易导致以下类似问题。

例如在语句selecta–bfromtable中，a，b均为变量，拼写该语句时，如果a=6，b=-3，则语句变为select6--3fromtable。

--被视为SQL的注释，结果语句报错。

4.不得使用“select*from…”语法，必须标明字段名。

即selectcol1,col2,…fromtableawhere…

5.严禁使用“insertintotable_namevalues（?

?

……）”语法，统一使用“insertintotable_name（col1,col2,……）values（?

…...）”。

6.SQL语句包含多表连接时，必须加上表的别名，对每个字段的使用都要带上表别名。

即selecta.col1,a.col2,b.col3fromtableaa,tablebbwherea.col4=b.col5

7.应避免显式或隐含的类型转换。

例如在where子句中numeric型和int型的列的比较。

8.在子查询中前后必须加上括号。

selectcol1,col2fromtableawherecol3in（selectcol4fromtablebwherecol4>

0）

9.执行SQL时一次应只执行一条，如果多条语句则应分开执行，但必须保持在一个事务中。

不得一次执行通过分号等分开的多条语句，这样处理不清晰。

10.如果能采用or代替，则不宜使用in语句。

in语句中的元素不得超过500个，如果超过，则应拆分为多条SQL语句。

严禁使用xxin（‘’,’’….）orxxin（‘’,’’,’’）。

11.or连接条件不得超过500，超过时应拆分为多条语句。

3.5.2性能优化

1.查询时应尽量减少多余数据的读取，通过使用where子句来减少返回的记录数。

2.如果在语句中有notin（in）操作，应尽量用notexists（exists）来代替。

特别对大数据量的两者检索速度有很明显的区别。

3.不宜使用外连接。

外连接效率低。

4.一条SQL语句中不宜使用3层以上的嵌套查询。

如果超过，则应在Java等应用服务器程序中处理。

5.一条SQL语句中不得从4个及以上表中同时取数。

仅作关联或过滤条件而不涉及取数的表不参与表个数计算；

如果必须关联4个或4个以上表，应在Java等应用服务器程序中处理。

6.应尽量避免使用orderby和groupby排序操作，如必须使用排序操作，尽量建立在有索引的列上。

因为大量的排序操作影响系统性能。

7.对索引列的比较，应尽量避免使用not或!

=，可拆分为几个条件。

因为“not”和“!

=”不会使用索引。

如col1是索引列，条件col1!

=0可以拆分为col1>

0orcol2<

0。

8.应尽量将数据库函数、计算表达式写在逻辑操作符右边。

因为这些对列的操作会将导致表扫描，影响性能。

9.在where子句中，如果有多个过滤条件，应将索引列或过滤记录数最多的条件放在前面。

10.能用连接方式实现的功能，不得用子查询。

selectnamefromcustomerwherecustomerIdin（selectcustomerIdfromorderwheremoney>

1000）。

应该用如下语句代替：

selectnamefromcustomerinnerjoinorderoncustomer.customerId=order.customerIdwhereorder.money>

100。

或selectnamefromcustomerwhereexists（select1fromorderwheremoney>

1000andcustomer.customerId=order.customerId）<

这里需要注意：

使用exists的效率依赖于匹配度，innerjoin效率比较稳定>

11.多表关联查询时，写法可遵循以下原则，这样做有利于建立索引，提高查询效率。

格式如下：

selectsum（t1.je）fromtable1t1,table2t2,table3t3where（t1的等值条件（=））and（t1的非等值条件）and（t2与t1的关联条件）and（t2的等值条件）and（t2的非等值条件）and（t3与t2的关联条件）and（t3的等值条件）and（t3的非等值条件）。

3.5.3跨数据库支持

1.对于跨数据库Java应用程序的VO映射数据库的数据格式建议：

1）整型字段：

字段设置保存为Integer或者Long

2）数字型字段：

若需要使用小数2位以上的精确计算，读取、插入、更新使用BigDecimal类型

3）字符型字段：

读取为String，并保存为String，插入或者更新为String

4）时间字段：

读取为String，插入或者更新时的时间格式使用中间件统一处理。

2.字符串连接应使用“||”符号，而不应使用“+”。

“+”是SQLServer语法，Oracle和DB2支持“||”，Hibernate转化为SQLServer时，会自动将“||”转为“+”。

3.通配符不能使用‘[a-c]%’这种形式。

应写成如：

selectcol1,col2fromtable_namewherecol1like‘[a]%’ORcol1like‘[b]%’ORcol1like‘[c]%’

4.截取字符串长度函数应使用substr，起始位置为1表示从头开始。

因为db2中substr起点为1，0会报错；

在SqlServer数据库中使用的是substring需要进行转换。

5.不得通过selectpercentn和selecttopn限制查询结果集的记录数。

6.join与on必须严格匹配，严禁出现没有on的join。

7.join…on后面不宜使用or，如果使用则需将or的范围用（）括起来。

8.不得使用selectinto的格式。

Selectinto是SQLServer特有语法，因为Oracle和DB2不支持。

9.应将Null值与空字符串（长度为零的字符串）视为不同。

虽然Oracle视Null与空字符串为相同，但DB2和SQLServer却视为不同。

3.6死锁预防规范

1.应尽可能缩短事务。

在同一DB中并发执行多个需要长时间运行的事务时，发生死锁的概率较大。

事务运行时间越长，其持有排它锁（exclusive锁）或更新锁（update锁）的时间便越长，从而堵塞了其它活动并可能导致死锁。

保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

同时，涉及多个表的查询更新操作，若比较耗时，尽量不要放在一个事务内处理，能分割便分割。

若不能分割，便尽可能使之在业务量较小的时间（例如子夜或者午餐时间）执行。

2.应按同一顺序访问数据对象。

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。

例如，如果两个并发事务获得Supplier表上的锁，然后获得Part表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在Supplier表上。

第一个事务提交或回滚后，第二个事务继续进行。

不发生死锁。

将存储过程用于所有的数据修改可以标准化访问对象的顺序。

3.必须避免编写包含用户交互的事务。

因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，若用户不能及时反馈，则此事务将挂起。

因而将严重降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。

即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。

4.可使用低隔离级别。

确定事务是否能在更低的隔离级别上运行。

执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。

使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。

5.可考虑体系结构的优化与代码重构，提高系统整体的运行效率。

例如尽可能不采用效率低下的计算模型，复杂的业务应采用异步任务调度处理。

6.可通过程序控制事务提交的时机。

如果一次检索出了10万条记录但只更改了其中的100条，就可以通过代码来执行100个update。

或是用分段提交，即所有的修改使用多个事务进行提交，但这样会使事务不完整，应酌情使用。

7.宜将经常更新的数据库和查询数据库分开。

定期将不改变的数据导入查询数据库中，这样查询和更新就可以分开进行，而降低死锁机率。

8.在进行数据库模式设计时，应注意外键引用的完整性，并对外键加索引。

如果更新了父表的主键，由于外键上没有索引，所以子表会被锁定；

如果删除了父表中的一行，整个子表也会被锁定。

4.检查制度

1、由公司技术总监每年末组织公司年度设计审查，重点针对重点项目和高并发系统；

各部门应在每个季度末由部门技术负责人（架构师或技术总监）组织数据库设计审查；

产品和项目技术负责人每月度应进行数据库设计审查，并将走查结果记入项目状态报告。

以落实本规范的执行。

2、产品或项目的设计评审时，本规范为数据库设计评审的重要依据，如不符合规范应报告部门技术负责人和公司技术总监。

5.裁剪指南

1.本规范适用于以下项目：

1）高并发系统项目

2）在发布日之后立项的新项目。

3）在发布日之前已立项，但尚未开始或者刚刚开始进行数据库设计的在建项目。

2.本规范不适用于以下项目：

1）在本规范相应版本生效日期之前就已经结束的老项目。

2）在本规范相应版本生效日期之时未结束，但已经完成或者已经进行部分数据库设计的在建项目（高并发系统除外）。

3.在本公司级规范发布之前，可能各部门均存在各自的内部规范，对于新老规范交替的过渡期，允许在本规范的基础上做一定程度的裁剪，但在可能的情况下应尽量遵循本规范，以逐步实现最终一致。

4.在同一个产品线中，数据库设计所遵循的规范应当保持一致。

6.层次关系

6.1主控文件

《软件开发程序》

6.2支持文件

无

6.3相关文件

7.附录

7.1同望数据库设计模板

7.2规范用词用语说明

1为便于在执行本规范条文时区别对待，对于要求严格程度不同的用词说明如下：

（1）表示很严格，非这样不可的用词：

正面词采用“必须”，反面词采用“严禁”。

（2）表示严格，在正常情况下均应这样做的用词：

正面词采用“应”，反面词采用“不应”或“不得”。

（3）表示允许稍有选择，在条件许可时首先应这样做的用词：

正面词采用“宜”，反面词采用“不宜”。

（4）表示有选择，在一定条件下可以这样做的，采用“可”。

2本规范中指定按其他有关标准、规范执行时，写法为:

“应符合……的规定”或“应按……执行”。

非必须按所指定的标准和规范执行的，写法为:

“可参照……”。

7.3