438-MySQL(表设计,范式)

以你之姓@ 2022-09-05 12:52 6阅读 0赞

## 关系型数据库表设计 ##

每个表代表一个实体，每一个实体代表一张表，表与表的关系对应实体与实体的关系

**表与表的关系分为：**  
**一对一  
一对多  
多对多**

**一对一的关系：**  
假设我们有2张表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**1个用户对应1个身份信息，1个身份信息对应1个用户**。  
但是这样设计表，并不能获取uid为1000的zhang同学的身份信息。因为目前没有关联。**然后我们这么做**，在身份信息表增加1列 uid，这样就有关联了。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**我们把依赖的称为子表，被依赖的称为父表。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
查wang同学的身份信息：  
![在这里插入图片描述][c3ac6940d1a94eefa2adcccca3868752.png]  
**子表添加的uid相当于外键，关联了父表的主键**  
**子表的外键和父表的主键的数据类型必须是一致的，列名最好是一样！**  
我们现在是不会在DB表增加外键，不会让外键的约束由mysql去做，因为会增加mysql-server的负担。我们让mysql-server做核心的CRUD，限制逻辑由我们业务层逻辑层去控制。

**一对多的关系：**  
我们做1个电商系统：  
![在这里插入图片描述][532328b42b62464a8cad6b1bcf9a4226.png]  
在形成订单之前，**用户和商品**之间没有直接的关联关系。  
**用户-订单**：1个用户有多个订单，1个订单属于1个用户。属于1对多的关系。  
**商品-订单**：1个商品属于多个订单，1个订单可以有多个商品。属于多对多的关系。  
![在这里插入图片描述][14ecf29f29b443e59b06b368a4a1eea9.png]  
我们设计用户表和商品表如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
我们设计订单表如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]  
**用户-订单**：目前订单表中没有和用户表任何相关的字段，我们现在在订单表中增加一列。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
**一对多，也就是在子表增加一列uid，关联父表的主键。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]

**多对多的关系：**  
商品-订单：商品1张表，订单1张表，但是订单这张表存储的数据太过于冗余了！！！如果同一个订单有100个商品，就要存储100条记录！！！  
![在这里插入图片描述][f1ff7e15ef86481cab19d18fc22397e2.png]  
而且上面显示的这2列是不会改变的，造成数据的冗余。而且在订单中加1个商品减1个商品，就要进行大批量的相同操作的修改。一个订单不需要存储同样的多个的uid号，总价值，配送地址，这些东西存1份就可以了。唯有和商品相关的pid，number，momeny，需要进行多份。

![在这里插入图片描述][be52c2a5a8c6464195d5e48ff5db4247.png]  
**我们增加1个中间表，然后修改订单表。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]  
**在订单表通过O1000在订单内容表查询这个订单的所有商品信息。  
在商品表里，pid可以作为主键。在订单这个表里，orderid可以作为主键。  
在订单内容表里，pid和orderid组合起来作为主键！！！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
这样就避免了订单表的数据冗余的问题。

## 关系型数据库范式 ##

**关系型数据库范式**  
应用数据库范式可以带来许多好处，**但是最重要的好处归结为三点：**

**1）减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）  
2）消除异常（插入异常，更新异常，删除异常）  
3）让数据组织的更加和谐**

**但是数据库范式绝对不是越高越好，范式越高，意味着表越多，多表联合查询的机率就越大，SQL的效率就变低。**

**第一范式（1NF）**

每一列保持原子特性

**列都是基本数据项，不能够再进行分割**，否则设计成一对多的实体关系。例如表中的地址字段，可以再细分为省，市，区等不可再分割（即原子特性）的字段，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
**上图的表就是把地址字段分成更详细的city，country，street三个字段，注意，不符合第一范式不能称作关系型数据库。**

**分裂好出来后，现在就是：我们根据人的id查到他地址的id，然后用他地址的id去专门存储地址信息的表里查所有的信息。**  
![在这里插入图片描述][d817d5deac784e258acd08fdcf35e5e5.png]

**第二范式（2NF）**

属性完全依赖于主键-主要针对联合主键

**非主属性完全依赖于主关键字，如果不是完全依赖主键，应该拆分成新的实体，设计成一对多的实体关系。**

**例如**：选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，（学号，课程名称）是联合主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键的其中一个字段，**不符合第二范式**。  
**这样的话就会造成数据（学分）的冗余！学分只依赖于课程，姓名和年龄只和学号有关**

