数据库设计规范

1.简介

数据库设计是指对一个给定的应用环境，构造优的数据库模式，建立数据库及其他应用系统，使之能有效地存储数据，满足各种用户的需求。数据库设计过程中命名规范很是重要，命名规范合理的设计能够省去开发人员很多时间去区别数据库实体。

近也因为工作需要所以整理出了这个word文档，望大家指正。

2数据库设计

数据库规划→需求分析→数据库设计→应用程序设计→实现→测试→运行于维护

2.1数据库规划

定义数据库应用系统的主要目标，定义系统特定任务，包括工作量的估计、使用资源、和需求经费，定义系统的范围以及边界。

2.2需求分析

2.1.1需求分析步骤与成果

涉及人员：用户和分析人员

任务：对现实世界要处理的对象进行详细的调查，收集基础数据及处理方法，在用户调查的基础上通过分析，逐步明确用户对系统的需求，包括信息的要求及处理的要求。

方法与步骤：1.通过与用户的调查，对用户的信息需求进行收集。

2.在收集数据的同时，设计人员要对其进行加工和整理，以数据字典和数据流图的形式描述出来，并以设计人员的角度向用户讲述信息，根据用户的反馈加以修改并确定（该过程是反复的过程）

成果：数据流图，数据字典，各种说明性表格，统计输出表以及系统功能结构图。

2.1.2数据流图基本元素与数据流图

外部实体：存在于软件系统之外的人员或组织（正方形或立方体表示）。

加工：数据处理，表示输入数据在此进行变换，产生输出数据（圆角巨型或圆形表示）。

数据流：表示流动着的数据（箭头线表示）。

数据存储：用来表示要存储的数据（开门矩形或两条平行横线表示）。

订单处理系统顶层流程图：

0层数据流图：

2.3数据库设计

2.3.1概念结构设计

• 对事务加以抽象以E-R图的形式描述出来
• E-R图（实体联系图）：包括实体，联系，属性

实体：现实中的事物例如，学生，老师

联系：两个实体之间的关系，1:1、1：N、M:N三种关系

属性：实体所具有的属性，例如 学生的学号、姓名、性别等

例如：一个学生属于一个班级，一个班级拥有多名学生，E-R图如下

网上购物系统E-R图，该系统数据之间存在下列约束

1. 一个客户（编号）可以拥有多个订单，每个订单仅属于一个客户。
2. 一个订单（编号）可以包含多个订购细目，每个订购细目只属于一个订单。
3. 一个商品可以出现多个订购细目中，一个订购细目只包含多个商品。
4. 一个商品类别可以包含多种商品，一种商品只属于一个商品类别。

图2.2

2.3.2逻辑结构设计

2.3.2.1E-R图转换成关系模式

•  将E-R图转换成关系模式

将每个实体转换成一个关系模式，实体的属性即关系模式的属性，实体的标识即关系模式的键。

•  根据规则合并E-R图中的1:1,1:N,M:N之间的联系

1. 若实体的联系是(1:1)，则可以将两个实体转换成两个关系模式，任意一个关系模式的属性中加入另一个关系模式的主键（作为外键）和联系自身的属性
2. 若实体间的联系是一对多（1：n），则将n端的实体类型转换成关系模式中加入1端实体类型的主键（作为外键）和联系类型的属性。
3. 若实体间的联系是多对多（m:n），则将联系类型也转换成关系模式，其属性为2实体类型的主键（作为外键）加上联系类型自身的属性，而该关系模式的主键为2端实体主键的组合。
4. 若关系模式是1：1：1的关系，转换原则同1：1
5. 若关系模式是1：1：n的联系，转换原则同1：n
6. 若关系模式是1：n：m的联系，则可以将联系类型也转换成关系模式，其属性为m端和n端实体类型的主键（作为外键）加上联系类型自身的属性，而关系模式的主键为n和m端实体主键的组合
7. 若关系模式是n:m:p的联系，转换规则同m:n

根据E-R图实体之间的联系可以转换成以下关系模式：

客户（客户编号，姓名，电话，E-mail）。关系的主键：客户编号；外键：无

订单（订单编号，订购时间，客户编号）。关系的主键：订单编号；外键：客户编号

订购细目（订购明细编号，订购数量，支付金额，订单编号）。关系主键：订购明细编号；外键：订单编号。

出现（订购明细编号，商品编号，类型）。关系的主键：订购明细编号，商品编号；外键：订购明细编号，商品编号。

商品：（商品编号，商品名称，单价，生产日期，商品类别号，商品类别名）。关系的主键：商品编号；外键：无

在关系模式设计中可能会出现以下几个问题：数据冗余、数据修改不一致、数据插入异常、数据删除异常，所以提出范式的要求，目的就是低限度地冗余，避免插入、删除、修改异常。

2.3.2.2范式

主属性：包含键的所有属性。

•  关系模式要求达到4NF （减少冗余，消除操作异常）

范式（1NF）：若关系模式R的每一个分量是不可分的数据项，则关系模式属于范式。即每个属性都是不可拆分的.

