MySQL_14数据库设计三范式

MySQL_14数据库设计三范式

1.什么是数据库设计范式?

数据库表的设计依据。

2.数据库设计三范式各自的要求

  • 数据库设计第一范式:
    • 要求任何一张表都必须有主键。
    • 每一个字段都要具有原子性,不可再分。
  • 数据库设计第二范式:
    • 建立在第一范式基础上。
    • 要求所有非主键字段完全依赖主键,不要产生部分依赖。
  • 数据库设计第三范式:
    • 建立在第二范式基础上。
    • 要求要求所有非主键字段直接依赖主键,不要产生传递依赖。

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

3.数据库设计第一范式

最核心、最重要的范式,所有表的设计都需要满足该范式。

要求:

  • 必须有主键。
  • 每一个字段都要具备原子性,不可再分。

我们来看下面这张表:

学生编号     学生姓名     联系方式
--------------------------------------------
1001	    张三		  zs@qq.com,12345555555
1002	    李四		  li@163.com,12131466666
1001   	    王五		  ww@qq.com,17888888888

这张表明显不符合第一范式的要求:

  1. 学生编号有重复的,没有主键。
  2. 联系方式可再分为邮箱和电话,不具备原子性。

按照第一范式进行更改:

学生编号(pk) 学生姓名    邮箱地址       电话号码
---------------------------------------------
1001	    张三		 zs@qq.com    12345555555
1002	    李四		 li@163.com   12131466666
1003   	    王五		 ww@qq.com    17888888888

4.数据库设计第二范式

要求:

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

我们来看下面这张表:

学生编号		学生姓名		教师编号		教师姓名
---------------------------------------------------
1001		   张三		    001 		   王老师
1002 		   李四 			002 		   赵老师
1003 		   王五 			001 		   王老师
1001 		   张三 			002 		   赵老师

这张表描述了学生和教师的关系:(1个学生可能有多个老师,1个老师有多个学生)

是非常典型的:多对多关系。

分析是否符合第一范式要求:不符合,没有主键。

按照第一范式的要求修改:

学生编号 + 教师编号(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
    

    口诀:多对多,三张表,关系表两个外键。

5.数据库设计第三范式

要求:

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

分析下面这张表:

学生编号(PK) 学生姓名	班级编号	班级名称
-----------------------------------------
1001 		 张三 	 01 		一年一班
1002 		 李四 	 02 		一年二班
1003 		 王五 	 03 		一年三班
1004 		 赵六 	 03 		一年三班

以上表的设计是描述班级和学生之间的关系。

一个班级里有多个学生,是一对多的关系。

是否满足第一范式要求?

? 满足。有主键;所有字段不可再分。

是否满足第二范式要求?

? 满足。主键不是复合主键,没有产生部分依赖。

是否满足第三范式要求?

? 不满足。班级名称依赖班级编号,班级编号依赖学生编号,产生了传递依赖。

? 不符合第三范式的要求,产生了数据冗余。

那么该如何设计一对多呢?

可以分为两个表:

  • 班级表:一

    班级编号(pk)	班级名称
    ------------------------
    01 			   一年一班
    02 			   一年二班
    03 			   一年三班
    03 			   一年三班
    
  • 学生表:多

    学生编号(PK) 学生姓名	班级编号(fk)
    ----------------------------------
    1001 		 张三 	 	01 	
    1002 		 李四 	 	02 	
    1003 		 王五 	 	03 	
    1004 		 赵六 	 	03 	
    

口诀:一对多,两张表,多的表加外键。

6.总结

  1. 一对多:

    • 一对多,两张表,多的表加外键。
  2. 多对多:

    • 多对多,三张表,关系表两个外键。
  3. 一对一:

    • 一对一拆分表的情况

      • 在实际开发中,可能存在一张表中的字段太多、太庞大的情况。这种时候就需要拆分表。

      • 没拆分前:一张表

        t_user:

        id		login_name		login_pwd		real_name		email		address...
        -------------------------------------------------------------------------------
        1		zhangsan		123456			张三			   zs@qq.com	北京
        2		lisi			123456			李四			   ls@qq.com	上海
        3		wangwu			123456			王五			   ww@163.com	广州
        ...
        

        这种庞大的表建议拆分成两张:

        t_login:登录信息表

        id(pk)	login_name		login_pwd
        ----------------------------------
        1		zhangsan		123456	
        2		lisi			123456	
        3		wangwu			123456		
        ...
        

        t_user:用户详细信息表

        id(pk)		real_name		email		address ...		login_id(fk+unique)
        -------------------------------------------------------------------------------
        1001		张三			   zs@qq.com	北京			  1
        1002		李四			   ls@qq.com	上海			  2
        1003		王五			   ww@163.com	广州			  3
        ...
        

7.以满足客户需求为基准

数据库设计三范式是理论上的。

而实践和理论往往有偏差。

比如为了满足客户需求,有时会拿冗余换执行速度。

就比如上面的一些分表操作,虽然减少了内存的浪费,但表一多,连接的次数也就变多了。

在sql中,表与表之间的连接次数越多,效率越低。(笛卡尔积现象)

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

总而言之,一切的最终目的都是满足客户需求。

MySQL_14数据库设计三范式

上一篇:Oracle和SQLite位数不足补0


下一篇:三、mongodb副本集搭建