数据库表及字段命名、设计规范

1.数据库表及字段命名、设计规范
1.1数据库表数据库表的命名规范：
表的前缀应该用系统或模块的英文名的缩写（全部大写或首字母大写）。如果系统功能简单，没有划分为模块，则可以以系统英文名称的缩写作为前缀，否则以各模块的英文名称缩写作为前缀。例如：如果有一个模块叫做BBS(缩写为BBS),那么你的数据库中的所有对象的名称都要加上这个前缀：BBS_ + 数据库对象名称，BBS_CustomerInfo标示论坛模块中的客户信息表

表的名称必须是易于理解，能表达表的功能的英文单词或缩写英文单词，无论是完整英文单词还是缩写英文单词，单词首字母必须大写。如果当前表可用一个英文单词表示的，请用完整的英文单词来表示；例如：系统资料中的客户表的表名可命名为：SYS_Customer。如果当前表需用两个或两个以上的单词来表示时，尽量以完整形式书写，如太长可采用两个英文单词的缩写形式；例如：系统资料中的客户物料表可命名为：SYS_CustItem。

表名称不应该取得太长（一般不超过三个英文单词）。
在命名表时，用单数形式表示名称。例如，使用 Employee，而不是 Employees。
对于有主明细的表来说。明细表的名称为：主表的名称 + 字符Dts。例如：采购定单的名称为：PO_Order，则采购定单的明细表为：PO_OrderDts
对于有主明细的表来说，明细表必须包含两个字段：主表关键字、SN，SN字段的类型为int型，目的为与主表关键字联合组成明细表的关键字，以及标示明细记录的先后顺序，如1,2,3……。
表必须填写描述信息
后台表名尽量与前台表名相同，后台独有的表应以_b作为后缀。如r_gggd_b
1.2表字段
 命名规范      
 数据库字段的命名必须遵循以下规范：
采用有意义的字段名。字段的名称必须是易于理解，能表达字段功能的英文单词或缩写英文单词，单词首字母必须大写，一般不超过三个英文单词。例如:人员信息表中的电话号码可命名为：Telephone或Tel。产品明细表中的产品名称可用ProductName表示。（推荐一般用完整的英文单词）。

系统中所有属于内码字段（仅用于标示唯一性和程序内部用到的标示性字段），名称取为：“ID”，采用整型或长整型数，具体根据可能的数据量确定，增加记录时取最大值加1，该字段通常为主关键字。

系统中属于是业务范围内的编号的字段，其代表一定的业务信息，比如资料信息和单据的编号，这样的字段建议命名为：“Code”，其数据类型为varchar，该字段需加唯一索引。

在命名表的列时，不要重复表的名称；例如，在名为 Employee 的表中避免使用名为 EmployeeLastName 的字段。

不要在列的名称中包含数据类型。

设计规范
所有字段在设计时，除以下数据类型timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、binary 、varbinary外，必须有默认值。字符型的默认值为一个空字符值串’’；数值型的默认值为数值0；逻辑型的默认值为数值0；
其中：系统中所有逻辑型中数值0表示为“假”；数值1表示为“真”。
    datetime、smalldatetime类型的字段没有默认值，必须为NULL。

当字段定义为字符串形时建议使用varchar而不用nvarchar。

建议在大多数表中（如报销单，申请单），应都有以下字段：
字段名说明类型默认值
CreatorID创建者int0
CreatedTime创建时间DatetimeNULL

字段的描述
数据库中每个字段的描述(Description)如下：

尽量遵守第三范式的标准（3NF）。
表内的每一个值只能被表达一次
表内的每一行都应当被唯一的标示
表内不应该存储依赖于其他键的非键信息

如果字段事实上是与其它表的关键字相关联而未设计为外键引用，需建索引。

如果字段与其它表的字段相关联，需建索引。

如果字段需做模糊查询之外的条件查询，需建索引。

除了主关键字允许建立簇索引外，其它字段所建索引必须为非簇索引。

字段必须填写描述信息

2存贮过程命名及设计规范
2.1命名规范
存贮过程的命名请遵循以下命名规范：USP _ + 系统模块缩写（与表前缀类似）+_ + 功能标识 + 代表存贮过程操作的主要表名（不带前缀）或功能的英文单词或英文单词缩写。
如果一个存贮过程只对一个表进行操作，建议存贮过程的名称就用存贮过程所操作的表的表名（不带前缀）。这样有利于根据表名找到相应的存贮过程。
为了在众多的存贮过程中能很快的找到并维护存贮过程，我们按存贮过程的作用将系统的存贮过程
进行以下的分类及命名：（以下示例假设存贮过程所在的模块名为ORG）
作用第一前缀第二前缀名
(功能标识)示例
用于新增的存贮过程USP_ORGAddUSP_ORG_Add_Employee
用于修改的存贮过程USP_ORGUptUSP _ORG_Upt_Employee
用于删除的存贮过程USP_ORGDelUSP _ORG_Del_Employee
用于单据查询的存贮过程USP_ORGQryUSP _ORG_Qry_Employee
用于报表统计的存贮过程USP_ORGRptUSP _ORG_Rpt_GetEmployee
用于一些特殊过程处理的存贮过程USP_ORGOthUSP _ORG_Oth_SetSystemMessage