第二范式(2NF)：R属于1NF,且每一个非主属性完全依赖于键（没有部分依赖），则R属于2NF

例如：选课关系(学号，课程号，成绩，学分)

该关系的主键是（学号，课程号），但是课程号→学分，所以学分属性部分依赖于主键，即关系部满足第二范式，可以拆分为（学号，课程号，成绩），（课程号，学分）两个关系

第三范式(3NF)：R属于2NF,且每个非主属性即不部分依赖于码，也不传递依赖于码

例如：学生关系（学号，姓名，所属系，系地址）

该关系的主键是:学号

学号→所属系，所属系→学号，所属系→系地址；根据函数的依赖公理，系地址传递函数依赖于学号，即关系不满足第三范式，可以拆分关系为（学号，姓名，所属系），（所属系，系地址）

如果不拆分会存在数据修改异常，比如该学生的换了系，修改了所属系，但是系地址没有修改，这样就造成了修改异常

 BCNF：R属于3NF,且不存在主属性对码的部分和传递函数依赖

例如：关系R（零件号，零件名，厂商名），如果设定每种零件号只有一个零件名，但不同的的零件号可以有相同的零件名，每种零件可以有多个厂商生产，但每家厂商生产的零件应有不同的零件名。这样可以得到：

零件号→零件名，（厂商名，零件名）→零件号

所以主属性包括（零件号，厂商名，零件名），但是“零件名”传递依赖于码“厂商名，零件名”，所以关系R不满足BCNF，当一个零件由多个生产厂商生产时，由于零件号只有一个而零件名根据厂商不同而又多个，零件名与零件号之间的联系将多次重复，带来数据冗余和操作异常现象

可以将关系分解为（零件号，厂商名），（零件号，零件名）

4NF：关系模式R属于1NF,若对于R的每个非平凡多值依赖X→→Y且Y不包含于X时，X必含码，则R属于4NF

5NF:对关系进行投影，消除关系中不是由候选码所蕴含的连接依赖

对于上面的商品关系，由于关系的主键是商品编号，而商品类别号→商品类别名

所以商品关系部满足第三范式，非主属性商品类别名传递依赖于商品编号，会存在数据冗余，数据修改异常问题。将商品关系分解为：

商品（商品编号，商品名称，单价，生产日期，商品类别号）

商品类别（商品类别号，商品类别名）

2.3.3物理结构设计

为一个给定的逻辑数据模型设计一个合适应用要求的物理结构的过程

•  数据库的建立
•  数据表的建立
•  索引的建立
•  视图的建立
•  触发器的建立
•  存储过程设计
• 用户自定义函数设计
•  对关系模式的数据项加以约束，如检查约束、主键约束、参照完整性约束以保证数据正确性

2.4应用程序设计

采用语言以结构化设计方法或面向对象方法进行设计

2.5系统实现

3.优化策略

3.1.查询优化策略

1. 尽可能地减少多表查询或建立物化视图
2. 只检索需要的列
3. 用带IN的条件字句等级替换or字句
4. 关联查询替代相关子查询
5. 单个事务不宜太长，以尽早释放锁

3.2表设计

1.如果频繁地访问涉及的是对两个相关的表进行连接操作，则考虑将其合并

2.如果频繁地访问只是在表中的某一部分字段上进行，则考虑分解表，将该部分单独作为一个表

3.对于很少更新的表，引入物化视图

4. 当系统中有一些少量的，重复出现的值时，使用字典表来节约存储空间和优化查询。如地区、系统中用户类型的代号等。这类值不会在程序的运行期变化，但是需要存储在数据库中。

   就地区而言，如果我们要查询某个地区的记录，则数据库需要通过字符串匹配的方式来查询；如果将地区改为一个地区的代号保存在表中，查询时通过地区的代号来查询，则查询的效率将大大提高。

程序中宜大量的使用字典表来表示这类值。字典表中保存这类值的代号和实体的集合，以外键的方式关联到使用这类值的表中。然而，在编码阶段，程序员并不使用字典表，因为首先查询字典表中实体的代号，违背了提高查询效率的初衷。程序员在数据字典的帮助下，直接使用代号来代表实体，从而提高效率。

虽然字典表在实际上并不使用，但是仍应该保留在数据库中（起码是在开发期内保留）。字典表作为另一种形式上的“数据字典文档”出现，以说明数据库中哪些表的哪些字段是使用了字典表的。

为了提高数据库的数据完整性，在开发阶段可以保留完整的字典表和普通表的外键约束。但是在数据库的运行阶段，应该将普通表和字典表的外键删除，以提高运行效率，特别是某些表使用了很多字典表的情况。

   案例：某数据库中有百万条用户信息，应用系统中常常需要按照地区要查询用户的信息。用户信息表以前是按照具体的地区名称来保存的，现在将具体的名称改为字典表中的地区代号，查询效率大大提高。

3.3索引

