第一章 数据库的设计

1.1 为什么需要规范的数据库设计

　　在第一阶段，根据业务需求，我们直接创建库、创建表、插入测试数据，然后再查询数据，为什么现在需要强调先设计再创建库、创建表呢？原因非常简单，正如我们修造建筑物一样，如果您是盖一间茅屋或一间简易平房，您会花钱请人设计房屋图纸吗？毫无疑问，没人请。但是，如果是房地产开发商开发一个楼盘，修建多栋楼的居住小区，他会请人设计施工图纸吗？答案是肯定的，不但开发商会考虑设计施工图纸，甚至很多专业的购房者也会在看房时要求开发商出示设计图纸。

　　同样道理，在实际的项目开发中，如果系统的数据存储量较大，设计的表比较多，表和表之间的关系比较复杂，我们就需要首先考虑规范的数据库设计，然后再进行具体的创建库、创建表的工作。不管是创建动态网站，还是创建桌面窗口应用程序，数据库设计的重要性都不言而喻。如果设计不当，查询起来就非常吃力，程序的性能也会受到影响。无论您使用的是SQL Server 还是 Oracle 数据库，通常进行规范化的数据库设计，都可以使您的程序代码更具体可读性，更容易扩展，从而也会提升项目的应用性能。

　　1. 什么是数据库设计

　　数据库设计就是将数据库中的数据对象以及这些数据对象之间关系，进行规划和结构化的过程。

　　2. 数据库设计非常重要

　　数据库中创建的数据结构的种类，以及在数据对象之间建立的复杂关系是决定数据库系统效率的重要因素。

　　糟糕的数据库设计表现为以下几点。

　　（1）效率底下。

　　（2）更新和检索数据时会出现许多问题。

　　良好的数据库设计表现为以下几点。

　　（1）效率高。

　　（2）便于进一步扩展。

　　（3）使得应用程序开发变得更容易。

1.2 设计数据库的步骤

　　经过了毕业设计项目开发、我们对项目的开发有了一个整体上的认识，项目开发需要经过需求分析、概要设计、详细设计、代码编写、运行测试和打包发行几个阶段。下面，重点讨论在各个阶段中数据库的设计过程。

　　　　需求分析阶段：分析客户的业务和数据处理需求。

　　　　概要设计阶段：绘制数据库的E-R模型图，用于在项目团队内部、设计人员和客户之间进行沟通，确认需求信息的正确性和完整性。

　　　　详细设计阶段：将E-R图转换为多张表，进行逻辑设计，确认各表的主外键，并应用数据库设计的三大范式进行审核。经项目组开会讨论确定后，还需根据项目的技术实现、团队开发能力以及项目的经费来源，选择具体的数据库（如SQL Server或Oracle等）进行物理实现，包括创建库、创建表，并创建我们后面将要学习的存储过程等。创建完毕后，开始进入代码编写阶段，开发前端应用程序。

　　现在，我们共同讨论在需求分析阶段，后台数据库的设计步骤。

　　需求分析阶段的重点是调查、收集并分析客户业务的数据需求、处理需求、安全性与完整性需求。

　　常用的需求调研方法有在客户的公司跟班实习、组织召开调查会、邀请专人介绍、设计调查表，并请用户填写、查阅业务相关数据记录等。

　　常用的需求分析方法有调查客户的公司住址情况、各部门的业务需求情况、协助客户分析系统的各种业务需求、确定新系统的边界。

　　物理数据库的大小和复杂程度如何，在进行数据库的系统分析时，都可以参考下列基础步骤。

　　（1）收集信息。

　　（2）标识对象。

　　（3）标识每个对象需要存储的详细信息。

　　（4）标识对象之间的关系。

　　1. 收集信息

　　创建数据库之前，必须充分理解数据库需要完成的任务和功能。简单地说，我们需要了解数据库需要存储哪些信息（数据），实现哪些功能。

　　2. 标识对象（实体）

　　在收集需求信息后，必须标识数据库要管理的关键对象或实体。对象可以是有形的事物，如人或产品；也可以是无形的事务，如商业交易，公司部门或发薪周期。在系统中标识这些对象以后，与它们相关的对象就会条理清楚。

　　3. 标识每个对象需要存储的详细信息（属性）

