Web开发中常用的数据关系

数据关系

Web开发中，需要使用数据库存储数据。这些数据大多数都是相互关联的，存储和使用这样的数据，我常用关系型数据库Mysql来实现。这里，我设计一个用户数据模型，用来举例说明这些数据之间的关系。

独立的数据

我规划了一张表user，用来存储用户基础信息，字段包含主键id和用户名username，看起来这张表是这样的：

id	username
1	张三
2	李四
3	王五

当我们查询user表时，就会得到包含这两个字段的数据行。这样的数据，与其他表没有任何关联，和表本身也没有关联，可以被称为独立的数据，这样的数据，非常适合存储到非关系数据库中。不过，我们的用户表还会修改设计，因为，用户表会关联其他表。

一对一关系

我们的用户表中，字段太少了，不能描述用户的丰富属性。这时候，我们有两种选择：
　　1. 给用户表增加字段
　　2. 使用一张新的表格（用户信息表user_info）来存储用户扩展信息
直接给用户表增加字段，适合用户字段本身不多，并且增加的字段不会影响其他字段的情况；否则，我们可以新建一张表来存储这些用户信息。用户信息表设计如下：

id	sex	email
1	男	zs@test.com
2	男	ls@test.com
3	男	ww@test.com

那么，两张表如何关联呢？
根据设计要求，一条用户表的数据，对应一条用户信息表的数据，这样的对应关系，称为一对一关系。既然如此，我们可以给用户表增加一个字段，存储对应用户信息表的主键。这样一来，我们想要知道用户的扩展信息，需要两步操作：１、查找用户表，找到用户的关联用户信息表的主键。２、根据用户信息表的主键，查找到用户的扩展信息。
根据用户信息表的一条数据的主键，到用户表中，也可以查询到用户表中的具体数据。
更改后的用户表如下：

id	username	info_id
1	张三	1
2	李四	2
3	王五	3

当然，我们也可以将关联主键的字段放到用户信息表中，这样的话，可以保持用户表字段较少，那么我们就不要修改用户表，用户信息表修改如下：

id	sex	email	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	email
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训练营 第九篇