1. 如果查询是瓶颈，则在关系上建立适当的索引；通常，作为查询条件的属性上建立索引可以提高查询效率。
2. 如果更新是瓶颈，因为每次更新都会重建表上的索引，引起效率降低，则考虑删除某些索引。
3. 选择适当索引，如果经常使用范围查询，则B树索引比散列索引更高效
4. 将有利于大多数查询和更新的索引设为聚集性索引。

3.4提高IO效率

1. 索引文件和数据文件分开存储，事务日志文件存储在高速设备上
2. 经常修改数据文件和索引文件的页面大小
3. 定期对数据进行排序
4. 增加必要的索引项

4.数据库命名规范

4.1数据库对象

数据库对象采用26个英文字母（区分大小写）和0－9这十个自然数，加上下划线_组成，共63个字符。不能出现其他字符（注释除外）。

同一个数据库中这些对象名都是不能重复

C    CHECK_CONSTRAINT

D    DEFAULT_CONSTRAINT

F    FOREIGN_KEY_CONSTRAINT

IT   INTERNAL_TABLE

P    SQL_STORED_PROCEDURE

PK   PRIMARY_KEY_CONSTRAINT

S    SYSTEM_TABLE

SQ   SERVICE_QUEUE

TR   SQL_TRIGGER

U    USER_TABLE

UQ   UNIQUE_CONSTRAINT

V    VIEW

4.2命名规范规定

1.表名使用单数名

例如：对存储客人信息的表（Customer）不使用Customers

2.避免无谓的表格后缀

1、 表是用来存储数据信息的,表是行的集合。那么如果表名已经能够很好地说明其包含的数据信息，就不需要再添加体现上面两点的后缀了。

2、  GuestInfo(存储客户信息)应写成Guest，FlightList（存储航班信息的表）应写成Flight

3.所有表示时间的字段，统一以 Date 来作为结尾（而不是有的使用Date,有的使用Time）

以大家都熟悉的论坛来说，需要记录会员后一次登录的时间，这时候一般人都会把这个字段命名为LoginTime 或者 LoginDate。这时候，已经产生了一个歧义;如果仅看表的字段名称，不去看表的内容，很容易将LoginTime理解成登录的次数，因为，Time还有一个很常用的意思，就是次数

4.所有表示数目的字段，都应该以Count作为结尾

5.所有代表链接的字段，均为Url结尾

6.所有名称的字符范围为：A-Z, a-z, 0-9 和_(下划线)。不允许使用其他字符作为名称。

7.采用英文单词或英文短语（包括缩写）作为名称，不能使用无意义的字符或汉语拼音。

8.名称应该清晰明了，能够准确表达事物的含义，好可读，遵循“见名知意”的原则。

4.3数据库命名规范

数据库名称不需要简写，根据实际意义来命名。例如：ReportServer

数据库名：ReportServer

逻辑数据名：ReportServer；逻辑日志名：ReportServer_log

物理数据名：ReportServer.mdf；物理日志名：ReportServer_log.LDF

CREATE DATABASE [ReportServer] ON  PRIMARY

( NAME = N'ReportServer', FILENAME = N'D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\useData\ReportServer.mdf' , SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'ReportServer_log', FILENAME = N'D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\useData\ReportServer_log.LDF' , SIZE = 6400KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

注意：避免所有数据库的逻辑名称使用相同的名称。

4.4表设计命名规范

注意字段名不能使用保留关键字：如action,avg等

1、不使用tab或tbl作为表前缀（本来就是一个表，为什么还要说明）

2、表名以代表表内的内容的一个和多个名词组成，以下划线分隔，每个名词的个字母大写，例如：User、UserLogin，UserGroupRelation等

3、使用表的内容分类作为表名的前缀：如，与用户信息相关的表使用前缀User，与内容相关的信息使用前缀Content。

4、表的前缀以后，是表的具体内容的描述。如：用户登录信息的表名为：UserLogin，用户在论坛中的信息的表名为：UserBBSInfo

5、一些作为多对多连接的表，可以使用两个表的前缀作为表名：

         如：用户登录表UserLogin，用户分组表GroupInfo，这两个表建立多对多关系的表名为：UserGroupRelation

4.4.1字段命名规范

1. 字段名不要存在无用前缀，例如表‘WeiXinConfig’，既然我已经知道这张表是关于微信的表，里面的名称字段可以可以使用Name,不需要添加无用的前缀类似‘WeiXinName’，‘WeiXinGuanZhuMsg’，‘WeiXinUpImgMsg’等
2. 字段使用实际英文翻译作为命名字段，见名知意,不要使用让人看了半天都不知道是啥意思的字段（类似：lev1,lev2…）

4.5存储过程命名

存储过程名=[SP]+[查询修改标示]+[表名]

例如：

查询存储过程

SPCommunity

修改存储过程

SPChangeCommunity

4.5.1只允许应用程序通过存储过程访问数据库

   只允许应用程序通过存储过程访问数据库，而不允许直接在代码中写SQL语句访问数据库。

在数据库开发项目中，大量使用存储过程有很多的好处，首先看微软提供信息：