　　将数据库中的主要对象标识为表的候选对象以后，下一步就是标识每个对象存储的详细信息，也称为该对象的属性，这些属性将组成表中的列。简单地说，就是需要细分出每个对象包含的子成员信息。

　　4. 标识对象（实体）之间的关系

　　关系型数据库有一项非常强大的功能，它能够关联数据库中各个项目的相关信息。不同类型的信息可以单独存储，但是如果需要，数据库引擎可以根据需要将数据组合起来。在设计过程中，要标识对象之间的关系，需要分析数据库表，确定这些表在逻辑上是如何相关的，然后添加关系列建立起表之间的连接。

1.3 绘制E-R（实体-关系）图

　　在需求分析阶段解决了客户的业务和数据处理需求后，就进入了我们的概要设计阶段，我们需要和项目团队的其他成员以及我们的客户沟通，讨论数据库的设计是否满足客户的业务和数据处理需求。和机械行业需要机械制图，建筑行业需要施工图一样，我们的数据库设计也需要图形化的表达方式---E-R（Entity-Relationship）实体关系图，它包括一些具有特定含义的图形符号。

1.3.1 实体-关系模型

　　1. 实体

　　所谓实体就是指现实世界中具有区分其他事物的特征或属性并与其他实体有联系的对象。例如，BBS论坛系统中的用户（如张三、李四、王五等）、帖子、板块（动画板块、情感天地板块等）等，实体一般是名词，它对应我们表中的一行数据，例如张三用户这个实体，将对应于“用户表”中，张三用户所在的一行数据，包括他的密码、出生日期、电子邮件等信息。严格地说，实体是指表中一行一行的特定数据，但我们在开发时，也常常把整个表也称为一个实体。

　　2. 属性

　　属性可以理解为实体的特征。例如，“用户”这一实体的属性有昵称、出生日期和电子邮件等。属性对应表中的列。

　　3. 关系

　　关系是两个或多个实体之间的联系。

　　如图所示为用户实体和版块实体之间的关系。实体使用方块表示，实体一般是名词，属性使用椭圆标识，一般也是名词，关系使用菱形标识，一般是动词。

　　

　　4. 映射基数

　　映射基数表示可以通过关系与实体关联的其他实体的个数。对于实体集X和Y之间的二元关系，映射基数必须为下列基数之一。

　　一对一：X中的一个实体最多与Y中的一个实体关联，并且Y中的一个实体最多与X中的一个实体关联。假定规定一个论坛用户只能担任一个板块的版主，同时每个版块也只能有一个版主，那么，用户实体和版块实体之间就是一对一的关系。

　　一对多：X中的一个实体可以与Y中的任意数量的实体关联。Y中的一个实体最多与X中的一个实体关联。一个发帖（主贴）可以有多个回帖，所以说，发帖实体和回帖实体之间就是典型的一对多关系，一对多关系也可以表示为1：N。

　　多对一：X中的一个实体最多与Y中的一个实体关联。Y中的一个实体可以与X中的任意数量的实体关联。发帖实体和回帖实体之间是典型的一对多关系，反过来说，回帖实体和发帖实体之间就是多对一的关系。

　　多对多：X中的一个实体可以与Y中的任意数量的实体关联，反之亦然。假定一个版块允许有多个版主，一个用户也允许担任多个版块的版主，那么，版块实体和用户实体之间就是典型的多对多的关系，多对多关系也可以表示为M：N。

　　5. 实体关系图

　　E-R图以图形的方式将数据库的整个逻辑结构表示出来。E-R图的组成包括以下几个部门。

　　---> 矩形表示实体集。

　　---> 椭圆形表示属性。

　　---> 菱形表示关系集。

　　---> 直线用来联接属性和实体集，也用来连接实体集和关系集。

　　直线可以是有方向的（在末端有一个箭头），用来表示关系集的映射基数。图中显示了一些示例。这些示例表示了可以通过关系与一个实体相关联的其他实体的个数。箭头的定位很简单，可以将其视为指向引用的实体。

　　1：1 --- 每个客户（Customer）最多有一个账户（Account），并且每个账户最多归一个客户所有。

　　1：N --- 每个客户（Customer）可以有任意数量的账户（Account），但每个账户最多归一个客户所有。

