编程一五八俱乐部SQL学习笔记-3-数据库的三大设计范式

一、第一范式1nf

数据表中的所有字段都是不可分割的原子值。

mysql> create table student2(
    -> id int primary key,
    -> name varchar(20),
    -> address varchar(30)
    -> );
mysql> insert into student2 values(1,'zhangsan','中国四川省成都市武侯区武侯大道100号');
mysql> insert into student2 values(2,'lisi','中国四川省成都市武侯区武侯大道200号');
mysql> insert into student2 values(3,'wangwu','中国四川省成都市高新区天府大道90号');
mysql> select * from student2;
+----+----------+-----------------------------------------------------+
| id | name     | address                                             |
+----+----------+-----------------------------------------------------+
|  1 | zhangsan | 中国四川省成都市武侯区武侯大道100号                 |
|  2 | lisi     | 中国四川省成都市武侯区武侯大道200号                 |
|  3 | wangwu   | 中国四川省成都市高新区天府大道90号                  |
+----+----------+-----------------------------------------------------+
//其中address字段值可以继续拆分，不满足第一范式

mysql> create table student3( 
id int primary key, 
name varchar(20), 
country varchar(20), 
province varchar(20), 
city varchar(20),
details varchar(20));

mysql> insert into student3 value(1,'zhangsan','中国','四川省','成都市','武侯区武侯大道100号');
mysql> insert into student3 value(2,'lisi','中国','四川省','成都市','武侯区京城 大道200号');
mysql> insert into student3 value(3,'wangwu','中国','四川省','成都市','高新 区天府大 90号');
mysql> select * from student3;
+----+----------+---------+-----------+-----------+-----------------------------+
| id | name     | country | province  | city      | details                     |
+----+----------+---------+-----------+-----------+-----------------------------+
|  1 | zhangsan | 中国    | 四川省    | 成都市    | 武侯区武侯大道100号         |
|  2 | lisi     | 中国    | 四川省    | 成都市    | 武侯区京城大道200号         |
|  3 | wangwu   | 中国    | 四川省    | 成都市    | 高新区天府大道90号          |
+----+----------+---------+-----------+-----------+-----------------------------+

范式设计越详细，对某些实际操作可能更好，党并非有好处，需要根据项目实际情况设定。

二、第二范式2nf

满足第一范式的前提下，其他列都必须完全依赖于主键列。
如果出现不完全依赖，只可能发生在联合主键的情况下。

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),  //只和product_id有关，与主键里的customer_id无关
    customer_name VARCHAR(20), //只和customer_id有关，与主键里的product_id无关
    PRIMARY KEY (product_id, customer_id) 
);

这就不满足第二范式：其他列都必须完全依赖于主键列！

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

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

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

myorder 表中：product_id和customer_id完全依赖于 order_id 主键。
product 和 customer 表中：其他字段又完全依赖于相应的主键。
满足了第二范式的设计！

三、第三范式3nf

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

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

表中的 customer_phone 有可能依赖于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)
); //其他列只依赖于主键列，相互列之间没有依赖关系

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

成长于苍茫茫的异乡，回首依然望见故乡月亮。