[大数据]Mysql学习日记：L28-数据库设计三范式

目录

一、数据库设计三范式

二、第一范式

三、第二范式

四、第三范式

五、总结表的设计

什么是数据库设计范式

设计库表的设计依据，教你怎么进行数据库表的设计。

一、数据库设计三范式

数据库设计范式有3个

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

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

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

设计数据库的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

二、第一范式

最核心，最重要的范式，所有表的设计都需要满足，

必须有主键，并且每一个字段都是原子性不可再分

例：

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

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

1001? ? ? ? 张三? ? ? ? zs@gmail.com,1359999999

1002? ? ? ? 李四? ? ? ? ls@gmail.com,13699999999

1001? ? ? ? 王五? ? ? ? ww@163.net,13488888888

以上数据库的设计不满足第一范式

原因：

①没有主键

②联系方式可以分为邮箱和电话，不满足不可再分的原则

修改：

学生编号（PK） 学生姓名 ????????邮箱? ? ? ? ? ? ? ? ????????联系电话

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

1001? ? ? ? ? ? ? ? ? ? 张三? ? ? ????????? zs@gmail.com???????1359999999

1002? ? ? ? ? ? ? ? ? ? 李四? ? ? ????????? ls@gmail.com????????13699999999

1003? ? ? ? ? ? ? ? ? ? 王五? ? ? ????????? ww@163.net??????????13488888888

三、第二范式

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

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

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

1001 ????????张三 ????????001 ????????王老师

1002 ????????李四 ????????002 ????????赵老师

1003 ????????王五 ????????001 ????????王老师

1001 ????????张三 ????????002? ? ? ? ?赵老师

提醒：这张表描述了学生和老师的关系（1个学生可能有多个老师，1个老师可能有多个学生）

这是非常典型的多对多的关系

以上表的设计是否满足第一范式？

不满足，修改：

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

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

1001 ?????????001????????????????张三 ??????? ????????王老师

1002 ????????002????????????????李四 ???????? ????????赵老师

1003 ????????001????????????????王五 ???????? ????????王老师

1001 ????????002????????????????张三 ????????? ? ? ? ?赵老师

学生编号和教师编号两个字段联合做主键，复合主键（PK：学生编号 +教师编号），经过修改后以上的表满足了第一范式，但是不满足第二范式：‘张三’依赖1001，‘王老师’依赖001，显然产生了部分依赖。

产生部份依赖的缺点：数据冗余，空间浪费，‘张三’重复了，‘王老师‘重复了

为了让这张表满足第二范式，需要使用三张表来满足多对多的关系。

学生表

学生编号（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? ? ? ? ? ? ? ? ? ?赵六? ? ?? ? 03 ????????一年三班

上表描述的是班级和学生的关系，很显然是一对多的关系，一个教室中有多个学生

上表满足第一范式（有主键、原子性）

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

不满足第三范式（因为一年一班依赖01，01依赖1001，产生了传递依赖，不符合第三范式的要求，产生了数据的冗余）

修改：

班级表（一）

班级编号（PK）? ? ? ? ? ? ? ? ? ? ? ? 班级名称? ? ? ? ? ? ? ??

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

01? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 一年一班? ? ? ? ? ? ? ??

02? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 一年二班? ? ? ? ? ? ? ??

03? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 一年三班

学生表（多）

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

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

1001? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ????????张三? ? ? ? ? ? ? ? ????????01

1002? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 李四? ? ? ? ? ? ? ????????? 02

1003? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 王五? ? ? ? ? ? ? ????????? 03

1004? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 赵六? ? ? ? ? ? ????????? ? 03

口诀：一对多，两张表，多的表加外键

五、总结表的设计

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

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

一对一：在实际的开发中，可能存在一张表字段太多，太庞大，这个时候需要拆分表

id? ? ? ? ? ? ? ? login_id? ? ? ? ? ? ? ? login_pwd? ? ? ? ? ? ? ? real_name? ? ? ? ? ? ? ? email? ? ? ? ? ? ? ? address

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

1? ? ? ? ? ? ? ? zhangsan? ? ? ? ? ? ? ? 123? ? ? ? ? ? ? ? ? ? ? ? 张三? ? ? ? ? ? ? ? ? ? ? ? zhangsan@xxx

2? ? ? ? ? ? ? ? lisi? ? ? ? ? ? ? ? ? ? ? ? ? ?123? ? ? ? ? ? ? ? ? ? ? ? 李四? ? ? ? ? ? ? ? ? ? ? ? lisi@xxx

……

这种庞大的表建议分为两张

t_login 登陆信息表

id（PK）? ? ? ? ? ? ? ? login_id? ? ? ? ? ? ? ? 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语句编写的难度也会降低