数据库表设计的原则攻略

  创建数据库里最基本的应该就是建表，建索引、存储过程等一系列操作了。谈到表就不得不谈到实体。

一、数据实体

什么是实体，客观存在并且可以相互区别的事物称为实体。这里我们就简单的把它理解为一个表吧，描述实体的特性，我们就把他们称为了属性。也可以说当我们把一个数据库表当作一个实体，那么它里面的所有字段是不是就是一个属性了呢？结果是肯定的。

二、实体间的联系

我想说的是，很简单，数据库里表跟表间的关系莫过于三种：一对一；多对多；一对多。

一对一其实就是说我们建的主表跟相关联的表之间是一一对应的，比如说，我建了一个学生基本信息表：t_student,然后我又建了一个成绩表，里面有个外键，studentID，学生基本信息表里的字段studentID和成绩表里的studentID就是一对一。

一对多，也是类似，我另外建一个班级表，而每个班级有多个学生，每个学生就对应一个班级，对班级来说当然就是一对多了。

多对多，我还举这个例子，我建个选课表，可能有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目。这就是多对多了。

三、基本表的完整性

(1) 原子性。基本表中的字段是不可再分解的。

　　 (2) 原始性。基本表中的记录是原始数据（基础数据）的记录。

　　 (3) 演绎性。由基本表与代码表中的数据，可以派生出所有的输出数据。

　　 (4) 稳定性。基本表的结构是相对稳定的，表中的记录是要长期保存的。

这是基本表的完整性，也是它特有的。这里我想说的是，在数据库里还有几种表也是常用的那就是中间表和临时表。

1、中间表

中间表是针对多对多关系的，就比如做公交查询系统。里面有两个表，分别是车站表、线路表。这里我们起个名字叫：t_busstation 、t_road，根据常识我们也知道，一个站有多个线路经过，而每个线路又有多个车站，怎么才能将两个表联系起来呢，如果是一对一，一对多，我们一个表，两个表就可以将他们实现了，但是多对多呢，这样我们就必须借助中间表用来连接两个表。一般中间表都是有一个本表的自增主键，还有另外两个表的主键。中间表是没有属性的因为它不是一个基本表。

2、临时表

在本次项目中，我们就要用到临时表，首先来看看什么是临时表吧。这是我从网上书上查到的。因为我们用的是MS SQL Server 2000数据库，而在这个数据库里是支持临时表的。

临时表：其实就是那些以#号开头为名字的数据表，它主要是用来存放临时数据的，当用户断开连接但没有出去临时表里的数据时，系统会自动把临时表里的数据清空。这里要说一点，临时表是放在系统数据库tempdb中的，而不是当前数据库。

临时表总共是分两种：本地临时表和全局临时表。

（1）这里我们需要了解的就是，在数据库中本地临时表是以一个#开头的，这种临时表只对当前的数据库用户可见，而其他的用户是不可见的。当数据库实例断开后当然也就丢失了数据了，不管是显式清空还是系统回收。

（2）还有一个就是全局临时表。它是以“##”开头的，而且是对于所有的用户都是可见的，当你断开数据库实例连接时，只要还有别的系统项目在引用它，连着数据库，那么数据就存在，只有当别的系统也断开连接时，系统才会清除全局临时表的数据。

下面是建立临时表的语句:

本地临时表：

  create table #student
(
studentID int ,
studentName nvarchar (40),
classID int
)

全局临时表：

create table ##student
(
studentID int ,
studentName nvarchar (40).
classID int
)

这里我们也可以用SQL语句完成：

select * from employee into #student

现在就来看看三大范式。

第一范式：如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.比如一个工人的基本信息表，里面有工人的工号，性别，年龄，这些属性都是不可分割的，所以这个表就符合了第一范式。

第二范式： 就是在第一范式的基础上延伸，使之表里的每个字段都与主键发生关系。假如一个关系满足第一范式,并且除了主键以外的其它字段,都依赖于该主键,则满足第二范式.
例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，这个列我们就可以把它删除。

第三范式：在第二范式的基础上更进一步，也就是为了实现表里的列都与主键列直接相关，不是间接相关。这个我们可以用“Armstrong 公理”中的传递规则来推理。

我们来看一下它的定义：

设U是关系模式R 的属性集，F 是R 上成立的只涉及U 中属性的函数依赖集。若X→Y 和 Y→Z在R 上成立，则X →Z 在R 上成立。因此我们就来看在网上搜索到的例子：例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

这里其实就是为了说明数据库的表里步要出现冗余，在顾客表里已经有了"顾客姓名"了，而在订单表里就别出现了，而直接根据顾客编号相关联就可以，否则造成资源浪费。

以上就是三大范式。
延伸：我们来看这三大范式：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
　　 第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
　　 第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没

有冗余。

其实在设计数据库的时候我们最多的要遵循的就是第三范式，但是并不是越满足第三范式数据库就设计的越完美，这种错误是错误的。有时候增加点冗余相反的会提高访问速率，因此在实际的设计过程中应降低对范式的要求。

