MySQL 学习笔记（三）——三大设计范式

数据库的三大设计范式

1、第一范式：数据中所有字段都是不可分割的原子值

只要字段值还可以继续拆分，就不满足第一范式。

举个例子，创建一个表student2，再向里面插入数据，结果如下：

mysql> select * from student2;
+----+------+-------------------------------+
| id | name | address                       |
+----+------+-------------------------------+
|  1 | 张三 | 中国湖北省天门市陆羽大道66号  |
|  2 | 李四 | 中国湖北省天门市陆羽大道100号 |
+----+------+-------------------------------+
2 rows in set (0.29 sec)

其中的地址字段值还是可以继续拆分，分成省份、城市、区，上图就是可拆分，不满足第一范式

我们要把表拆的详细一点，后期方便统计。

mysql> create table student3(
    -> id int primary key,
    -> name varchar(20),
    -> country varchar(30),
    -> province varchar(30),
    -> city varchar(30),
    -> details varchar(30)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into student3 values(1,'张三','中国','湖北省','天门市','陆羽大道66号');
Query OK, 1 row affected (0.31 sec)

mysql> insert into student3 values(2,'李四','中国','湖北省','天门市','陆羽大道100号');
Query OK, 1 row affected (0.05 sec)

查看数据：

mysql> select * from student3;
+----+------+---------+----------+--------+---------------+
| id | name | country | province | city   | details       |
+----+------+---------+----------+--------+---------------+
|  1 | 张三 | 中国    | 湖北省   | 天门市 | 陆羽大道66号  |
|  2 | 李四 | 中国    | 湖北省   | 天门市 | 陆羽大道100号 |
+----+------+---------+----------+--------+---------------+
2 rows in set (0.00 sec)

范式设计的越详细，对某些实际操作可能更好，比如我们要统计湖北省人数，就可以直接对province操作。

但是不一定都是好处，需要对项目的实际情况进行设定。

 

2、第二范式：必须满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键

如果出现不完全依赖，只可能发送在联合主键的情况下。

下面我们创建一个表，用来当做订单：

mysql> create table myorder(
    -> product_id int,           #产品号
    -> customer_id int,          #用户号
    -> product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)     #产品号和用户号形成联合主键
    -> );
Query OK, 0 rows affected (1.21 sec)

这里就发现一个问题，除主键外其他列，只依赖于主键的部分字段。
　　产品的名字只和产品号有关、用户的名字只和用户号有关，就是不完全依赖于主键，比满足第二范式！！
　　解决方法如下，拆表：

mysql> create table myorder2(     #订单id表
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );
Query OK, 0 rows affected (0.99 sec)

mysql> create table product(    #产品名 表，依赖于产品id
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.93 sec)

mysql> create table customer(   #顾客名 表，依赖于顾客id
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.82 sec)

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

 

3、第三范式：必须满足第二范式，除主键列的其他列之间不能有传递依赖关系

看这个例子：

mysql> create table myorder2(    
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int，
    -> customer_phone varchar(20)
    -> );
Query OK, 0 rows affected (0.99 sec)

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

应该将 顾客的手机 放入顾客表中才满足第三范式。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！