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

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

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

推荐阅读更多精彩内容