Web开发中，需要使用数据库存储数据。这些数据大多数都是相互关联的，存储和使用这样的数据，我常用关系型数据库Mysql来实现。这里，我设计一个用户数据模型，用来举例说明这些数据之间的关系。
独立的数据
我规划了一张表user，用来存储用户基础信息，字段包含主键id和用户名username，看起来这张表是这样的：
| id | username |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
当我们查询user表时，就会得到包含这两个字段的数据行。这样的数据，与其他表没有任何关联，和表本身也没有关联，可以被称为独立的数据，这样的数据，非常适合存储到非关系数据库中。不过，我们的用户表还会修改设计，因为，用户表会关联其他表。
一对一关系
我们的用户表中，字段太少了，不能描述用户的丰富属性。这时候，我们有两种选择：
1. 给用户表增加字段
2. 使用一张新的表格（用户信息表user_info）来存储用户扩展信息
直接给用户表增加字段，适合用户字段本身不多，并且增加的字段不会影响其他字段的情况；否则，我们可以新建一张表来存储这些用户信息。用户信息表设计如下：
| id | sex | |
|---|---|---|
| 1 | 男 | zs@test.com |
| 2 | 男 | ls@test.com |
| 3 | 男 | ww@test.com |
那么，两张表如何关联呢？
根据设计要求，一条用户表的数据，对应一条用户信息表的数据，这样的对应关系，称为一对一关系。既然如此，我们可以给用户表增加一个字段，存储对应用户信息表的主键。这样一来，我们想要知道用户的扩展信息，需要两步操作：１、查找用户表，找到用户的关联用户信息表的主键。２、根据用户信息表的主键，查找到用户的扩展信息。
根据用户信息表的一条数据的主键，到用户表中，也可以查询到用户表中的具体数据。
更改后的用户表如下：
| id | username | info_id |
|---|---|---|
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
当然，我们也可以将关联主键的字段放到用户信息表中，这样的话，可以保持用户表字段较少，那么我们就不要修改用户表，用户信息表修改如下：
id sex user_id 1 男 zs@test.com 1 2 男 ls@test.com 2 3 男 ww@test.com 3 甚至，我们可以用户表存储用户信息表的对应主键，并且用户表存储用户信息表的主键，这样做会增加一些存储成本，不过可以提高可读性。
多对一关系
多对一关系，也叫作一对多关系。网站一般会对用户进行分组，我们也需要设计用户分组表，如下：
| id | groupname |
|---|---|
| 1 | 管理员 |
| 2 | 会员 |
一个用户分组中包含了若干个用户，如何存储这样的关系呢？我们可以考虑，在用户组表中增加一个字段，来存储其包含的用户的主键，如下：
| id | groupname | user_id_list |
|---|---|---|
| 1 | 管理员 | 1,2 |
| 2 | 会员 | 3 |
我们知道，关系数据库的字段只能存储简单的数据类型，例如数字，字符串，时间等，不支持存储一个列表。所以这里，我们需要将列表数据转换成一个字符串来存储，取出来用的时候，我们需要再将其转换成列表。每次用户组的用户变动时，需要对字段user_id_list改动时，则变得麻烦。这时候，推荐另一种多对一的关联方式：在用户表中添加字段group_id，如下：
| id | username | info_id | group_id |
|---|---|---|---|
| 1 | 张三 | 1 | 1 |
| 2 | 李四 | 2 | 1 |
| 3 | 王五 | 3 | 2 |
更改用户组的用户成员时候，只需要修改对用用户的group_id即可，查询时根据group_id查询即可。
多对多关系
不同的用户组权限不同，我们需要设计一张权限表，如下：
| id | permissions |
|---|---|
| 1 | 禁用用户 |
| 2 | 修改自己的信息 |
管理员不仅可以修改自己的信息，还可以禁用其他用户，而会员只能修改自己的信息。这时候我们可以发现，管理员对应的权限有两个：禁用用户和修改自己的信息。而修改自己的信息这项权限，同时被两个用户组拥有：管理员和会员。想这样的关系，是典型的多对多关系。
如何根据这样的关系，设计表呢？
根据之前的经验，我们会想到，给用户组表或者权限表增加字段，用来存储其对应的关系表的主键。使用这样的办法，我们就会陷入到多对一关系中的不良案例中——利用一个字段存储多个关系主键。
这时候，我们需要跳出原有思维，新建一张用户组－权限关系表(group_permissions)，来存储他们的多对多关系，如下：
| id | group_id | permissions_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
使用查询语句select permissions_id from group_permissions where group_id=1来查询管理员拥有的权限id列表；使用查询语句select group_id from group_permissions where permissions_id=2来查询拥有需改自己的信息的用户组id列表。
自关联一对一关系
网站设计了推荐机制，如果一个用户通过另一个用户推荐注册，那么保存这个用户的推荐人。分析得知，一个用户的推荐人只能是另一个已存在的用户，这是一对一关系，区别是，这个一对一关系，关联的是用户表本身。
解决方案仍然是老办法，给用户表增加一个字段recommended_id，用来关联其对应的推荐人。用户表如下：
| id | username | info_id | group_id | recommended_id |
|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | null |
| 2 | 李四 | 2 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 |
自关联多对一关系
网站设计黑名单机制，用户可以拉黑不喜欢的其他用户。分析得知，一个用户可以拉黑多个其他用户，那么这是一个自关联的多对一关系。
就像之前的多对一关系那样，我们尝试使用增加字段的方式来解决这个问题。设计如下：
| id | username | info_id | group_id | recommended_id | blacklist_id_list |
|---|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | null | 2, 3 |
| 2 | 李四 | 2 | 1 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 | 1, 2 |
可以看出来，又陷入到了单一字段存储多个关系主键的问题了。解决办法也一样，单独创建一张黑名单关系表(blacklist)来存储黑名单关系，如下：
| id | user_id | blacklist_user_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
自关联多对多关系
网站需要增加粉丝和关注机制，用户可以关注其他用户，用户可以得知自己有哪些粉丝。分析得知，这是典型的自关联多对多关系。
尝试使用一张单独的粉丝关系表(fans)来存储分析关系，如下：
| id | user_id | fans_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
经简单的验证，可以得知，这样的设计可以完全满足粉丝机制的需求。
总结
通过网站用户和权限的简单设计案例，我们梳理了独立数据、一对一关系、多对一关系、多对多关系、自关联一对一关系、自关联多对一关系和自关联多对多关系的需求分析和设计实现，可以自如地处理这些复杂的关联关系。
最终的字段和数据如下：
user
| id | username | info_id | group_id | recommended_id |
|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | null |
| 2 | 李四 | 2 | 1 | 1 |
| 3 | 王五 | 3 | 2 | 2 |
user_info
| id | sex | |
|---|---|---|
| 1 | 男 | zs@test.com |
| 2 | 男 | ls@test.com |
| 3 | 男 | ww@test.com |
group
| id | groupname |
|---|---|
| 1 | 管理员 |
| 2 | 会员 |
permissions
| id | permissions |
|---|---|
| 1 | 禁用用户 |
| 2 | 修改自己的信息 |
group_permissions
| id | group_id | permissions_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
blacklist
| id | user_id | blacklist_user_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
fans
| id | user_id | fans_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
无戒365训练营 第九篇