　　M：N --- 每个客户（Customer）可以有任意数量的账户（Account），每个账户可以归任意数量的客户所有。

　　

　　　　

　　绘制E-R图后，我们还需要与客户反复地进行沟通，让客户提出修改意见，以确认系统中的数据处理需求是否正确和完整。

1.3.2 如何将E-R图转化为表

　　概要设计阶段解决了客户的需求捕获，并绘制了E-R图，在后续的详细设计阶段，我们需要把E-R图转化为多张表，并标识各表的主外键。本节将介绍如何将E-R图转化为表格，而如何审核各表的结构是否规范将专门在后面进行介绍。

　　第一步：将各实体转化为主键列，将各属性转化为各表对应的列。

　　第二步：标识每个表的主键列，需要注意的是，对没有主键的表添加ID编号列，该列没有实际含义，只用做主键或外键，例如用户表中的“UID”列，版块表中添加的“SID”列，发帖表和回帖表的“TID”列。为了数据编码的兼容性，建议使用英文字段。为了直观可见，在英文括号内注明对应的中文含义。

　　第三步：我们还需要在表之间体现实体之间的映射关系。

1.4 数据规范化

1.4.1 设计问题

　　有人开玩笑说，在概要设计阶段，同一个项目，10个设计人员将设计出10种不同的E-R图。不错，不同的人从不同的角度，标识出不同的实体，实体又包含不同的属性，自然就设计出不同的E-R图。那么怎样审核这些设计图呢，怎么评审出最优的设计方案呢？

　　为了讨论方便，下面直接以图中Account（账户）表为例，该表存储有关银行客户账户的信息和交易细节。

账户	客户姓名	地址	开户日期	账户类型	交易号	交易金额	交易日期
85001	Smith	1,Main	03-4-1	Savings	1	1000	03-4-2
85002	James	2,Main	03-4-3	Current	2	200	03-4-4
85003	Ritcha	3,Main	03-4-4	Savings	3	100	03-4-5
85003	Ritcha	3,Main	03-4-4	Savings	4	400	03-4-6

　　从用户的角度而言，将所有信息放在一个表中很方便，因为这样查询数据库可能会比较容易。但是上述表具有下列问题。

　　1. 信息重复

　　很多信息是重复的。“客户姓名” 和 “账户类型” 列中有许多重复的信息，例如“Savings”。信息重复会造成存储空间的浪费以及一些其他问题，如果不小心输入“Saving”和“Savings”，在数据库中将表示两种不同的账户类型。

　　2. 更新异常

　　冗余信息不仅浪费存储空间，而且会增加更新的难度。如果需要将“账户类型”修改为“Savings Account”而不是“Savings”，则需要修改所有包含该值的行。如果由于某种原因，没有更新所有行，则数据库中会有两种类型的账户类型，一个是“Savings”，另一个是“Savings Account”，这种情况被成为更新异常。

　　3. 插入异常（无法表示某些信息）

　　上图中，“帐号”或“交易号”单独不能作为主键，我们需要采用组合作为主键，假设图中的主键为（帐号，交易号）。任何要插入到该关系中的新行必须提供主键的值，因为现有的完整性要求主键不能完全或部分为空。如果某个帐号希望开户，即可向该表中插入一行数据，但该帐号刚开户，还没有交易记录，您将无法插入开户信息的数据。这种问题被成为插入异常。

　　4. 删除异常（丢失有用的信息）

　　在某些情况下，当删除一行时，可能会丢失有用的信息。例如，如果删除帐号为“85002”的行，就会丢失账户类型为“Current”的账户的信息，该表只剩下唯一的一种账户类型“Savings”了。当希望查询有哪些账户类型时，将会误以为只有“Savings”账户类型。这种情况被成为删除异常。

1.4.2 规范设计

　　在数据库设计时，有一些专门的规则，成为数据库的设计范式，遵守这些规则，您将创建设计良好的数据库，下面将逐一讲解数据库设计中著名的三大范式理论。

　　1. 第一范式（1NF , Normal Formate）

　　第一范式的目标是确保每列的原子性。如果每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）。

　　例如：顾客表（姓名，顾客编号，地址，···），其中“地址”列还可以细分为国家、省、市、区等，更多的程序甚至把“姓名”列也拆分为“姓”和“名”等。

