SQL_Server数据库设计规范-模板

XXXXXX物料拉动项目数据库设计开发规范

2020年9月

1范围 (4)

2总体要求 (4)

2.1数据库设计总体要求 (4)

2.2数据库对象命名的总体要求 (4)

2.3数据库程序编码的总体要求 (5)

3数据库设计规范 (5)

3.1数据库的概念设计 (5)

3.2数据库的逻辑设计 (6)

3.3数据库物理设计 (6)

4数据库对象命名规范 (7)

4.1.1数据表模块分类 (8)

4.1.2数据表命名方法 (9)

4.1.3数据表属性设计 (9)

5数据字典编写格式规范 (10)

5.1数据库表汇总表格式 (10)

5.2数据库表详述表格式 (10)

5.3编码数据表格式 (11)

6数据开发规范 (13)

6.1数据库脚本的目录结构 (13)

6.2数据库脚本的开发规范 (14)

6.2.1代码书写规范 (14)

6.2.2存储过程编写规范 (17)

7数据库脚本的部署规范 (21)

8数据库开发过程中要避免的问题(CHECKLIST) (21)

8.1数据库设计 (21)

8.2SQL编写 (21)

8.3索引的使用 (22)

8.4TEMPDB的使用规范 (22)

1范围

本规范规定了XXXXXX物料拉动项目数据库建设的规则，是检验评定软件开发者为XXXXXX物料拉动开发的应用系统质量的标准尺度。本规范适用于各软件开发者和有关单位为本项目开发的应用系统所使用的数据库，数据库的设计、数据库内的对象命名、数据字典文档的编写必须遵守本规范。

2总体要求

数据库建设规范主要包括数据库设计规范、数据库对象命名规则和数据库程序开发编码规范、数据字典文档编写格式等内容。

2.1 数据库设计总体要求

数据库是信息化建设的重要基础。数据库设计通常是作为应用系统开发的一部分进行的，但在应用系统开发中数据库设计具有特殊的重要性和相对独立性，因此在本规范中专门进行规定。

开发者在系统设计阶段，应采用合理的设计方法进行数据库设计，建立数据库的逻辑模型和物理模型，最终设计出高性能、易扩展、易维护、少冗余、高安全性、高可靠性、一致性和完整性好的数据库。

在数据库设计完成后，开发者应提交《数据库设计报告》。

在软件鉴定验收时，开发者应提交完整的《数据库设计报告》、《数据字典》和在开发过程中发生的变更记录。

2.2 数据库对象命名的总体要求

数据库对象如表、存储过程等的名称一般根据对象的类别、所在的系统代号和自定义代号来确定，但对各类对象的命名有其特定要求，具体的各种对象的命名要求请见本规范命名规范部分。

自定义代号全部采用英文名称缩写构成。

数据库对象不得使用数据库的关键字作为名字。

本部分的要求具有强制性，开发者应严格遵守此要求。

2.3 数据库程序编码的总体要求

通过对数据库程序编码制定规则，来提高程序的可读性。这其中主要包括变量命名规则、代码排版规则、代码注释规则等。

本部分的代码注释规则是对开发者编写注释的基本要求，开发者自己可以提出对代码注释的更高要求，但不得低于本规则的要求。

3 数据库设计规范

3.1 数据库的概念设计

概念设计的目标是反映系统信息需求的系统概念结构，概念模型不依赖于具体的计算机系统和数据库产品。将概念模型从具体的数据库表结构中提炼出来，不但能够有效的降低数据库设计的复杂性，而且为产品的可移植性打下了良好的基础。同时由于概念模型中不含具体数据库产品的技术细节，更容易为用户所理解，因而更有可能准确反映用户的需求。

概念设计应采用E-R模型进行，针对各个业务系统进行局部数据视图设计，然后进行局部数据视图集成，进行整体数据视图的设计。在整体设计中找到最大的共享数据集，同时对这些共享数据进行语义上的描述(数据产生的业务系统、数据使用的业务系统等)。面向信息服务的数据建模采用E-R模型描述，但所有的数据模式按照中心主题建立。

E-R图设计、画法不是唯一的，但在绘E-R图时应注意：结构要清晰、关联要简洁、实体个数要适中、属性分配要合理、没有低级冗余。

概念设计采用好的工具可体改设计的效率和工作质量，本系统概念设计统一使用ERWin 文件作为最终提交产物。

3.2 数据库的逻辑设计

逻辑设计又称为实现设计，逻辑设计的目的是从概念结构中导出特定的关系数据库产品可实现的数据逻辑模式结构和功能。这些模式在功能、性能、完整性、一致性约束和数据库的可扩展性等方面均应满足用户的各种要求。