![在这里插入图片描述][cd4bc698d3314c05981bf0762358772c.png]  
**学生-课程：多对多的关系。**  
**我们应该这么设计：**  
![在这里插入图片描述][b65f3ce40a9946508643e169036dc2e3.png]  
**我们再看另一个例子**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
**一个部门有多个部员** **一个部员属于1个部门**

**一个表有联合主键，不是主键的那些列必须和联合主键的那些列有依赖关系。**

**第三范式（3NF）**

属性不依赖于其它非主属性，只依赖于主属性

**要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。**

**示例**：学生关系表为Student（学号， 姓名， 年龄， 所在学院， 学院地点， 学院电话），学号是主键，**但是学院电话和学院地址只依赖于所在学院，并不依赖于主键学号（依赖非主属性了）**，因此该设计**不符合第三范式**，应该把学院专门设计成一张表，**学生表和学院表**，**两个是一对多的关系。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
**注意**：**一般关系型数据库满足第三范式就可以了**。

**多表的查询比单表的查询慢。为了速度，有时候会故意增加冗余的数据，但是冗余存储可能会造成更新或者删除的异常。**

**BC范式（BCNF）**

每个表中只有一个候选键（即主键），其他的字段是可以重复的，而不是每一行都是不一样的。

简单的说，**BC范式是在第三范式的基础上的一种特殊情况**，即每个表中**只有一个候选键**（**在一个数据库中每行的值都不相同，则可称为候选键**），在上面第三范式的noNF表（上面图3）中可以看出，每一个员工的email都是唯一的（不可能两个人用同一个email），则此表不符合BC范式，对其进行BC范式化后的关系图为:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]

**第四范式（4NF）**

消除表中的多值依赖

简单来说，**第四范式就是要消除表中的多值依赖**，**也就是说可以减少维护数据一致性的工作**。比如图4中的noNF表中的skill技能这个字段，有的人是“java，mysql”，有的人描述的是“Java，MySQL”，这样数据就不一致了，**解决办法就是将多值属性放入一个新表**，**所以满足第四范式的关系图如下：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
**从上面对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多。表多会带来很多问题：**  
1、 查询时需要连接多个表，增加了SQL查询的复杂度  
2 、查询时需要连接多个表，降低了数据库查询性能

**因此，并不是应用的范式越高越好，视实际情况而定。第三范式已经很大程度上减少了数据冗余，并且基本预防了数据插入异常，更新异常，和删除异常了。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/3a1f581a507c4ffaa167c994e173912d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/b8ba5a5999f947f38acad4732b9c45f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/bd85e314479547498f3d49faa845d189.png
[c3ac6940d1a94eefa2adcccca3868752.png]: /images/20220829/251461311f1d4bd384792f0377037892.png
[532328b42b62464a8cad6b1bcf9a4226.png]: /images/20220829/7b1b492477c34d819186e1805dba6a63.png
[14ecf29f29b443e59b06b368a4a1eea9.png]: /images/20220829/58f97d09de934fcc888eba9b240f1de7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/46b8732a457a4a84961574aa2a12df42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/4ed7b0d78c2c4aefb7e7a6cd843d5d6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/e24c5fee78d844659c1d163e28d8ec73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/d696a4b9b12f4018864f403512289418.png
[f1ff7e15ef86481cab19d18fc22397e2.png]: /images/20220829/5d8670d458384a8a808363c64ebb2fec.png
[be52c2a5a8c6464195d5e48ff5db4247.png]: /images/20220829/4c98f4469dd04c58a22297acc6bd9f12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/cd530087516e4979b014a2d35df8ef19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/0be57162356d44bf9a9eac9a0ca54079.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/931982da6a2b4f628ca3864ebc470f53.png
[d817d5deac784e258acd08fdcf35e5e5.png]: /images/20220829/4b159719e00e4663b087af681a9a1e75.png
[cd4bc698d3314c05981bf0762358772c.png]: /images/20220829/9d0ce47cdfcf41e0b74ebb339297ced4.png
[b65f3ce40a9946508643e169036dc2e3.png]: /images/20220829/4a6b97727ad24d80b5447cebf547729c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/6060dc8eb35540f2a64dced219a0a008.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/637afedfda894bc8a818a7a9e1423017.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/132a8412b34e425aba5780864f2d30da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/d7a320b2b2234efcac4a0dde76542886.png