MYSQL学习之数据库设计三范式篇

（一）什么是设计库设计范式？

数据库表的设计依据，从而进行数据表的设计。

（二）范式内容

I.第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

II.第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

III.第三范式： 建立在第二范式的基础上，要求所有的非主键字段支架以来逐渐，不要产生传递依赖。

依据上述的三个范式设计数据库表可以避免数据的冗余，空间的浪费。

实例如下：

第一范式

学生编号 学生姓名 联系方式

-----------------------------------------------------------------------------

1001 张三 zs@gmail.com,13578668908

1002 李四 ​ ​ls@gmail.com​​,14278904355

1001 王五 ww@163.net,13567890342

-----------------------------------------------------------------------------

分析：以上表格并不符合第一范式

（1）表中没有主键

（2）联系方式字段并不具有原子性

修改后如下：

-----------------------------------------------------------------------------

学生编号（PK） 学生姓名 邮箱地址 手机号

-----------------------------------------------------------------------------

1001 张三 ​ ​zs@gmail.com​​ 1 3578668908

1002 李四 ​ ​ls@gmail.com​​ 14278904355

1003 王五 ​ ​ww@163.com ​​ 13567890342

-----------------------------------------------------------------------------

第二范式

----------------------------------------------------------------------

学生编号 学生姓名 教师编号 教师姓名

----------------------------------------------------------------------

1001 张三 001 王老师

1002 李四 002 赵老师

1003 王五 001 王老师

1001 张三 002 赵老师

-----------------------------------------------------------------------

分析：以上表格不满足第一范式：

①没有主键

修改后入下：

---------------------------------------------------------------------

学生编号 + 教师编号（PK） 学生姓名 教师姓名

---------------------------------------------------------------------

1001 001 张三 王老师

1002 002 李四 赵老师

1003 001 王五 王老师

1001 002 张三 赵老师

-----------------------------------------------------------------------

说明

将学生编号和教师编号两个字段联合做主键，复合主键（PK：学生编号+教师编号）

分析：虽然经过修改后的表满足了第一范式，但是并不满足第二范式

学生依赖于学生编号 ，教师依赖于教师编号，产生了部分依赖

缺点：数据冗余，空间浪费

修改后如下：

对于老师与学生之间的这种多对多的关系，可以使用多张表来表示

学生表

-----------------------------------------------------------

学生编号 （PK） 学生姓名

-----------------------------------------------------------

1001 张三

1002 李四

1003 王五

-----------------------------------------------------------

教师表

----------------------------------------------------------

教师编号（PK） 教师姓名

----------------------------------------------------------

001 王老师

002 赵老师

-----------------------------------------------------------

学生教师关系表

-------------------------------------------------------------------

id(PK) 学生编号（fk） 教师编号（fk）

1 1001 001

2 1002 002

3 1003 001

4 1001 002

-------------------------------------------------------------------

多对多设计口诀

多对多，三张表，关系表两个外键

第三范式

第三范式是建立在第二范式的基础上，要求所有非主键字段必须直接依赖主键，不要产生传递依赖。

学生编号（PK） 学生姓名 班级编号 班级名称

-----------------------------------------------------------------

1001 张三 01 一年一班

1002 李四 02 一年二班

1003 王五 03 一年三班

1004 赵六 04 一年三班

----------------------------------------------------------------

分析·：

上表描述的是班级和学生的关系（一对多的关系--->一个教室可以有多个学生）

①满足第一范式（存在主键）

②满足第二范式（主键是单一主键，不是复合主键，没有产生部分依赖）

③不满足第三范式： 产生了传递依赖（班级依赖班级编号，班级编号依赖学生编号）

解决方案：设计两张表（班级表、学生表）

班级表:一

班级编号 （PK） 班级名称

--------------------------------------------------------

01 一年一班

02 一年二班

03 一年三班

-------------------------------------------------------

学生表：多

学生编号（PK） 学生姓名 班级编号 (fk）

-------------------------------------------------------

1001 张三 01

1002 李四 02

1003 王五 03

1004 赵六 04

------------------------------------------------------

口诀：

一对多，两张表，多的表加外键

表的设计

对应关系

设计方法

一对一

外键唯一

一对多

两张表，多的表加外键

多对多

三张表 ，关系表加两个外键

一对一：

对于一张表中存在多个字段，这时就需要这一张表进行拆分

t_user

id login_name loginpwd real_name email address

-----------------------------------------------------------------------------

1 zhangsan 123 张三 zhangsan@xxx

2 lisi 123 李四 lisi@xxx

......

-----------------------------------------------------------------------------

拆分为两张表：

t_login 登录信息表

id(pk) login_name login_pwd

-----------------------------------------------------------------------------

1 zhangsan 123

2 lisi 123

......

----------------------------------------------------------------------------

t_user 用户详细信息表

id(pk) real_name email address..... login_id(fk+unique)

-----------------------------------------------------------------------------

100 张三 zhangsan@xxx 1

200 李四 lisi@xxx 2

......

----------------------------------------------------------------------------

总结：

数据库三范式是理论上的，实践与理论之间是存在偏差的，最终的目的都是为了满足客户的需求，有的时候会拿冗余换速度

因为在sql中，表与表之间的连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。