逻辑设计的主要任务是根据得到的全局概念模式进行关系逻辑设计，并作规范化分解，建立一系列的规范化分解，对于概念模式中的数据集进行数据表的设计。所有的公共共享数据集，业务数据要保证数据源唯一。同时对本期开发的业务系统中不能维护但需要使用的共享数据集，建立与老业务系统之间数据抽取的映射关系。

面向信息服务的数据模式根据概念模式的来源和用途分别建立数据表或者跨数据库视图。能从业务数据平台中直接获取的数据应设计跨数据库视图，需要从业务数据平台层捕获的历史数据应设计成数据表。

在逻辑设计阶段，应考虑：

?为提高程序的执行效率适当进行逆规范化，减少连接运算

?合理的属性类型及长度

?创建视图

?可以建立面向用户的外模式，提供一定的逻辑数据独立性。

3.3 数据库物理设计

3.3.1数据库对象设计

将设计好的关系逻辑模式按照数据产生的来源建立不同的业务数据集(库)，然后建立一个公共共享数据集(库)，在这个数据库中建立共享视图，同时为各个数据集(库)进行用户设置和权限分配。

因选择的数据库不同，在具体的物理设计上也会有所不同，但要保证数据库的设计和管理符合规范，并保证制定的规范是可行的、可扩的，同时必须符合本规范的要求。

在物理设计阶段，应根据数据的访问需求、频度、性能要求等设计索引和簇集，包括单属性索引、多属性索引、簇集索引等。

3.3.2数据库用户与帐号设计

●数据库账号包括数据库管理员(DBA)，模式管理员（SCHEMA OWNER),应用连接账号，

应用管理账号等；

●数据库管理员账号用于系统管理，不应操作业务数据；

●SCHEMA OWNER账号用于模式的创建，变更和用户对象授权.

●应用连接账号用于应用服务器配置数据库连接；除初始化数据导入外，数据库中的业务

数据应通过应用程序更新．

●应用管理账号主要用于问题排查，仅赋予只读权限；

●禁止使用具有读写权限的数据库账号直接连接后台数据库进行手工的数据记录修改动

作．

3.4 数据库设计提交产物规范

3.4.1产物格式

?所有设计阶段提交的数据库设计文档均使用ERWin文件格式；

?产物文件的命名方式为：项目名称+“数据库设计_XX设计”，其中××分别为“概念”、

“逻辑”、“物理”

3.4.2版本控制

?数据模型文件要进行版本管理．

?版本命名采用三段式：主版本．次版本．序列号

?其中主版本用于系统级的升级变更，次版本用于模块级的升级变更．序列号每次DDL

操作增加１

?正式上线前主版本为０

4数据库对象命名规范

4.1.1数据表模块分类

根据“处理特点”，将数据表进行分类如下：

数据表分类：基本实体表、辅助编码表、业务数据表、实体联系表、系统信息表、累计数据表、历史数据表。

基本实体表：描述业务实体的基本信息和编码。如，产品、客户、供应商、雇员。

辅助编码表：描述属性的列表值。如，合同类型、职称、民族、付款方式。

业务流程表：记录业务发生的流程中产生的数据。如，合同、出仓单、申请单、凭证。

实体联系表：描述业务实体之间的联系，一般用于存放多对多的联系。如雇员-技能表。

系统信息表：存放与系统操作、业务控制有关的参数。如，用户信息、权限、用户配置信息等。

历史数据表：存放历史性的业务信息记录。如：已完成任务、已处理订单。

接口数据表：存放系统与系统之间进行信息交换的数据，如：DD物料信息拉动需求接口表，SAP数据接口表，PCL数据接口表等。

临时处理表：用来存放临时数据的，如：临时计算的中间值存储。在Microsoft SqlServer2005里不推荐频繁使用临时表，这会加剧系统的资源消耗。

系统日志表：用来记录系统的操作日志和异常日志。

4.1.2数据表命名方法

. 业务流程表：TT_子系统简称_<表标识>。

. 基本实体表：TM_子系统简称_<表标识>。

. 辅助编码表：TC_子系统简称_<表标识>。

. 实体联系表：TR_子系统简称_<表标识>。

. 系统信息表：TS_子系统简称_<表标识>。

. 历史数据表：原表名_HST。

. 临时处理表：TG_子系统简称_<表标识>。

. 系统接口表：TI_子系统简称_<表标识>。

. 系统日志表：TL_子系统简称_<表标识>

其中子系统名称为子系统英文简称，分为如下几个分类：

1.EPS : EPS系统专用表，例如：EPS任务信息表TT_EPS_T ask

2.JIT : JIT系统专用表，例如：JIT物料单表TT_JIT_MaterialForm

3.DD : DD系统专用表，例如：DD物料单TT_DD_MaterialForm

4.PPS : PPS系统专用表，

5.BAS : 基础信息表，通用表

6.RDC : RDC系统专用表

7.SUPPLIER:供应商终端专用表