如果系统中的存贮过程只有一级，则遵照以上规则命名，如果存在多级，则需要区分其属于哪一级，具体为：USP + 所属的级次 + _ + 后面的部分
例如：
1.USP1_ORG_Add_Subject        （没有调用其它存贮过程）
2.USP2_ORG_Upt_Subject          (调用了第1级的存贮过程)
3.USP3_ORG_Qry_Subject          (调用了第2级的存贮过程)

2.2设计规范
在存贮过程中必须说明以下内容：
目的：说明此存贮过程的作用。
作者：首次创建此存贮过程的人的姓名。在此请使用中文全名，不允许使用英文简称。
创建日期：创建存贮过程时的日期。
修改记录：
修改记录需包含修改顺序号、修改者、修改日期、修改原因，修改时不能直接在原来的代码上修改，也不能删除原来的代码，只能先将原来的代码注释掉，再重新增加正确的代码。修改顺序号的形式为：log1，log2，log3。。。，根据修改次数顺序增加，同时在注释掉的原来的代码块和新增的正确代码块前后注明修改顺序号。
对存贮过程各参数及变量的中文注解。
示例如下：

/*
目的：根据部门与物料和会计区间查询生产现场领料汇总报表
作者：李奇
创建日期：2002-12-11
*/

/*
修改顺序号：log1
修改者：刘敏
修改日期：2002.12.22
修改原因：（具体原因详细描述）
*/

CREATE PROCEDURE [dbo].[USP_GetLMSSum]
@ProductionType int=1,    --生产类型（1-自制；0-委外加工）
@DeptID int=0,     --生产部门
@ItemID int=0,     --物料
@StartDate datetime='2002-11-26',--会计区间开始日期
@EndDate datetime='2002-12-25'--会计区间截止日期
AS

/*
log1 old
--自制领料
INSERT INTO #LMSDts
SELECT dbo.iStockBill.DeptID, dbo.fDept.DeptName,
      dbo.mLMS.ItemID AS ItemInterID, dbo.fItem.ItemID, dbo.fItem.ItemName,
      ISNULL(dbo.fItem.Model, N'') AS Model, ISNULL(dbo.fUnit.UnitName, N'')
      AS UnitName, dbo.mLMS.Qty, dbo.mLMS.TransType, dbo.mWO.OrderType,
      dbo.mLMS.CreateTime
end log1 old
*/
--log1 new
--自制领料
INSERT INTO #LMSDts
SELECT dbo.iStockBill.DeptID, dbo.fDept.DeptName,
      dbo.mLMS.ItemID AS ItemInterID, dbo.fItem.ItemID, dbo.fItem.ItemName,
      ISNULL(dbo.fItem.Model, N'') AS Model, ISNULL(dbo.fUnit.UnitName, N'')
      AS UnitName, dbo.mLMS.Qty, dbo.mLMS.TransType, dbo.mWO.OrderType,
      dbo.mLMS.CreateTime
--end log1 new

3 视图命名规范
3.1命名规范
视图的命名请遵循以下命名规范：UV _ + 系统模块缩写（与表前缀类似）+_ + 功能标识 + 代表视图查询的主要表名（不带前缀）或功能的英文单词或英文单词缩写。
如果一个视图只对一个表进行查询，建议视图的名称就用视图所查询的表的表名（不带前缀）。这样有利于根据表名找到相应的视图。
为了在众多的视图中能很快的找到并维护视图，我们按其作用将系统的视图
进行以下的分类及命名：（以下示例假设视图所在的模块名为ORG）
作用第一前缀第二前缀名
(功能标识)示例
用于单据查询的视图UV_ORGQryUV_ORG_Qry_Employee
用于报表统计的视图UV_ORGRptUV_ORG_Rpt_GetEmployee
用于一些特殊过程处理的视图UV_ORGOthUV_ORG_Oth_SetSystemMessage

如果系统中的视图只有一级，则遵照以上规则命名，如果存在多级，则需要区分其属于哪一级，具体为：UV + 所属的级次 + _ + 后面的部分
例如：
UV1_ORG_Add_Subject        （没有调用其它视图）
UV2_ORG_Upt_Subject          (调用了第1级的视图)
UV3_ORG_Qry_Subject          (调用了第2级的视图)

