DB设计：关系型数据库设计总结-优快云博客

前段时间因为项目需要搭建一个web服务器，后端Web框架我调研了几个，比如Python的Flask,Django,

但是当时对于数据库的选择，却走了一点弯路。因为平时对于数据库接触不多，所以一开始选择了MongoDB

关系数据库简介

关系数据库由由埃德加·科德（IBM）在1969年左右提出。自推出后就成为商业应用的主要数据库模型（与其他数据库模型，

关系数据库将数据存储在表（table）中。一个表由行和列组成。行称为记录(record)或元组(tuple)，列称为字段(field)或属性(attribute)。

SQL（结构化查询语言）通常用来对关系数据库进行操作。

关系数据库设计步骤

数据库的设计对经验的要求比理论要高，因为你必须作出许多选择。数据库通常是为了某种应用的需求而高度定制的，因此，不要做什么而不是要做什么，但最后的决定权还是在设计者的手中。

一、需求分析

尽可能地收集需求，以及定义你的数据库的最终目的。比如要开发书店查询应用，就要先知道应用有什么需求，

二、收集数据，组织表并设定主键

一旦需求明确，接下来就要确定有哪些数据需要存储到数据库中。通常我们都是将数据基于分类存储到不同的表中。主键（primary key）。

关于主键

在关系模型中，表不可以含有重复的行，否则会导致检索出现歧义。为保证唯一性，每个表都有某一列（或者多列）作为主键，

主键的选择由库的设计者来决定，要遵循以下原则：

主键的值必须是唯一的（即不可重复）
主键不能为空

除此之外，对于主键的选取还有一些best practice:

主键的值不可修改。因为主键可能会在其他表中用来引用，如果改了主键的值，就需要把其他表的引用都更新。
主键可以是任何类型，但最好是整数（效率原因）
主键最好用简单键，如果一定要用组合键，要尽量用最少的列

目前的数据库大都可以不主动指定主键，而是由于数据库自己添加额外的一列类型为自增整数（AutoNumber）并指定为主键。

三、建立关系

在关系数据库中包含独立且不相关的表格通常没有太大意义，如果真是这种情况你可以考虑使用NoSQL或者电子表格来存储这些内容。

一对多(one-to-many)
多对多(many-to-many)
一对一(one-to-one)

一对多

考虑一个族谱关系的例子，一个母亲可能会有0个或多个小孩，但是任意一个小孩都有且只有一个母亲。这样的关系便称为一对多。

因此，考虑支持一对多的数据库关系，我们应该建立两个表，分别为Mothers和Children，只保存各自的属性，并且设置分别的主键为

640?wx_fmt=png

其中Children表里的MotherID列又被称为约束或外键（Foreign Key），用SQL来描述如下：

CREATE TABLE `Mothers` ( 
`MotherID` INTEGER NOT NULL AUTO_INCREMENT,`Name` VARCHAR(100) NOT NULL,`Age`  SMALLINT NOT NULL,`BloodType` VARCHAR(2) NOT NULL,PRIMARY KEY (`MotherID`)
);CREATE TABLE `Children` ( 
`ChildrenID` INTEGER NOT NULL AUTO_INCREMENT,`MotherID` INTEGER NOT NULL,`Name` VARCHAR(100) NOT NULL,`Age`  SMALLINT NOT NULL,`Sex` VARCHAR(50) NOT NULL,`BloodType` VARCHAR(2) NOT NULL,PRIMARY KEY (`ChildrenID`),FOREIGN KEY (`MotherID`) REFERENCES `Mothers` (`MotherID`)
);

多对多

考虑一个“产品销售”数据库的例子，某个客户的订单包含一个或者多个产品，而某个产品又可能出现在多个订单之中，Orders和产品Products开始看。Products含有关于产品的信息（如名称，介绍，库存）以及一个主键ProductID;表Orders则包含订单信息OrderID。同样地，我们没法简单地将所有购买的产品保存在订单表里，

因此，为了支持这种多对多的关系，我们需要第三个表。在本例子中，姑且将其命名为OrderDetails，OrderID和ProductID，Orders和Products表的Foreign Key，如下图所示：

640?wx_fmt=png

SQL描述如下：

CREATE TABLE `Orders` (`OrderID` INTEGER NOT NULL AUTO_INCREMENT,`OrderDate` DATETIME DEFAULT CURRENT_TIMESTAMP,`CustomerID` INTEGER NOT NULL,PRIMARY KEY (`OrderID`)
);CREATE TABLE `Products` (`ProductID` INTEGER NOT NULL AUTO_INCREMENT, 
`Name` VARCHAR(100) NOT NULL,`Stock` INTEGER DEFAULT 0,PRIMARY KEY (`ProductID`)
);CREATE TABLE `OrderDetails` (`OrderID` INTEGER NOT NULL,`ProductID` INTEGER NOT NULL, 
FOREIGN KEY (`OrderID`) REFERENCES `Orders` (`OrderID`),FOREIGN KEY (`ProductID`) REFERENCES `Products` (`ProductID`),PRIMARY KEY (`OrderID`,`ProductID`)
);

