1 关系
数据库设计,需同时考虑实体设计(数据表)和实体间的关系设计。
1.1 一对一
一张表中的一条记录一定只能与另一张表中的一条记录对应。用具有唯一性的字段来关联两张表。
数据可以合并到同一张表中。
例如,人与身份证号。
1.2 一对多
一张表中的一条记录可以对应另一张表中的多条记录,但第二张表中的一条记录只能对应第一张表中的一条记录。需要在第二张表中增加字段关联第一张表。
例如,人与银行卡。
1.3 多对多
一张表中的一条记录可以对应另一张表中的多条记录,同时第二张表中的一条记录也能对应第一张表中的多条记录。需要增加一个表,维护两张实体表之间的关系。中间表与两张实体表都是一对多的关系。
例如,朋友关系,老师和学生,订单与商品。
2 范式
范式(Normal Format)用于解决数据的存储与优化,防止冗余,从而节省空间。
范式是分层结构,从1NF,2NF直到6NF。1NF要求最低,每一层都比上一层更严格。要满足下一层范式必须实现上一层范式。数据库通常用前三种范式。
2.1 第一范式 1NF:字段具有原子性
数据不可拆分,取出后可以直接用,不需要再处理,对应字段就具有原子性。
下面示例中,产品编码和产品两个字段违反了原子性:
表2.1:
订单号 | 产品编码 | 产品名 | 价格 | 用户ID | 用户名 | 创建日期 |
---|---|---|---|---|---|---|
1001 | P100,P101 | 路由器,手机 | 100,200 | 250 | 张三 | 2018-05-08 |
需要拆分数据,将一对一的关系留在原表,一对多的关系拆分到另外一张表:
表2.2:
订单号 | 用户ID | 用户名 | 创建日期 |
---|---|---|---|
1001 | 250 | 张三 | 2018-05-08 |
表2.3:
订单号 | 产品编码 | 产品名 | 价格 |
---|---|---|---|
1001 | P100 | 路由器 | 100 |
1001 | P101 | 手机 | 200 |
2.2 第二范式 2NF:表中有复合主键时,表中字段不能出现部分依赖
数据表中有复合主键时,如果表中有字段不是由整个主键确定,则出现部分依赖。此时应将部分依赖的字段拆分成单独的数据表,最终保证表中所有字段全部由复合主键确定;或者取消复合主键,增加一个逻辑主键(为数据表增加 ID 字段)。
复合主键:由多个字段构成的主键。例如上面例子中拆分后的第二个表(表2.3)的主键是(订单号,产品编码),就是复合主键。
上面的例子中,订单号和产品编码作为表的主键,并不能决定产品的属性。说白了,产品属性跟订单没有关系,应该建立一个单独的产品表用于放这些信息。不然,假如用户又下单购买了电脑,但是电脑的信息从哪里获取?
表2.4:
订单号 | 产品编码 |
---|---|
1001 | P100 |
1001 | P101 |
表2.5:
产品编码 | 产品名 | 价格 |
---|---|---|
P100 | 路由器 | 100 |
P101 | 手机 | 200 |
拆分后,可以单独编辑每个产品的信息,可以实现产品的上下架。
2.3 第三范式 3NF:一张表中的所有字段都直接依赖主键(业务主键而非逻辑主键)
传递依赖:表中的某个字段不是直接依赖主键,而是通过某个非主键字段依赖,最终实现依赖主键。
如果有字段存在传递依赖,需要将这些字段取出到单独的表。
上面的表2.2 中,在一张订单表中同时存储了用户的两个字段:用户ID和用户名。订单号能决定用户ID, 而用户ID 能决定用户名,发生传递依赖。
订单号 | 用户ID | 创建日期 |
---|---|---|
1001 | 250 | 2018-05-08 |
用户ID | 用户名 |
---|---|
250 | 张三 |
2.4 逆规范化:一张表提供所有的经常同时查询的数据,而不是去多表查询。
优点:查询速度快。
缺点:数据冗余,浪费空间。且更改后可能导致数据一致性出问题。
例如,假设每个订单都需要显示用户名,而订单表中不存在用户名,那每次查询操作都需要 JOIN 用户表,耗时。可以考虑把用户名字段加在订单表中,通过冗余字段来提高性能。关于数据一致性的问题,可以考虑通过触发器来实现,每次用户表的用户名字段变更时,同步修改订单表。