以前对数据冗余并不是很了解，在百度知道里的定义是这样的：在一个数据集合中重复的数据称为数据冗余. 但是不是说我们表的主键在其他表里重复出现就是冗余，这不是，而是为了连接两个表。只有非键字段就是既不是主键外键等约束的键如果重复出现，就会形成数据冗余。数据冗余也包括重复性冗余和派生冗余。比如工人表里有"基本工资"，"奖金"两列，然后还有一个"总工资"的列，这个总工资就是派生冗余。低级的重复性冗余一定要避免，杜绝，但是像派生冗余还是提倡的因为它能提高访问的效率。

 

 

在目前的企业信息系统中，数据库还是最佳的数据存储方式，虽然已经有很多的书籍在指导我们进行数据库设计，但应该那种方式是设计数据库的表结构的最好方法、设计时应遵从什么样的原则、四个范式如何能够用一种方式达到顺畅的应用等是我一直在思考和总结的问题，下文是我针对这几个问题根据自己的设计经历准备总结的一篇文章的提纲，欢迎大家一块进行探讨，集思广益。其中提到了领域建模的概念，但未作详细解释，希望以后能够有时间我们针对这个命题进行深入探讨。

1)不应该针对整个系统进行数据库设计，而应该根据系统架构中的组件划分，针对每个组件所处理的业务进行组件单元的数据库设计；不同组件间所对应的数据库表之间的关联应尽可能减少，如果不同组件间的表需要外键关联也尽量不要创建外键关联，而只是记录关联表的一个主键，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。

2)采用领域模型驱动的方式和自顶向下的思路进行数据库设计，首先分析系统业务，根据职责定义对象。对象要符合封装的特性，确保与职责相关的数据项被定义在一个对象之内，这些数据项能够完整描述该职责，不会出现职责描述缺失。并且一个对象有且只有一项职责，如果一个对象要负责两个或两个以上的职责，应进行分拆。

3)根据建立的领域模型进行数据库表的映射，此时应参考数据库设计第二范式：一个表中的所有非关键字属性都依赖于整个关键字。关键字可以是一个属性，也可以是多个属性的集合，不论那种方式，都应确保关键字能够保证唯一性。在确定关键字时，应保证关键字不会参与业务且不会出现更新异常，这时，最优解决方案为采用一个自增数值型属性或一个随机字符串作为表的关键字。

4)由于第一点所述的领域模型驱动的方式设计数据库表结构，领域模型中的每一个对象只有一项职责，所以对象中的数据项不存在传递依赖，所以，这种思路的数据库表结构设计从一开始即满足第三范式：一个表应满足第二范式，且属性间不存在传递依赖。

5)同样，由于对象职责的单一性以及对象之间的关系反映的是业务逻辑之间的关系，所以在领域模型中的对象存在主对象和从对象之分，从对象是从1－N或N－N的角度进一步主对象的业务逻辑，所以从对象及对象关系映射为的表及表关联关系不存在删除和插入异常。

6)在映射后得出的数据库表结构中，应再根据第四范式进行进一步修改，确保不存在多值依赖。这时，应根据反向工程的思路反馈给领域模型。如果表结构中存在多值依赖，则证明领域模型中的对象具有至少两个以上的职责，应根据第一条进行设计修正。第四范式：一个表如果满足BCNF，不应存在多值依赖。

7)在经过分析后确认所有的表都满足二、三、四范式的情况下，表和表之间的关联尽量采用弱关联以便于对表字段和表结构的调整和重构。并且，我认为数据库中的表是用来持久化一个对象实例在特定时间及特定条件下的状态的，只是一个存储介质，所以，表和表之间也不应用强关联来表述业务（数据间的一致性），这一职责应由系统的逻辑层来保证，这种方式也确保了系统对于不正确数据（脏数据）的兼容性。当然，从整个系统的角度来说我们还是要尽最大努力确保系统不会产生脏数据，单从另一个角度来说，脏数据的产生在一定程度上也是不可避免的，我们也要保证系统对这种情况的容错性。这是一个折中的方案。

8)应针对所有表的主键和外键建立索引，有针对性的（针对一些大数据量和常用检索方式）建立组合属性的索引，提高检索效率。虽然建立索引会消耗部分系统资源，但比较起在检索时搜索整张表中的数据尤其时表中的数据量较大时所带来的性能影响，以及无索引时的排序操作所带来的性能影响，这种方式仍然是值得提倡的。

9)尽量少采用存储过程，目前已经有很多技术可以替代存储过程的功能如“对象/关系映射”等，将数据一致性的保证放在数据库中，无论对于版本控制、开发和部署、以及数据库的迁移都会带来很大的影响。但不可否认，存储过程具有性能上的优势，所以，当系统可使用的硬件不会得到提升而性能又是非常重要的质量属性时，可经过平衡考虑选用存储过程。

10)当处理表间的关联约束所付出的代价（常常是使用性上的代价）超过了保证不会出现修改、删除、更改异常所付出的代价，并且数据冗余也不是主要的问题时，表设计可以不符合四个范式。四个范式确保了不会出现异常，但也可能由此导致过于纯洁的设计，使得表结构难于使用，所以在设计时需要进行综合判断，但首先确保符合四个范式，然后再进行精化修正是刚刚进入数据库设计领域时可以采用的最好办法。

11)设计出的表要具有较好的使用性，主要体现在查询时是否需要关联多张表且还需使用复杂的SQL技巧。

12)设计出的表要尽可能减少数据冗余，确保数据的准确性，有效的控制冗余有助于提高数据库的性能