事实上，多对多的关系是以两组一对多的关系来实现的，额外引入的表被称为junction table即连接表。OrderDetails表里出现多次，但OrderDetails

一对一

考虑一个“产品信息”数据库，其中除了产品名称，产品数量等基本信息外，还需要保存产品图片，产品详细等富文本详情信息，

回到前面的例子，我们需要分裂出一个称为ProductDetails的表，与Products构成一对一的关系。图我就不画了，

CREATE TABLE `Products` (`ProductID` INTEGER NOT NULL AUTO_INCREMENT,`Name` VARCHAR(50) NOT NULL,PRIMARY KEY (`ProductID`)
);CREATE TABLE `ProductDetails` (`ProductID` INTEGER NOT NULL AUTO_INCREMENT,`DetailInfo` VARCHAR(65535),FOREIGN KEY (`ProductID`) REFERENCES `Products` (`ProductID`),PRIMARY KEY (`ProductID`)
);

可以看到在ProductDetails表中，主键和外键都为同一列, 这保证了一对一的正确性。值得一提的是，这里保证了ProductsProductDetails，但ProductDetails必须对应一个Products，如果后者对前者不是强关联，如“丈夫-妻子”UNIQUE的属性作为外键即可。

精炼及规格化

当设计好一个数据库或者拿到已有的数据库时，我们可能会想要：

增加更多的列
为某个表中的可选数据创建一个新表并建立一对一关系
将一个大表分裂为两个小表
...

在进行这些操作时，下列的规则就可以作为参考。

规范规则(Normalization)

范式（Normal Form），指的是符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度，

第一范式(1NF)

第一范式又称为1NF（First Normal Form），是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库。

简而言之，第一范式就是没有重复的列。

第二范式(2NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式必须先满足第一范式（1NF）。主键。

第二范式也要求实体的属性完全依赖于主键。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，OrderDetails，主键为ProductID和OrderID，若含有一列为产品单价ProductPrice,OrderID，因此此属性应该保存在Products表中。

简而言之，第二范式就是属性应完全依赖于其主键。

第三范式(3NF)

满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式要求数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖。EmplyeeID->DepartmentID->DepartmentName),因此在员工信息表中列出部门编号后就不应再将部门名称、

简而言之，第三范式就是任一非主键属性不应依赖于其它任何非主键属性。

鲍依斯-科得范式(BCNF)

BCNF(Boyce Codd Normal Form)又称为3.5NF，其出现的目的是为了弥补3NF的缺陷。在满足3NF的前提下，今日会议室预订表为例。考虑有以下表格：

会议室编号	开始时间	结束时间	会议类型
1	09:30	10:30	A1
1	10:00	11:30	A2
2	09:00	09:30	B1
2	14:30	17:30	B2

其中会议类型的定义如下：

A1, 在会议室1里的一般会议
A2，在会议室1里的管理层会议
B1，在会议室2里的一般会议
B2，在会议室2里的管理层会议

这个表里所有字段都是候选关键字段，因此显然是满足3NF的，但同时存在以下决定关系：

（会议室编号）-> （会议类型）
（会议类型） -> （会议室编号）

即关键字段影响关键字段的情况，因此不满足BCNF。

简而言之，BCNF就是任一属性不应依赖于其它非主键属性。

其他更严格的范式超出了本文的范围，因此不再赘述。

完整规则(Integrity)

除了设计范式，我们也可以通过完整性规则（Integrity rules）来检查自己的设计。常见的完整性规则如下：

实体完整性（Entity Integrity Rule）

实体完整性指表中行的完整性。主要用于保证操作的数据（记录）非空、唯一且不重复。即实体完整性要求每个关系（表）

参照完整性（Referential Integrity Rule）

参照完整性属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一，就会影响数据的完整性。

域完整性（Domain Integrity）

域完整性是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。表中的CHECK、

用户定义完整性（User-defined Integrity）

又叫业务逻辑完整性（Business logic Integrity），是对数据表中字段属性的约束，用户定义完整性规则（User-defined integrity）

其他

通常我们可以通过对指定的列创建索引来加快数据库的读取和查询速度。在实现上，索引通常是一个结构化文件，可以提高SELECT的速度，INSERT, UPDATE和DELETE的速度有一定负面影响。如果没有索引，进行一次条件查询（比如SELECT * FROM Customers WHERE name="Sam"）

对于特定的表来说，索引可以是1列，多列组合（称为组合索引，Concatenated Index）或者是某列的部分内容（称为部分索引，Partial Index）。