4.1.3数据表属性设计

(1)对每张表都加入CreateBy（当前业务数据的创建人）, LastUpdateBY（当前数

据的最后更新人）,CreateDate（当前数据创建时间）,LastUpdateDate（当

前数据最后更新日期）四个基本字段，由系统自动记录。

(2)对于插入操作和更新操作较为频繁的表，主键的数据类型建议采用整型。

(3) 对于数值列，不可为空，“0”作为默认值。

(4) 对于必要的“冗余”列，如客户名称，应有相应的程序保持各“冗余”列的同一性，以免出现异议。

(5) 对于关联较为紧密，但是更新并不频繁的两个表，建议尽可能互相冗余部分字段，以免产生过多的join操作。

5 数据字典编写格式规范

开发者应提供完整准确的数据字典，数据字典中应包括数据库表汇总表、数据库表详述表、编码数据表三部分内容。

5.1 数据库表汇总表格式

数据库表汇总表的目的是将某一子系统所用到的全部表和视图列在一个表格中，以便检索查找。其格式如下：

表5-1 数据库表汇总表格式表

要求相同性质的表集中排列。汇总表中的表格编号与详述表中对应的表格编号要相同。其中更新记录必须在设计完成后的开发过程中由开发组指定成员进行专门维护，所有对数据库设计的变更必须统一提交此成员统一维护，任何开发人员不可以自行修改数据库结构，所有的变动都必须在数据字典中进行记录。

5.2 数据库表详述表格式

数据库表详述表描述所有表和视图字段的功能，其格式如下：

表5-2 数据库详述表格式表

数据库的完整性约束规则一般应用P、F、I、N、D、U、C表示，其含义如下：

（1）、P：主键（PK）；

（2）、F：外键（FK），在‘取值范围’内指明外键：表名[（列名）]；

（3）、I：在该字段上有索引，在数据表最后指明索引名，方向（A/D），字段名；

（4）、N：该字段不许为空；

（5）、D：有缺省值，在‘取值范围’内指明缺省值；

（6）、U：取值唯一；

（7）、C：主键数据删除时级联删除全部外键数据。非主键无此标志；

具有上述特性的字段，在文档相应的位置按上述说明填写‘*’记号。

5.3 编码数据表格式

编码数据表用于将某些编码内容已经明确的编码表及其内容列在文档中，要求编码数据表按表格编号升序排列。

表5-3 编码数据表格式表

6 数据开发规范

6.1 数据库脚本的目录结构

按照以下结构初始化VSS目录和数据库子目录：

\CMD 放置所有的可以直接通过osql或者批处理命令进行的脚本。

\DBName 在系统中的每个数据库应该有自己的目录结构，目录结构如下：\Shechma：用来创建此数据库的脚本文件，并且来更改数据库构架。

\StaticDATA：用来初始化此数据库的数据文件.

\SP：用来存储存储过程的脚本

\UDF：用户自定义函数。

\View：视图定义脚本。

\DCL：数据控制语句脚本—主要包括控制所有的数据库对象的Grant 和 Revoke 语句。

\TBL：除了下面子目录，这个目录应该包括表的定义脚本，每个表应该有它自己的脚本，此脚本应该包括经表的创建,修改，删除语句，索引，触发器，完整性参照，Check 约束，默认值约束等，每个表创建语句应该包括在不同的脚本中，并被把归类到类似于下面的相应的子目录中.注意：本目录中的脚本和以下子目录的脚本应该命名为它所影响的表名，比如：表名.sql，或者Alter_表名.sql，Del_表名.sql.所有的约束和默认值定义都应该直接通过ALTER TABLE命令来创建,产生脚本后放置在对应的子目录。

在创建表的同时对约束和默认值进行嵌入式的定义应该被禁止

\MISC：这儿用于存放各式各样的其它脚本，例如alter table脚本或者一次更改的脚本，注意，不要放此目录看成是一个包罗各种脚本的容器，只应包括需要放到VSS 中，但是又不属于上面所列目录的脚本。

所有的脚本文件都应该按照数据库对象命名相同的规则－包括没有特殊字符，限制名字以避免歧义，大写每个单词的首字符，所有的包括SQL脚本的文件应该以.SQL为结束字符，而不是.SP,.TRG等。可执行的批处理文件应该是以.CMD为扩展名.

6.2 数据库脚本的开发规范

除非特殊需要，否则所有的数据库操作（创建表、删除表之类的管理脚本除外）均要通过存储过程进行处理，不允许在高级语言程序代码里直接写入SQL脚本。

6.2.1代码书写规范

?每个参数定义和变量定义占一行；

?SQL语句一行不超过80个字符，语句中的连接符or、in、and、以及＝、<=、>=等前后加上一个空格。所有的SQL关键字都要大写。比如SELECT,UPDATE,FROM,ORDER,BY 等