3.2 设计规范
在视图中必须说明以下内容：
目的：说明此视图的作用。
创建者：首次创建此视图的人的姓名。在此请使用中文全名，不允许使用英文简称。
修改者、修改日期、修改原因：如果有人对此视图进行了修改，则必须在此视图的前面加注修改者姓名、修改日期及修改原因。
对视图各参数及变量的中文注解。
示例如下：
/*
目的：查询本月所要培训的科目
创建：加菲猫
时间：2001-3-3
修改者：Dyan    修改日期：2002-12-11     
修改原因及内容：学员不需要培训，将不需要培训的课程去掉。
修改者：周明    修改日期：2002-4-2     
修改原因及内容：增加一门新课程
*/
CREATE VIEW dbo.USP_AddSubject
AS
SELECT SubjectIId AS 课程编号   
FROM dbo.ZfLocaleDecide

3.3 存储过程和事务处理
如果事务处理在存储过程返回时的嵌套层次与执行时的层次不同，SQL Server会显示信息提示事务处理嵌套失控。因为存储过程并不异常终止该批处理，在执行和确认随后的语句时，过程内的rollback tran 会导致数据完整性损失。
在编写存储过程时，应遵守以下原则：
1．过程对@@trancount应无净改变。
2．仅当存储过程发出begin tran语句时，才发出rollback tran。

3.4 其他注意事项
存储过程应该坚实可靠的，因为它们是驻留在服务器中，被频繁使用的。应仔细检查参数的有效性，并在有问题时返回出错信息。应确保参数的数据类型和被比较的栏的数据类型匹配，从而避免数据类型匹配错误。在每个SQL语句之后要检查@@error。

4触发器编码规范
4.1命名规范
触发器名为相应的表名加上后缀

Insert触发器加'_i'，Delete触发器加'_d'，Update触发器加'_u'，如：r_bch_i，r_bch_d，r_bch_u。

4.2 设计规范
在触发器中必须说明以下内容：
目的：说明此触发器的作用。
创建者：首次创建人的姓名。在此请使用中文全名，不允许使用英文简称。
修改者、修改日期、修改原因：如果有人对此视图进行了修改，则必须在此视图的前面加注修改者姓名、修改日期及修改原因。
对其中各参数及变量的中文注解。

4.3范例
下面通过一个例子，说明触发器编程中应遵守的规范：

/* delete related r_a according to deleted table */
CREATE TRIGGER r_a_d ON r_a
      FOR DELETE
AS
IF @@ROWCOUNT = 0 -no rows deleted
          RETURN

/* delete r_b table related to deleted table */
DELETE r_b
          FROM r_b b, deleted d
          WHERE b.id=d.id
IF @@ERROR != 0
    BEGIN
          RAISERROR("Error occurred deleting related records", 16, 1)
          ROLLBACK TRAN
END
RETURN

作以下几点说明：
1．检查是否有行被修改。注意：不论数据是否被修改，触发器都会引发，执行情况取决于T-SQL语句的执行，而和任何潜在的where子句是否执行无关。
2．因为被删除行在该表中不再可用，所以应在被删除的表中查看。
3．检查T-SQL语句的返回代码，以捕获任何出错条件。
4.4 事务过程中的触发器
1．触发器内的rollback将所有工作返回至最外层的begin tran，完成触发器内的处理并异常终止当前的批处理。
2．不可以从触发器内部返回至某个已命名的事务过程，这将产生运行错误，挂起所有工作并终止批处理。
5 SQL语言编码规范
5.1所有关键字必须大写。
如：INSERT、UPDATE、DELETE、SELECT及其子句。
IF……ELSE、CASE、DECLARE等。

所有函数及其参数中除用户变量以外的部分必须大写。

在定义变量时用到的数据类型必须小写。
所有关键字必须大写
5.2注释
注释可以包含在批处理中。在触发器、存储过程中包含描述性注释将大大增加文本的可读性和可维护性。本规范建议：
1、注释以英文为主。
    实际应用中，发现以中文注释的SQL语句版本在英文环境中不可用。为避免后续版本执行过程中发生某些异常错误，建议使用英文注释。
2、注释尽可能详细、全面。
创建每一数据对象前，应具体描述该对象的功能和用途。
传入参数的含义应该有所说明。如果取值范围确定，也应该一并说明。取值有特定含义的变量（如boolean类型变量），应给出每个值的含义。
3、注释语法包含两种情况：单行注释、多行注释
单行注释：注释前有两个连字符（--），最后以行尾序列（CR-LF）结束。一般，对变量、条件子句可以采用该类注释。
多行注释：符号/*和*/之间的内容为注释内容。对某项完整的操作建议使用该类注释。
4、注释简洁，同时应描述清晰。
5函数注释：
编写函数文本--如触发器、存储过程以及其他数据对象--时，必须为每个函数增加适当注释。该注释以多行注释为主，主要结构如下：
/************************************************************************
*name        :               --函数名
*function    :               --函数功能
*input       :               --输入参数
*output      :               --输出参数
*author      :               --作者
*CreateDate  :             --创建时间
*UpdateDate  :               --函数更改信息（包括作者、时间、更改内容等）
*************************************************************************/
CREATE PROCEDURE sp_xxx

