1 引言
1.1 编写目的
数据库是整个软件设计与开发的基础，数据库设计的好坏直接影响着软件产品的性能及其可扩充性。
数据库设计应由系统分析设计人员与数据库设计人员共同完成，系统分析设计人员从整个软件功能实现的角度提出对数据库的具体要求，他们应向数据库设计人员提供数据流图和数据字典，在这个阶段，考虑较多的是数据库的设计如何满足业务的需求并适应业务的发展；数据库设计人员根据数据流图与数据字典，参考系统需求文档与系统设计文档，与系统分析设计人员共同设计数据库的具体实现，在这个阶段，考虑更多的是设计出易于理解、存取快捷、性能优化的数据库结构。因此需要系统分析设计人员与数据库设计人员通力合作，共同配合，设计出既能满足软件产品的功能要求，又能符合数据存储与操作要求的数据库。
本文档的编写目的是为了：
指导数据库设计人员与系统分析设计人员，定义设计数据库结构时应遵循的约束条件及设计准则。
指导数据库设计人员与系统分析设计人员进行具体的数据库设计实现。
用规范文件的形式，对数据库设计进行有效的规范管理，使得最终的数据库设计具有良好的风格和统一的结构，且具有设计方便、保密性强、易于维护等特点。
1.2 适用范围
本文档适用于如下对象：
数据库设计人员
系统分析与设计人员
质量管理与测试人员
1.2.1 主要内容
本规范主要内容是公司所有参与数据库设计的人员在进行数据库设计的过程中所应遵循的设计原则，命名约定，应遵循的设计方法及相应的实现机制等，包括数据库设计阶段需完成的各种文档。
1.3 术语定义
DBMS：数据库管理系统，常用的商业DBMS有Oracle, SQL Server, DB2等。
数据库设计：数据库设计是在给定的应用场景下，构造适用的数据库模式，建立数据库及其应用系统，有效存储数据，满足用户信息要求和处理要求。
需求分析阶段：综合各个用户的应用需求(现实世界的需求)。
概念数据模型：概念数据模型以实体-关系(Entity-RelationShip,简称E-R)理论为基础，并对这一理论进行了扩充。它从用户的观点出发对信息进行建模，主要用于数据库概念级别的设计，独立于机器和各DBMS产品。可以用Sybase PowerDesigner工具来建立概念数据模型（CDM）。
逻辑数据模型：将概念数据模型转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式。可以用Sybase PowerDesigner工具直接建立逻辑数据模型（LDM），或者通过CDM转换得到。
物理数据模型：在逻辑数据模型基础上，根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。可以用Sybase PowerDesigner工具直接建立物理数据模型（PDM），或者通过CDM / LDM转换得到。
2 数据库设计原则
按阶段实施并形成该阶段的成果物
一般符合3NF范式要求；兼顾规范与效率
使用公司规定的数据库设计软件工具
命名符合公司标准和项目标准
2.1 设计总体原则
在由系统分析向数据库设计转化时，应以数据库基表为中心，以其它数据库对象为补充，以基表间关系反映功能模块内部的逻辑结构。
设计时应充分考虑产品之间的独立性与封装性。
在进行数据库设计时，应随时进行数据字典的维护。
在进行数据库设计时，数据库设计成员与系统分析设计人员互相配合，共同完成。
在设计初期，以系统分析设计人员为主，优先考虑满足软件的功能需求；在设计的中后期，以数据库设计人员为主，在满足软件功能需求的前提下，从数据存储与操作的角度进行数据库的具体设计。
在具体数据库对象的设计时，优先考虑数据库整体结构设计，再考虑具体数据库对象设计，即在满足整体最优的原则下寻求局部最优。
在具体数据库对象结构的确定过程中，应经过系统分析设计人员与数据库设计人员的共同确认；在对象结构需要更改时，也必须应经过系统分析设计人员与数据库设计人员的共同确认。
数据库对象的编码设计应在数据库设计初始阶段进行确定，以保证整个数据库设计的一致性。
3 数据库设计目标
规范性：一般符合3NF范式要求，减少冗余数据。
高效率：兼顾规范与效率，适当进行反范式化，满足应用系统的性能要求。
紧凑性：例如能用char(10)的就不要用char(20)，提高存储的利用率和系统性能，但同时也要兼顾扩展性和可移植性。
易用性：数据库设计清晰易用，用户和开发人员均能容易地理解。
4 数据库设计规范
数据库设计过程包括如下阶段：数据分析阶段、概念设计阶段、逻辑设计阶段、物理设计阶段、实施与运行维护阶段。如下图：
4.1 数据分析阶段
在数据分析阶段（一般在项目的需求分析或者系统设计阶段进行），应注意搜集和分析数据相关的内容，并形成相关成果物，包括数据流图和数据字典等，以此作为数据库设计的基础和依据。数据流图从数据传递和加工的角度，以图形的方式刻画数据流从输入到输出的移动变换过程。数据字典则对数据流图中的各种成分进行详细说明，作为数据流图的细节补充。数据字典一般应包括对数据项，数据结构、数据存储和数据处理的说明。具体表现如下：
需求收集和分析，结果得到数据字典描述的数据需求(和数据流图描述的处理需求)。
需求分析的重点：调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性与完整性要求。
需求分析的方法：调查组织机构情况、各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界。
常用的调查方法有： 跟班作业、开调查会、请专人介绍、询问、设计调查表请用户填写、查阅记录。
分析和表达用户需求的方法主要包括自顶向下和自底向上两类方法。自顶向下的结构化分析方法(Structured Analysis，简称SA方法)从最上层的系统组织机构入手，采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。
数据流图表达了数据和处理过程的关系。系统中的数据则借助数据字典(Data Dictionary，简称DD)来描述。
4.1.1 数据流程图示例
4.1.2 数据字典 示例
4.2 概念设计阶段
在数据分析的基础上，使用E-R模型技术，将现实世界中的客观对象抽象为实体和关系，形成概念数据模型（CDM）。CDM可以从更高层次地理解系统、以及技术人员可用于和用户交流，和用户达成共识，所以必须完成这一阶段的工作。
4.2.1 CDM示例
通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。
概念模型用于信息世界的建模。概念模型不依赖于某一个DBMS支持的数据模型。概念模型可以转换为计算机上某一DBMS支持的特定数据模型。
概念模型特点：
(1) 具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识。
(2) 应该简单、清晰、易于用户理解，是用户与数据库设计人员之间进行交流的语言。
概念模型设计的一种常用方法为IDEF1X方法，它就是把实体-联系方法应用到语义数据模型中的一种语义模型化技术，用于建立系统信息模型。
使用IDEF1X方法创建E-R模型的步骤如下所示:
4.2.2 第一步：初始化工程
这个阶段的任务是从目的描述和范围描述开始，确定建模目标，开发建模计划，组织建模队伍，收集源材料，制定约束和规范。收集源材料是这阶段的重点。通过调查和观察结果，业务流程，原有系统的输入输出，各种报表，收集原始数据，形成了基本数据资料表。
4.2.3 第二步：定义实体
实体集成员都有一个共同的特征和属性集，可以从收集的源材料——基本数据资料表中直接或间接标识出大部分实体。根据源材料名字表中表示物的术语以及具有“代码”结尾的术语，如客户代码、代理商代码、产品代码等将其名词部分代表的实体标识出来，从而初步找出潜在的实体，形成初步实体表。
4.2.4 第三步：定义联系
通过引入交叉实体除去上一阶段产生的非确定关系，然后从非交叉实体和独立实体开始标识侯选码属性，以便唯一识别每个实体的实例，再从侯选码中确定主码。为了确定主码和关系的有效性，通过非空规则和非多值规则来保证，即一个实体实例的一个属性不能是空值，也不能在同一个时刻有一个以上的值。找出误认的确定关系，将实体进一步分解，最后构造出IDEF1X模型的键基视图(KB图)。
4.2.5 第四步：定义码
通过引入交叉实体除去上一阶段产生的非确定关系，然后从非交叉实体和独立实体开始标识侯选码属性，以便唯一识别每个实体的实例，再从侯选码中确定主码。为了确定主码和关系的有效性，通过非空规则和非多值规则来保证，即一个实体实例的一个属性不能是空值，也不能在同一个时刻有一个以上的值。找出误认的确定关系，将实体进一步分解，最后构造出IDEF1X模型的键基视图(KB图)。
4.2.6 第五步：定义属性
从源数据表中抽取说明性的名词开发出属性表，确定属性的所有者。定义非主码属性，检查属性的非空及非多值规则。此外，还要检查完全依赖函数规则和非传递依赖规则，保证一个非主码属性必须依赖于主码、整个主码、仅仅是主码。以此得到了至少符合关系理论第三范式的改进的IDEF1X模型的全属性视图。
4.2.7 第六步：定义其他对象和规则
定义属性的数据类型、长度、精度、非空、缺省值、约束规则等。定义触发器、存储过程、视图、角色、同义词、序列等对象信息。
4.3 逻辑结构设计阶段
将E-R模型转换DBMS支持的数据模型，包括关系模型、网状模型、层次模型、对象模型等等。常用的DBMS是关系数据库，因此要转换为关系模型。具体表现如下：
将概念结构转换为某个DBMS所支持的数据模型(例如关系模型)，并对其进行优化。设计逻辑结构应该选择最适于描述与表达相应概念结构的数据模型，然后选择最合适的DBMS。
将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式,这种转换一般遵循如下原则：一个实体型转换为一个关系模式。实体的属性就是关系的属性。实体的码就是关系的码。
数据模型的优化，确定数据依赖，消除冗余的联系，确定各关系模式分别属于第几范式。确定是否要对它们进行合并或分解。一般来说将关系分解为3NF的标准，即：
表内的每一个值都只能被表达一次。
表内的每一行都应该被唯一的标识(有唯一键)。
表内不应该存储依赖于其他键的非键信息。
可以用Sybase PowerDesigner直接将CDM转换为LDM。
从理论上来说，转换过程一般有7个步骤：
转换强实体
转换弱实体
转换1：1关系
转换1：N关系
转换M：N关系
转换多值属性（Multi-Valued Attribute）
转换n元关系（n-ary Relation）
4.3.1 E-R模型和关系模型的映射如下
4.4 数据库物理设计阶段
为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)。根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。
4.4.1 物理数据库设计主要包括
使用逻辑模型建立一系列的表（如果在CDM和LDM中使用中文，应在转换后使用英文）；
使用索引以提升性能；
实施约束和安全限制；
对数据进行分区和分布式处理等。
4.5 数据库实施阶段
运用DBMS提供的数据语言(例如SQL)及其宿主语言(例如C)，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。 数据库实施主要包括以下工作：用DDL定义数据库结构、组织数据入库 、编制与调试应用程序、数据库试运行 ，(Data Definition Language(DDL数据定义语言)用作开新数据表、设定字段、删除数据表、删除字段，管理所有有关数据库结构的东西)
Create (新增有关数据库结构的东西，属DDL)
Drop (删除有关数据库结构的东西，属DDL)
Alter (更改结构，属DDL)
4.6 数据库运行与维护阶段
在数据库系统运行过程中必须不断地对其进行评价、调整与修改。内容包括：数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。
4.7 建模工具的使用
为加快数据库设计速度，目前有很多数据库辅助工具(CASE工具)，如Rational公司的Rational Rose，CA公司的Erwin和Bpwin，Sybase公司的PowerDesigner以及Oracle公司的oracle Designer等。
ERwin主要用来建立数据库的概念模型和物理模型。它能用图形化的方式，描述出实体、联系及实体的属性。ERwin支持IDEF1X方法。通过使用ERwin建模工具自动生成、更改和分析IDEF1X模型，不仅能得到优秀的业务功能和数据需求模型，而且可以实现从IDEF1X模型到数据库物理设计的转变。ERwin工具绘制的模型对应于逻辑模型和物理模型两种。在逻辑模型中，IDEF1X工具箱可以方便地用图形化的方式构建和绘制实体联系及实体的属性。在物理模型中，ERwin可以定义对应的表、列，并可针对各种数据库管理系统自动转换为适当的类型。
设计人员可根据需要选用相应的数据库设计建模工具。例如需求分析完成之后，设计人员可以使用Erwin画ER图，将ER图转换为关系数据模型，生成数据库结构;画数据流图，生成应用程序。
5 数据库设计技巧
5.1 设计数据库之前(需求分析阶段)
1) 理解客户需求，包括用户未来需求变化。
2) 了解企业业务类型，可以在开发阶段节约大量的时间。
3) 重视输入(要记录的数据)、输出(报表、查询、视图)。
4) 创建数据字典和ER 图表
数据字典(Data Dictionary，简称DD)是各类数据描述的集合，是关于数据库中数据的描述，即元数据，不是数据本身。(至少应该包含每个字段的数据类型和在每个表内的主外键)。
数据项描述: 数据项名，数据项含义说明，别名，数据类型，长度，取值范围，取值含义，与其他数据项的逻辑关系
数据结构描述: 数据结构名，含义说明，组成:[数据项或数据结构]
数据流描述: 数据流名，说明，数据流来源，数据流去向， 组成:[数据结构]，平均流量，高峰期流量
数据存储描述: 数据存储名，说明，编号，流入的数据流，流出的数据流，组成:[数据结构]，数据量，存取方式
处理过程描述: 处理过程名，说明，输入:[数据流]，输出:[数据流]，处理:[简要说明]
ER 图表和数据字典可以让任何了解数据库的人都明确如何从数据库中获得数据。ER图对表明表之间关系很有用，而数据字典则说明了每个字段的用途以及任何可能存在的别名。对SQL 表达式的文档化来说这是完全必要的。
5) 定义标准的对象命名规范
数据库各种对象的命名必须规范。
5.2 表和字段的设计(数据库逻辑设计)
5.2.1 表设计原则
1) 标准化和规范化
数据的标准化有助于消除数据库中的数据冗余。标准化有好几种形式，但Third Normal Form(3NF)通常被认为在性能、扩展性和数据完整性方面达到了最好平衡。简单来说，遵守3NF 标准的数据库的表设计原则是：“One Fact in One Place”即某个表只包括其本身基本的属性，当不是它们本身所具有的属性时需进行分解。表之间的关系通过外键相连接。它具有以下特点：有一组表专门存放通过键连接起来的关联数据。
2) 数据驱动
采用数据驱动而非硬编码的方式，许多策略变更和维护都会方便得多，大大增强系统的灵活性和扩展性。
举例，假如用户界面要访问外部数据源(文件、XML 文档、其他数据库等)，不妨把相应的连接和路径信息存储在用户界面支持的表里。如果用户界面执行工作流之类的任务(发送邮件、打印信笺、修改记录状态等)，那么产生工作流的数据也可以存放在数据库里。角色权限管理也可以通过数据驱动来完成。事实上，如果过程是数据驱动的，你就可以把相当大的责任推给用户，由用户来维护自己的工作流过程。
3) 考虑各种变化
在设计数据库的时候考虑到哪些数据字段将来可能会发生变更。
4) 表名、报表名和查询名的命名规范
(采用前缀命名)检查表名、报表名和查询名之间的命名规范。你可能会很快就被这些不同的数据库要素的名称搞糊涂了。你可以统一地命名这些数据库的不同组成部分，至少你应该在这些对象名字的开头用 Table、Query 或者 Report 等前缀加以区别。如果采用了 Microsoft Access，你可以用 qry、rpt、tbl 和 mod 等符号来标识对象(比如 tbl_Employees)。用 sp_company 标识存储过程，用 udf_ (或者类似的标记)标识自定义编写的函数。
5.2.2 字段设计原则
1) 每个表中都应该添加的3 个有用的字段。
dRecordCreationDate，在SQL Server 下默认为GETDATE()•
sRecordCreator，在SQL Server 下默认为NOT NULL DEFAULT USER•
nRecordVersion，记录的版本标记;有助于准确说明记录中出现null 数据或者丢失数据的原因时效性数据应包括“最近更新日期/时间”字段。时间标记对查找数据问题的原因、按日期重新处理/重载数据和清除旧数据特别有用。
2) 对地址和电话采用多个字段
描述街道地址就短短一行记录是不够的。Address_Line1、Address_Line2 和Address_Line3 可以提供更大的灵活性。还有，电话号码和邮件地址最好拥有自己的数据表，其间具有自身的类型和标记类别。
3) 表内的列[字段]的命名规则(采用前缀/后缀命名)、采用有意义的字段名
对列[字段]名应该采用标准的前缀和后缀。如键是数字类型：用 _N 后缀;字符类型：_C 后缀;日期类型：_D 后缀。再如，假如你的表里有好多“money”字段，你不妨给每个列[字段]增加一个 _M 后缀。
假设有两个表： Customer 和 Order。Customer 表的前缀是 cu_，所以该表内的子段名如下：cu_name_id、cu_surname、cu_initials 和cu_address 等。Order 表的前缀是 or_，所以子段名是：or_order_id、or_cust_name_id、or_quantity 和 or_description 等。 这样从数据库中选出全部数据的 SQL 语句可以写成如下所示：
Select * From Customer, Order Where cu_surname = "MYNAME" ; and cu_name_id = or_cust_name_id and or_quantity = 1 在没有这些前缀的情况下则写成这个样子(用别名来区分)： Select * From Customer, Order Where Customer.surname = "MYNAME" ; and Customer.name_id = Order.cust_name_id and Order.quantity = 1
第 1 个 SQL 语句没少键入多少字符。但如果查询涉及到 5 个表乃至更多的列[字段]你就知道这个技巧多有用了。
5) 选择数字类型和文本类型的长度应尽量充足
假设客户ID 为10 位数长。那你应该把数据库表字段的长度设为12 或者13 个字符长。但这额外占据的空间却无需将来重构整个数据库就可以实现数据库规模的增长了。
6) 增加删除标记字段
在表中包含一个“删除标记”字段，这样就可以把行标记为删除。在关系数据库里不要单独删除某一行;最好采用清除数据程序而且要仔细维护索引整体性。
7) 提防大小写混用的对象名和特殊字符
采用全部大写而且包含下划符的名字具有更好的可读性(CUSTOMER_DATA)，绝对不要在对象名的字符之间留空格。
8) 小心保留词
要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突，比如，用 DESC 作为说明字段名。后果可想而知!DESC 是 DESCENDING 缩写后的保留词。表里的一个 SELECT * 语句倒是能用，但得到的却是一大堆毫无用处的信息。
9) 保持字段名和类型的一致性
在命名字段并为其指定数据类型的时候一定要保证一致性。假如字段在表1中叫做“agreement_number”，就别在表2里把名字改成“ref1”。假如数据类型在表1里是整数，那在表2里可就别变成字符型了。当然在表1(ABC)有处键ID，则为了可读性，在表2做关联时可以命名为ABC_ID。
10) 避免使用触发器
触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。
5.2.3 选择键和索引
(一)深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字;同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
(二)何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
(三)结合实际，谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。
在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条)：
(1)仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title from tgongwen用时：128470毫秒(即：128秒)
(2)在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())用时：53763毫秒(54秒)
(3)将聚合索引建立在日期列(fariqi)上：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())用时：2423毫秒(2秒)
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
得出以上速度的方法是：在各个select语句前加：
declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())2、只要建立索引就能显著提高查询速度
事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。
从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引(compound index)。
很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗?带着这个问题，我们来看一下以下的查询速度(结果集都是25万条数据)：(日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列)
(1)select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5'
查询速度：2513毫秒
(2)select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度：2516毫秒
(3)select gid,fariqi,neibuyonghu,title from Tgongwen
where neibuyonghu='办公室'
查询速度：60280毫秒从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
5.2.4 数据库完整设计
1) 完整性实现机制：
实体完整性：主键
参照完整性：
父表中删除数据：级联删除;受限删除;置空值
父表中插入数据：受限插入;递归插入
父表中更新数据：级联更新;受限更新;置空值
DBMS对参照完整性可以有两种方法实现：外键实现机制(约束规则)和触发器实现机制用户定义完整性：
NOT NULL;CHECK;触发器
2) 用约束而非商务规则强制数据完整性
采用数据库系统实现数据的完整性。这不但包括通过标准化实现的完整性而且还包括数据的功能性。不要依赖于商务层保证数据完整性;它不能保证表之间(外键)的完整性所以不能强加于其他完整性规则之上。如果你在数据层确实采用了约束，你要保证有办法把更新不能通过约束检查的原因采用用户理解的语言通知用户界面。
3) 强制指示完整性
在有害数据进入数据库之前将其剔除。激活数据库系统的指示完整性特性。这样可以保持数据的清洁而能迫使开发人员投入更多的时间处理错误条件。
4) 使用查找控制数据完整性
控制数据完整性的最佳方式就是限制用户的选择。只要有可能都应该提供给用户一个清晰的价值列表供其选择。这样将减少键入代码的错误和误解同时提供数据的一致性。某些公共数据特别适合查找：国家代码、状态代码等。
5) 采用视图
为了在数据库和应用程序代码之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问数据表。这样做还等于在处理数据库变更时给你提供了更多的自由。
6) 分布式数据系统
对分布式系统而言，在你决定是否在各个站点复制所有数据还是把数据保存在一个地方之前应该估计一下未来 5 年或者 10 年的数据量。当你把数据传送到其他站点的时候，最好在数据库字段中设置一些标记，在目的站点收到你的数据之后更新你的标记。为了进行这种数据传输，请写下你自己的批处理或者调度程序以特定时间间隔运行而不要让用户在每天的工作后传输数据。本地拷贝你的维护数据，比如计算常数和利息率等，设置版本号保证数据在每个站点都完全一致。
7) 关系
如果两个实体之间存在多对一关系，而且还有可能转化为多对多关系，那么你最好一开始就设置成多对多关系。从现有的多对一关系转变为多对多关系比一开始就是多对多关系要难得多。
8) 给数据保有和恢复制定计划
考虑数据保存策略并包含在设计过程中，预先设计你的数据恢复过程。采用可以发布给用户/开发人员的数据字典实现方便的数据识别同时保证对数据源文档化。编写在线更新来“更新查询”供以后万一数据丢失可以重新处理更新。
9) 用存储过程让系统做重活
提供一整套常规的存储过程来访问各组以便加快速度和简化客户程序代码的开发。数据库不只是一个存放数据的地方，它也是简化编码之地。
5.2.5 其他设计技巧
1) 避免使用触发器
触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。
2) 使用常用英语(或者其他任何语言)而不要使用编码
在创建下拉菜单、列表、报表时最好按照英语名排序。假如需要编码，可以在编码旁附上用户知道的英语。
3) 保存常用信息
让一个表专门存放一般数据库信息非常有用。在这个表里存放数据库当前版本、最近检查/修复(对Access)、关联设计文档的名称、客户等信息。这样可以实现一种简单机制跟踪数据库，当客户抱怨他们的数据库没有达到希望的要求而与你联系时，这样做对非客户机/服务器环境特别有用。
4) 包含版本机制
在数据库中引入版本控制机制来确定使用中的数据库的版本。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。把版本信息直接存放到数据库中更为方便。
5) 编制文档
对所有的快捷方式、命名规范、限制和函数都要编制文档。
采用给表、列、触发器等加注释的 数据库工具。对开发、支持和跟踪修改非常有用。
对数据库文档化，或者在数据库自身的内部或者单独建立文档。这样，当过了一年多时间后再回过头来做第2 个版本，犯错的机会将大大减少。
6) 测试、测试、反复测试
建立或者修订数据库之后，必须用用户新输入的数据测试数据字段。最重要的是，让用户进行测试并且同用户一道保证选择的数据类型满足商业要求。测试需要在把新数据库投入实际服务之前完成。
7) 检查设计
在开发期间检查数据库设计的常用技术是通过其所支持的应用程序原型检查数据库。换句话说，针对每一种最终表达数据的原型应用，保证你检查了数据模型并且查看如何取出数据
6 数据库命名规范
6.1 命名准则
Pascal casing
此规范要求大写每个单词的首字母。（如TestCounter）
Camel casing
此规范要求大写每个单词的首字母除了第一个单词，如testCounter。
Upper case
仅仅对于由一个或两个字母的缩写组成的标识符，我们用Upper case，那些由3个或更多字符组成的标识符应该用Pascal Casing。
拼音大写命名
这个规范要求使用汉语拼音，拼音所有字母需要大写。
拼音全拼命名
这个规范要求使用汉语拼音，拼音首写字母需要大写。
拼音缩写命名
这个规范要求使用汉语拼音首写字母，而且字母大写。
除了循环变量外，其他的命名一定要具有含义，可以通过命名直接理解其作用。
如果表示集合，需要使用复数形式，如-s，-list，-collection。
6.1.1 数据库命名
命名组成
[产品名称]_[类型名称]。
命名分隔
以点号（_）来区分组成部分。
拼写规范
命名使用Pascal casing。
6.1.2 表命名
命名组成
[模块名称]_[表类型名称]_[表定义]
命名分隔
以点号（_）来区分组成部分。
模块命名
命名使用Upper case，参考请见附表1
表类型命名
命名使用Upper case，参考请见附表2
表定义命名
命名使用拼音缩写命名或者Pascal casing，参考请见附表3
6.1.3 字段命名
拼写规范
命名使用拼音缩写命名或者Pascal casing。
字段命名一定要具有一定含义，便于理解和维护。
6.1.4 主键命名
命名组成
[PK]_[表定义]
前缀（PK_）。
6.1.5 索引命名
命名组成
[INDEX]_[索引字段名]_[表定义]
前缀（INDEX_）。
6.1.6 外键命名
命名组成
[FK]_[主表表定义]_[从表表定义]
前缀（FK_）。
6.1.7 视图命名
命名组成
[VW]_[视图定义]
前缀（VW_）。
6.1.8 存储过程命名
命名组成
[P]_[存储过程定义]
拼写规范
命名使用Pascal casing。
前缀（P_）。
存储过程定义需要有一定含义，一般动词表示行为在前，名词表示对象在后。具体常用动词名参照附表4。
6.1.9 自定义函数命名
命名组成
[FN]_[函数定义]
拼写规范
命名使用Pascal casing。
前缀（FN_）。
函数定义需要有一定含义，一般动词表示行为在前，名词表示对象在后。具体常用动词名参照附表4。
6.1.10 触发器命名
命名组成
[TRI]_[触发器定义]
拼写规范
命名使用Pascal casing。
前缀（TRI_）。
尽量不要使用。
6.1.11 变量/参数命名
拼写规范
命名使用Pascal casing。
可以准确表达变量/参数含义。
6.1.12 游标命名
命名组成
[CSR]_[游标定义]
拼写规范
命名使用Pascal casing。
使用CSR_为前缀；
一般游标都是获取结果集中一行记录中的部分字段，所以基本上都用动词Get作为表意动词。
6.1.13 别名命名
别名一定要表示具体含义，一律不准使用A，B，C等毫无含义的别名。
档案表别名使用表定义部分；
单据表使用相同的别名比如（ZB， MI， MX），这样相同业务逻辑之间复制代码修改量比较小；
字段别名使用字段的拼音缩写命名。
6.2 文件夹与文件规则
6.2.1 物理位置说明
数据库物理文件一般不要存放在C盘，因为系统重装对C盘破坏最大；
数据库物理文件一般不要存放在SQL SERVER默认安装目录；
TMP库一般放置在C盘，可以提高访问效率。
6.2.2 文件夹
数据库物理文件夹以产品名称命名；
备份文件夹以BAK命名；
6.2.3 文件
数据库物理文件与数据库名同名；
备份文件名组成[数据库名]_[年月日]_[BAK]；
6.3 注释说明
（注释很重要，书写要规范）
6.3.1 注释准则
单行注释
使用（--）开头并延续到本行的结尾；
使用语句尽可能精炼，并把意思描述清楚；
起始位置（--）与被注释的代码相同（缩进级别相同），其长度不应超过被的代码长度。
块注释
带分隔符的注释以字符 /* 开头,以字符 */ 结束。带分隔符的注释可以跨多行；
6.3.2 历史注释
文件修改注释
使用块注释；
说明文件的功能；
说明记录文件的创建和重大修改功能。
部分修改注释
使用行注释；
修改现有代码需要注明修改人，修改时间和修改原因；
删除现有代码需要注明删除人，删除时间和删除原因。
6.3.3 参数/变量注释
参数/变量注释
在参数/变量后注释；
使用单行注释；
描述参数/变量的含义；
如果参数/变量值有枚举含义，需要列举出来逐个说明；
6.3.4 语句注释
语句注释
在语句前注释；
使用单行注释；
描述清楚语句的逻辑含义。
6.3.5 存储过程/自定义函数/触发器注释
功能注释
使用块注释
需要描述清楚该过程/函数/触发器所实现的功能含义，务必交待清楚，不要节约墨水。
修改注释
注明创建/修改人；
注明创建/修改日期；
注明修改内容；
参数列表注释
使用单行注释；
枚举类型值，需要交待清楚每种类型值的具体含义；
语句注释
使用单行注释；
具体参见存储过程实例
6.4 基本格式
6.4.1 大小写规则
SQL关键字，需要大写；
系统函数名，需要大写；
系统对象（系统表，系统字段），按照系统定义的大小写执行（数据库里怎么定义就怎么引用）；
数据库对象名称，按照实际定义的大小写执行（数据库里怎么定义就怎么引用）；
6.4.2 换行
BEGIN/END独占一行：
参数或者字段过多，创建或声明时，每个完整元素独占一行；
每行语句不超过80个字符；
FROM子句独占一行；
多余一个的JOIN子句独占一行；
CASE语句中，ELSE或者多余一个的WHEN子句独占一行；
INTO子句独占一行；
WHERE子句独占一行；
GROUP BY 子句独占一行；
ORDER BY 子句独占一行；
如果一行写不完换行时，需要确保每行逻辑完整性。
如果一行写不完换行时，在关键字前换行。
如果一行写不完换行时，在逗号前换行。
如果一行写不完换行时，在运算符前换行。
6.4.3 缩进
使用四个空格缩进；
不要使用TAB缩进；
相同逻辑关键字/运算符号/标点符号缩进保持一致；
6.4.4 空格
赋值空格，前后空格；
运算空格，前后空格；
冒号空格，前后空格；
逗号空格，前不空格，后空格；
分号空格，前不空格，后空格；
小括号，不空格；
中括号，不空格；
单引号，双引号，不空格；
6.4.5 空行
代码块之间（END后）空行；
逻辑块之间空行；
其他地方一律不准空行；
不允许出现两行及两行以上空行；
6.5 语句样例
6.5.1 SELECT语句
不要使用类似SELECT *这样的语句，一定要将字段名详细列出来
SELECT <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>
, <字段>
INTO <新表名>
FROM <表名>
WHERE <条件> AND <条件> AND <条件> AND <条件> AND <条件> AND <条件> AND <条件>
AND <条件>
GROUP BY <分组条件>
HAVING <条件>
ORDER BY <排序规则> [ ASC | DESC ];6.5.2 JOIN语句
SELECT <字段列表>
FROM <表名1> INNER JOIN <表名2> ON <关联条件>
INNER JOIN <表名3> ON <关联条件>
WHERE <条件>;6.5.3 子查询
SELECT <字段列表>
FROM (SELECT <字段列表>
FROM <表名>
WHERE <条件>
) AS <表别名>;6.5.4 INSERT语句
值插入：
INSERT INTO <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>
, <字段>
VALUES (<列值>, <列值>, <列值>, <列值>, <列值>, <列值>, <列值>, <列值>, <列值>
, <列值>);
查询插入：
INSERT INTO <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>
, <字段>
SELECT <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>, <字段>
, <字段>
FROM <表名>;6.5.5 UPDATE语句
UPDATE <被更新的表名>
SET <被更新的列> = <列值>
FROM <关联表名>
WHERE <条件>;6.5.6 CREATE语句
CREATE TABLE [dbo].[表名]
(
字段1 类型 --注释
, 字段2 类型 --注释
, 字段3 类型 --注释
, 字段4 类型 --注释
, 字段5 类型 --注释
);6.5.7 DELETE语句
DELETE FROM <表名>
WHERE <条件>;6.5.8 CASE语句
CASE WHEN <条件表达式> THEN <运算式>
…
WHEN <条件表达式> THEN <运算式>
ELSE <运算式>
END AS <别名>6.5.9 IF语句
IF <条件表达式>
BEGIN
<命令行或程序块>
END
ELSE IF <条件表达式>
BEGIN
<命令行或程序块>
END;6.5.10 WHILE语句
WHILE <条件表达式>
BEGIN
<命令行或程序块>
[BREAK]
[CONTINUE]
…
<命令行或程序块>
END;6.5.11 EXISTS语句
--肯定形式
IF EXISTS(SELECT 1 FROM <表名> WHERE <条件>)
BEGIN
<命令行或程序块>
END;
--否定形式
IF NOT EXISTS(SELECT 1 FROM <表名> WHERE <条件>)
BEGIN
<命令行或程序块>
END;6.5.12 游标语句
DECLARE 游标名 CURSOR FOR
SELECT 字段
FROM 表名;
OPEN 游标名;
FETCH NEXT FROM 游标名
INTO 参数列表;
WHILE @@FETCH_STATUS = 0
BEGIN
程序块
FETCH NEXT FROM 游标名
INTO 参数列表;
END;
CLOSE 游标名;
DEALLOCATE 游标名;6.5.13 变量声明
DECLARE @RETURN INT = 0 --返回值 1：成功；：失败；
, @SQL VARCHAR(MAX) = '' --执行语句
, @BillCode VARCHAR(50) = '' --单据编号6.5.14 变量赋值
--单参数赋值
SET <参数> = <值>
--多参数赋值
SELECT <参数> = <值>, <参数> = <值>, <参数> = <值>, <参数> = <值>, <参数> = <值>
, <参数> = <值>, <参数> = <值>, <参数> = <值>6.5.15 存储过程格式
参数列表必须有默认值，
/*
功 能：功能说明
修改说明：Create by CK on 2011-01-01
Modify by CK on 2011-01-15 增加ipos同步功能
*/
CREATE PROCEDURE [dbo].[存储过程名]
<参数> 类型= 默认值 --注释
, <参数> 类型= 默认值 --注释
, <参数> 类型= 默认值 --注释
, <参数> 类型= 默认值 --注释
, <返回值> 类型 OUTPUT --注释;返回值含义
AS
--语句注释
语句块6.5.16 表创建/升级脚本
表创建与升级脚本保持一致
IF NOT EXISTS (SELECT 1
FROM sys.objects
WHERE object_id = OBJECT_ID(N'表名') AND type in (N'U'))
BEGIN
创建表
END
GO
--修改人，修改时间，修改内容
修改语句
--修改人，修改时间，修改内容
修改语句
6.5.17 存储过程脚本
IF EXISTS (SELECT 1
FROM sysobjects
WHERE ID = OBJECT_ID(N'存储过程名')
AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE 存储过程名
END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
创建存储过程语句
6.5.18 视图脚本
IF EXISTS (SELECT 1
FROM sysobjects
WHERE ID = OBJECT_ID(N'视图名') AND type = 'V')
BEGIN
DROP VIEW 视图名
END
GO
6.5.19 索引脚本
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT 1
FROM SYS.INDEXES
WHERE NAME = '索引名' AND type = '1')
BEGIN
DROP INDEX 索引名 ON 表名
END
GO
CREATE UNIQUE CLUSTERED INDEX 索引名 ON 表名
GO