?where子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐,如：

SELECT CustomerID, CompanyName, ContactName, ContactTitle, ADDress, City, Region, PostalCode, Country, Phone, Fax

FROM Northwind.dbo.Customers

WHERE City='London'

OR City='Madrid'

OR City='Paris'

?采用每个存储过程的开始处说明过程功能、传入变量、传出结果:

所有的脚本文件都应该有一个标题，把文件头把在Create…AS语句之后

/****************************************************************** *name : --函数名/View/Store proc

*function : --函数功能

*stEPS : --代码的主要步骤（以自然语言描述算法）

*input : --输入参数

*output : --输出参数

*author : --作者

*CreateDate : --创建时间

*UpdateDate : -函数更改信息（包括作者、时间、更改内容等）

*******************************************************************/

如：

CREATE PROCEDURE ADDPublications

AS

/****************************************************************** ** Desc: The purpose of this procedure is to create the Publications

** for replication, as defined in the Publication table.

** Processing StEPS:

** 1. If dataBASe is not already published, publish it

** 2. For each Publication in Publication table:

** IF Publication doesn't exist

** Create Publication

** IF SnapShot Job for Publication doesn't exist

** Create SnapShot Job

** 3. Change LogReader Agent from default settings

** 4. Change Checkup Agent Heartbeat from default settings

**

** Parameters: none

** Tables Used:

** Publication select

** master..sysdataBASes select

** Return values: = 0 Success

** < 0 Failure

** Called By: None

** Calls: sp_replicationdboption

** sp_update_job

** Author: Michael Eldridge

** Date: 02/05/1998

******************************************************************* ** Change History

******************************************************************* ** Date Author Description

** ---------- --------- -----------------------------------------------------

** 10/03/1998 jsuther Sphinx agents now have step named 'Run Agent.' not ** 'Run Replication Agent.'

** 08/18/2000 brianell Standardized

*******************************************************************/

?脚本采用段四空格缩进格式，多添加注释

/* remark */

if sqlcode=0 then

begin

………

end

elsif sqlcode <0 then

begin

……..

end

end if;

?在一个包括多行的注释中,双中划线应该被放在每行的开始,不要使用/* */风格的

注释,因为注释中可能被嵌套. 如：

Comment complicated sections of code just above the section of code at the same indent level.

-----------------------------------------------------------------

-- Coment at level one

-----------------------------------------------------------------

WHILE (...)

BEGIN

statement 1

statement 2

-------------------------------------------------------------

-- Comment at level 2

-------------------------------------------------------------

statement 3

END

6.2.2存储过程编写规范

?只能用返回值来说明存储过程执行的结果，Return >= 0 标识成功 <-99 标识失败。

所有的存储过程都要返回值，即使只做了查询，也要返回0表示成功。

?错误处理的编写规范：推荐使用以下的存储过程错误处理规范代码来处理数据操作中的错误：

在存储过程的开始要这样写：

Declare @intTransactionCountOnEntry int

Declare @intErrorCode int

Select @intErrorCode = @@Error

If @intErrorCode = 0

Begin

Select @intTransactionCountOnEntry = @@TranCount BEGIN TRANSACTION --如果没有事务，省略此句

End

在存储过程中，在每一步的操作前，要调用：

If @intErrorCode = 0

只要涉及到修改数据的操作，每次操作结束后，立即调用

Select @intErrorCode = @@Error

在存储过程的结尾要这样写：

If @@TranCount > @intTransactionCountOnEntry

Begin

If @intErrorCode = 0

COMMIT TRANSACTION –如果没有事务，就返回成功代码Else

ROLLBACK TRANSACTION –如果没有事务，就返回错误代码End

如：

Declare @intTransactionCountOnEntry int

Declare @intErrorCode int

Select @intErrorCode = @@Error

If @intErrorCode = 0

Begin

Select @intTransactionCountOnEntry = @@TranCount

BEGIN TRANSACTION

End

If @intErrorCode = 0

begin

—insert asset

insert Inventory(EquipmentId, LocationId, StatusId, LeaseId,

LeaseScheduleId, OwnerId,

Lease, AcquisitionTypeID)

values ( @intEquipmentId, @intLocationId,

@intStatusId, @intLeaseId,

@intLeaseScheduleId,@intOwnerId,

@mnyLease, @intAcquisitionTypeID) Select @intErrorCode = @@Error

end

If @intErrorCode = 0

update LeaseSchedule

Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease where LeaseId = @intLeaseId

Select @intErrorCode = @@Error

End

If @@TranCount > @intTransactionCountOnEntry

Begin

If @intErrorCode = 0

COMMIT TRANSACTION

Else

ROLLBACK TRANSACTION

End

?存储过程内部变量的命名规范：建议采用以下的存储过程变量命名规范：