5.3条件执行语句if…else
条件语句块（statenemt block，以 begin…end为边界）仅在if子句的条件为真时才被执行。
为提高代码的可读性，建议嵌套不多于5层。还有，当嵌套层次太多时，应该考虑是否可以使用case语句。

5.4重复执行while和跳转语句goto
需要多次执行的语句，可以使用while结构。其中，控制while循环的条件在任何处理开始之前需要先执行一次。循环体中的保留字break无条件的退出while循环，然后继续处理后续语句；保留字continue重新计算while条件，如果条件为真，则从循环开始处重新执行各语句。
使用跳转语句goto和标签label也可以方便地实现循环和其他更灵活的操作。SQL SERVER仅具有单通道语法分析器，因此不能解析对尚未创建的对象所做的前向参考。换言之，跳转到某标签的后续语句应该是可执行的（如不存在可能尚未创建的数据对象）。

5.5书写格式
数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。规范建议按照分级缩进格式编写该文本。
顺序执行的各命令位于同一级；条件语句块（statenemt block，以 begin…end为边界）位于下一级，类推。
SQL语句是该文本的主体。为适应某些教复杂的用户需求，SQL语句可能比较庞大。为方便阅读和维护，规范建议按照SQL语句中系统保留字的关键程度再划分为三级。具体分级请参照下表。其中，非系统保留字（如字段名、数据表名、标点符号）相对本级保留字再缩进一级。多个连续的非保留字可以分行书写，也可以写在同一行。当WHERE包含的条件子句教复杂时，应该每行只写一个条件分句，并为重要的条件字句填写单行注释。
在保证基本缩进格式的前提下，可以通过对齐某些重要关键字（如条件关键字AND、OR，符号 = 、 <> 等）来进一步提高文本的易读性和可维护性。
相邻两级的缩进量为10个空格。这也是ISQL编辑器默认的文本缩进量。另外，在ISQL编辑器中，一个TAB键也相当于10个空格。

6数据对象的国际化
6.1关于数据对象的命名
数据对象和变量的命名一律采用英文字符。禁止使用中文命名。其他命名注意事项和规范请参考2命名规则。

6.2关于RAISERROR
SQL SERVER 系统的RAISERROR命令能够把某个出错情况返回给调用过程，这对说明调用过程的执行情况很有必要；同时可以部分避免客户端的冗余操作。另外，结合系统存储过程sp_addmessage和sp_dropmessage可以方便实现数据对象在SQL SERVER端的国际化。
SQL SERVER的MASTER数据库中有错误信息数据表sysmessages，专门用于存储系统和用户的错误提示及相关信息（如错误ID号、错误等级、状态）。用户可以调用sp_addmessage和sp_dropmessage预先将各类错误信息记入该数据表。其中，不同的错误信息用错误ID号区分。在编写存储过程代码时，调用RAISERROR函数从错误信息表sysmessages中引用相关错误ID号的错误信息。
由于0~50000的值是保留为 SQL SERVER使用的，所以用户自定义错误信息的错误ID号必须大于50000。
RAISERROR的语法如下：
RAISERROR ({msg_id | msg_str}, severity, state )
    本规范建议存储过程以RAISERROR和RETURN返回。
举例如下：
l第一步：生成该错误信息
/*insert a error message into the master..sysmessages*/
sp_addMessage 50001 ,16 ,'Can"t update the primary key from table %s'

l第二步：执行存储过程sp_xxx，异常返回时引用上述错误信息
CREATE PROCEDURE sp_xxx
BEGIN
…
RAISERROR(50001 ,16 ,1 ,'r_a')   -- Can"t update the primary key from table r_a
ROLLBACK TRAN
RETURN(1)
END

l第三步：在DELPHI调用中，通过EDBEngineError捕捉该错误
  try
    sp_test.execProc ;
  except
  on e :EDBEngineError
    if e.errors[1].errorcode = 13059 then
     /*hint 'Can"t update the primary key from table r_a'*/
     showMessage(e.errors[1].message)   
  end ;

l第四步：当不再使用该错误信息时，应该从错误信息表中删除相应数据
sp_dropMessage 50001

l第五步：错误提示信息国际化
用相应语言替换master..sysmessages表中用户自定义的错误消息即可。