Mysql学习日记：L28-数据库设计三范式_一对多两张表口诀-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_32833675/article/details/120790026

什么是数据库设计范式

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

一、数据库设计三范式

数据库设计范式有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语句编写的难度也会降低