　　2. 第二范式（2NF）

　　第二范式在第一范式的基础上，更进一层，其目标是确保表中的每列都和主键相关：如果一个关系满足1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式（2NF）。

　　例如：订单表（订单编号，产品编号，订购日期，价格···）

　　该表主要用来描述订单，所以将“订单编号”设为主键，“定购日期”、“价格”两列都和“订单编号”列主键相关，但“产品编号”列和“订单编号”列没有直接的关系，即“产品编号”列不依赖于“订单编号”主键列，该列应从该表中删除，放入产品表中。

　　这样，该表就只描述一件事情---订单信息。

　　3. 第三范式（3NF）

　　第三范式在第二范式的基础上，更进一层，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关。如果一个关系满足2NF，并且除了主键以外的其他列都不依赖于主键列，则满足第三范式（3NF）。

　　为了理解第三范式，需要根据Armstrong公理之一定义传递依赖。假设A、B和C是关系R的3个属性，如果A->B->C，则从这些函数依赖（FD）中，可以得出A->C。如上所述，依赖A->C是传递依赖。

　　例如：订单表（订单编号、定购日期、顾客编号、顾客姓名，···）

　　初看该表没有问题，满足2NF，每列都和主键列“订单编号”相关，再细看您会发现“顾客姓名”列和“顾客编号”列相关，“顾客编号”列和“订单编号”列又相关，最后经过传递依赖，“顾客姓名”列也和“订单编号”列相关。为了满足3NF，应该去掉“顾客姓名”列，将此列放入客户表中。

　　了解了用于规范化数据设计的三大范式后，让我们一起来审核上图帐号表Account。

　　1. 是否满足第一范式

　　　　第一范式要求每列必须是最小的原子单元，即不能再细分。前面我们提及过，为方便查询，地址需要分为省、市、区等，但我们目前还没有这方面的查询需求，所以本例没必要拆分“地址”列。

　　所以该表满足第一范式。

　　2. 是否满足第二范式

　　　　第二范式要求每列必须和主键相关，不相关的列放入别的表中，即要求一个表只描述一件事情。

　　　　实用的技巧是，我们可以直接看该表描述了哪几件事情，然后一件事情创建一张表。

　　　　　　帐号信息。

　　　　　　交易信息。

　　　　　　账户类型信息。

　　　　即我们需要拆分为3张表，对各列进行筛选，3张表即为如图。

账户表Account
帐号	客户姓名	地址	开户日期	账户类型
85001	Smith	1,Main	03-4-1	1
85002	James	2,Main	03-4-3	2
85003	Ritcha	3,Main	03-4-4	1
85003	Ritcha	3,Main	03-4-4	1

交易表Trade
交易号	交易金额	交易日期	帐号
1	1000	03-4-2	85001
2	200	03-4-4	85002
3	100	03-4-5	85003
4	400	03-4-6	85003

账户类型表AcntType
类型编号	账户类型
1	Savings
2	Current

　　　　其中，帐号、交易号、类型编号三列为三个表的主键，为了表示哪个帐号进行了交易，在交易表中插入了一列“帐号”。

　　3. 是否满足第三范式

　　　　第三范式要求表中各列必须和主键直接相关，不能间接相关，浏览每个表，都满足第三范式要求。

1.4.3 规范化和性能的关系

　　需要提醒的是，对于项目的最终用户来说，客户最关心的是方便、清晰的数据结果。您如果让客户选择，毫无疑问，客户会认为最初的表设计最适合需求，虽然它根本就不满足三大范式，并且存在大量的数据冗余。

　　所以说，我们在设计数据库时，设计人员和客户对数据库的设计有一定的矛盾。通过三大范式分解的3张表，为了满足客户的需求，最终我们需要通过3张表之间的连接查询，恢复为客户需要的数据结果。插入数据同样如此，对客户输入的数据，我们需要分开插入在3张不同的表中。

　　由此可以看出，为了满足三大范式，我们的数据操作性能会受到相应的影响。所以在实际的数据库设计中，既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据访问性能。有时，为了减少表间连接，提高数据库的访问性能，允许适当的数据冗余列，可能是最合适的数据库设计方案。

转载于:https://www.cnblogs.com/jiazengtao/archive/2013/06/08/3125487.html