MySQL_表设计原则
内容
- 给你一个场景，让你设计表。
- 关系型数据库的范式设计。
- 一对一/一对多实体关系的表设计原则
- 多对多实体关系的表设计原则
- 总结 - 表设计三要素
表间的关系
现实中，表与表之间的关系无非就三种：一对一、一对多、多对多。
如何设计表之间的关联关系？
一对一
举个例子：用户User和身份信息Info的关系，一个用户对应一个身份信息。两张表是一对一的关系。
- 用户User
| uid(主键、自增) | name | age | sex |
|---|---|---|---|
| 1000 | zhang | 20 | M |
| 1020 | liu | 21 | W |
| 2010 | wang | 22 | M |
- 身份信息Info
| cardid | addrinfo |
|---|---|
| 112233 | china |
| 334455 | japan |
| 556677 | canada |
目前来看，身份信息表和用户表完全对应不上。
需要在身份信息表加一个关联父表主键的字段uid，才能和用户表对应上。
| uid | cardid | addrinfo |
|---|---|---|
| 1000 | 112233 | china |
| 1020 | 334455 | japan |
| 2010 | 556677 | canada |
现在，两个表之间的关联就是靠Info表的uid建立起来的，即Info表的uid字段关联了父表的主键，Info表的uid在逻辑上实际上就是外键，Info叫做子表，User叫做父表。子表的外键和父表的主键的数据类型必须一致，字段名无所谓。
mysql的性能瓶颈主要集中在磁盘IO，出于对性能的优化考虑，我们现在不会在sql层明确指出这是一个外键，不会把外键的约束工作分配给mysql。而是把外键的处理我们放到应用层代码。以让sql处理更核心的业务。
使表之间能够一对一关联起来的做法：在子表中增加一列，与父表的主键关联。
1 | select * from info where uid=2010; #如此就相当于间接查到“王”的身份信息 |
一对多/多对多
举个例子：电商平台。有三个实体：用户User、商品Product、订单Order。
分析
- 用户 - 商品：在用户购买商品之前，它们之间没有直接的关联关系
- 用户 - 订单：一对多的关系
- 商品 - 订单：多对多的关系
表设计
- 用户User表
| uid | name | age | sex |
|---|---|---|---|
| 1000 | zhang | 20 | M |
| 1020 | liu | 21 | W |
| 2010 | wang | 22 | M |
- 商品Product表
| pid | pname | price | amount |
|---|---|---|---|
| 1 | phone | 600.0 | 100 |
| 2 | laptop | 2000.0 | 50 |
| 3 | battery | 10.0 | 200 |
- 订单Order表
| orderid | pid | number | money | totalprice | addrinfo |
|---|---|---|---|---|---|
| O1000 | 1 | 1 | 600.0 | 4640.0 | 西安工业大学 |
| O1000 | 2 | 2 | 4000.0 | 4640.0 | 西安工业大学 |
| O1000 | 3 | 4 | 40.0 | 4640.0 | 西安工业大学 |
| O2000 | 2 | 1 | 2000.0 | 2000.0 | 陕西科技大学 |
用户和订单 - 一对多
订单中没有用户的信息，对应不上是哪个用户的订单。
则需要给订单加一个用户id字段，需与主表的主键关联，类型必须一致。即可与用户对应。
| orderid | uid | pid | number | money | totalprice | addrinfo |
|---|---|---|---|---|---|---|
| O1000 | 1000 | 1 | 1 | 600.0 | 4640.0 | 西安工业大学 |
| O1000 | 1000 | 2 | 2 | 4000.0 | 4640.0 | 西安工业大学 |
| O1000 | 1000 | 3 | 4 | 40.0 | 4640.0 | 西安工业大学 |
| O2000 | 2010 | 2 | 1 | 2000.0 | 2000.0 | 陕西科技大学 |
商品和订单 - 多对多
目前的订单表的设计是有很多问题的，信息冗余过多。如果某用户在同一订单中购买了多种商品，则orderid、uid、totalprice和addrinfo都是一致的，为每一种商品都单列一条记录，冗余过多。
冗余带来的问题有很多，其中最大的问题是，如果用户对订单进行增删改查，则有可能影响整个有关此订单的所有记录，修改代价高。
如果订单(Order)表像下面这样，就很简洁了。这就不存在冗余了。如果需要更改订单，不用删除多行记录，只需修改一次。
| orderid | uid | totalprice | addrinfo |
|---|---|---|---|
| O1000 | 1000 | 4640.0 | 西安工业大学 |
| O2000 | 2010 | 2000.0 | 陕西科技大学 |
但是还要知道订单的内容，需要一个中间表 - 订单内容OrderList。
| orderid | pid | number | money |
|---|---|---|---|
| O1000 | 1 | 1 | 600.0 |
| O1000 | 2 | 2 | 4000.0 |
| O1000 | 3 | 4 | 40.0 |
| O2000 | 2 | 1 | 2000.0 |
由此设计，商品表和订单表则不会出现数据冗余。
总结
在进行表的设计时，考虑三个问题
- 表字段数据类型的斟酌
- 完整性约束条件的规范
- 实体关系对应的表设计原则
实体之间的关系，无非是一对一、一对多、多对多。
一对一、一对多中，子表和父表的关联可由子表增加一列字段来关联父表的主键。
而多对多关系中，除了增加一列字段来关联父表，还需要增加一个中间表来消除冗余。