Postgres基于数组的多对多关系实现
关系数据库系统中，对数据模型的多对多关系进行管理和操作，是非常常见的。经典的关系数据库系统使用中间关联表的方式进行处理，在处理少量和简单的数据时候，略显繁复。针对这个情况，本文提出了一种基于Postgres数组类型和相关操作，来处理多对多关系的数组的方式。在一些特定场景下，更加简单和方便。除此之外，笔者还希望通过相关技术和实现方式的探讨，帮助读者能够从不同的角度来分析问题，了解一些不同的知识和技能，并且拓展解决问题的思路和方式。
在实际内容开始之前，我们有必要先了解和熟悉一下关系数据库中几种比较常见的数据关联方式。
关系数据库中的数据关系
在关系型数据库中，任意两个有关联的数据记录集合，在数据记录之间的关系，都可以用三种类型来进行描述：
- 一对一(One-To-One)
严格而言，在数据库中实现和表达两个实体记录集合的记录之间的一对一的关系是比较多余的，它完全可以通过只使用一个表，但增加多个字段的方式来实现(即所谓的“宽表”)。
但在现实的应用场景中，可能基于业务区隔，方便管理和扩展，以及处理性能等多方面的考虑，也可能会设计多个表，并使用一对一的关系将它们关联起来。这一关系和本文主题关联不大，这里不深入讨论。
- 一对多(One-To-Many)
一对多关系，通常可以用于描述和记录树形结构(父子)的数据。一个记录(实体)可以关联多个其他记录，而每个被关联的实体只能关联一个实体。常见的例子是公司部门和员工之间的关系，每个部门可以有多个员工，而每个员工只能属于一个部门。这就是一个典型的树形结构的数据集合。
在关系型数据库的设计和实现中，这种结构通常使用外键和关联的方式来实现。在具有父子记录关系的两个表中，在子记录表上，增加一个字段作为外键来关联父表的记录ID，就可以将两类记录关联起来(详见示例数据)。在这种情况下，常见的数据处理需求和实现方式包括：
-- 建立/修改父子关联关系: 使用父记录ID更新子表中关联字段的内容
update employees set dep= 101 where id = 201 returning *;
-- 清除关联关系: 清除关联字段中的内容
-- 注意: 严格的外键定义不允许更新为不存在的值，会影响灵活性和性能
update employees set dep= nulll where id = 201 returning *;
-- 基于父记录查询子记录：从父记录表出发，直接连接查询(join)子记录表，可以有多条(或没有)
select D.*, E.id empid, E.name empname from departments D
join employees E on E.dep = D.id and D.id = 102;
-- 基于子记录查询父记录：从子记录表出发，关联查询父记录表, 应该只有1条
select E.*, D.id depid, D.name depname from employees E
join departments D on E.dep = D.id and E.id = 202;
- 多对多(Many-To-Many，下简称M2M)
多对多也是一种常见的数据和信息关联的类型。实体可以关联多个其他的实体，而被关联实体也可以关联多个实体，这样其实就可以在实体之间建立一种网络化的关联关系。如果以公司员工和他们所参与的项目为例，每个项目可以有多个员工参与，而每个员工也可以参与多个项目(见示例数据)。
一般SQL数据库并没有提供内置和显式的多对多关系映射的功能，因为这个需求可以简单的通过两个一对多的关系来实现。具体而言，就是通过建立除两个关联实体的数据表之外，第三个数据关联表。这张表通常包含两个表的外键,分别用于关联两张表的记录。如建立项目和员工的关联表,应当包括项目表的项目ID和员工表的员工ID，这个表的内容将会记录和体现项目和员工之间的关系。
这种模式下，常见的多对多关系处理操作和实现包括：
-- 为避免重复数据，创建约束
-- 建立关联关系: 在关联表中增加一条记录
insert into
update employees set dep= 101 where id = 201 returning *;
-- 清除关联关系: 删除关联表中的相关记录
delete from employees set dep= nulll where id = 201 returning *;
-- 基于父记录查询子记录：从父记录表出发，直接连接查询(join)子记录表，可以有多条(或没有)
select D.*, E.id empid, E.name empname from departments D
join employees E on E.dep = D.id and D.id = 102;
-- 基于子记录查询父记录：从子记录表出发，关联查询父记录表, 应该只有1条
select E.*, D.id depid, D.name depname from employees E
join departments D on E.dep = D.id and E.id = 202;
上面都是经典的关系型数据库处理数据关系的方式，下面我们进入本文的核心内容，基于数组来管理多对多关系的数据。这里的讨论基于Postgres数据库的数组相关功能和特性。其他的关系型数据库，如果也支持类似的数组功能，应当也是可以的。
PG数组操作
我们先来了解一下PG相关数组操作的相关特性和功能，这些内容对于后续的数据关联处理非常重要。PG可以将一个字段定义为数组的类型，并提供了相关的运算符和函数可以对其进行操作。要注意到PG中，数组的索引是从1开始的，下面这些语句可以帮助我们来理解PG是如何进行数组类型字段的操作的。
-- 查询数组和数组元素
with D(l) as (values (Array[1,2,3,4,6]), (Array[2,1,4,6]))
select *,l[2],l[2] from D ;
-- 数组转记录
with D(i,l) as (values (1,Array[1,2,3,4,6]), (2,Array[2,1,4,6]))
select i, unnest(l) from D ;
-- 记录转数组
with D as (
select 100 + generate_series(1,10) id
) select array_agg(id) from D;
-- 扩展数组
with D(a) as (values ('{1,3,5,6,8}'::int[]))
select 100 || D.a, D.a || 101, array_append(D.a,103) from D;
-- 从数组中删除值
with D(a) as (values ('{1,3,5,6,8,1}'::int[]))
select array_remove(D.a,103), array_remove(D.a,1) from D;
-- 查询数组是否包括值
with D(a) as (values ('{1,3,5,6,8,1}'::int[]))
select Array[1] <@ D.a, 1 = Any(D.a), 2 = Any(D.a) from D;
基于数组的多对多关系实现
在上一个章节中我们已经了解到Postgres提供了数组类型和相关的操作功能，我们可以使用这些特性，在不引入中间关系表的情况下，来实现两个集合记录之间的多对多关系。
以前面的项目和员工关系为例。我们可以在项目表上建立一个数组字段，这个字段数组里的内容，就是需要关联的实体ID(详见示例数据中的字段定义)。进一步的内容就是在PG中如何进行相关的操作，包括了如何建立关联数组字段，并将原有关联信息表迁移到关联数组的形式：
-- 创建关联信息数组字段
alter table projects add column emps _int2;
-- 使用关联表进行多对多关联查询(现有方式)
select E.id empid,E.name empname, P.id prjid, P.name prjname from emprojects EP
join employees E on E.id = EP.empid
join projects P on P.id = EP.prjid;
-- 将M2M关联表转换为数组形式
select prjid, array_agg(empid) from emprojects group by 1;
-- 使用关联数据更新关联数组
with U as (
select prjid, array_agg(empid) emplist from emprojects group by 1
) update projects set emps = emplist from U where prjid = id
returning projects.*
-- 使用数组进行多对多关联查询(改进方式)
with P as ( select id,name, unnest(emps) empid from projects )
select P.*, E.name empname from P join employees E on E.id = empid;
-- 直接输出树形关系
select * from projects where id = 302;
-- 增加一个关联关系(增加id到数组)
update projects set emps = array_append(emps, 211)
where id = 302 and not (211 = ANY(emps))
returning *;
-- 删除一个关联关系(从数组中移除)
update projects set emps = array_remove(emps, 211)
where id = 302 returning *;
这里相关的操作要点如下：
- 记录集转换为数组使用array_agg方法
- 数组转换为记录集使用unnest方法
- array_append可能出现重复值，可以使用条件判断来筛选
- array_append会加到数组的尾部，如果要控制位置需要使用其他方式
- array_remove可以去除所有相同(重复)的值
实际上，这个数组关联，既可以在项目表上建立，也可以在员工表上建立，在技术上是等效的。但在实现上应当从多个方面进行考虑，比如业务逻辑，数据集大小，处理性能等。比如上面这个例子就比较纠结，从业务逻辑上而言，应当将项目作为主表，这样可以不用修改员工表；但在处理性能和效率而言，却应当以员工作为主表，因为员工参与的项目是有限的，实际有效项目数据的数量有可能远远小于员工的数量；所以，如果使用这个方式，可能要具体场景具体分析，也可能没有一个通用的方式和标准。
优势和限制
经过前面的讨论，我们也可以看到，基于数组字段来实现M2M关联和操作，具有以下特点和优势:
- 简单
不需要中间关联表，只需要增加一个数组字段，就可以实现多对多的关联。并且可以简化查询时的额外关联操作和字段引用。并且可以简化输出数据(无重复和冗余信息)。
- 兼容一对多
使用数组字段，而不是单一外键数据字段，还可以兼容一对多的情况，或者限定为只关联第一个数组元素的情况。
- 结合应用服务器的能力，简化数据操作
以Nodejs的PG库为例，查询结果，可以直接输出JSON数组结构，非常方便处理和呈现。当然，如果程序库不支持相关功能，反而会带来不必要的麻烦。
当然，任何技术方案都没有完美的，这个也有一些负面的因素和限制：
- 只适用于特定和合适的场景
经典的关联表方式，虽然稍微麻烦一点，但可以适应所有应用的场景，具有很好的通用性。相对而言，使用关联数组方式，就需要结合实际的场景和情况进行评估了。
- 大型数组数据处理的效率
可以容易想象和理解。使用数组表达关联关系，应当不适合于很大的数据，因为数据库处理数组的效率，肯定不能和处理原生数据记录的方式相比。因此使用数组进行关联，数组的内容和构成数量，应当是比较确定和有限的。
- 无法附加数据和信息
使用中间关联表来实现数据关联，还有一个潜在的优势，就是在关联表中可以加入其他字段，来表示关联的附加信息，比如关联类别、关联状态、排序因子、其他关联实体等等，可以提供更多的灵活性和可能性。
- 特殊的操作方式
使用M2M关联表的操作和普通数据库表的操作无异，就是关联记录的增删改查，对于开发和理解都是非常友好的。但使用数组来处理，取决于相关数据库系统对于数组数据处理的能力，虽然PG在这方面是比较强大的，但如果要用的比较好的话，也会对开发人员有比较高的要求。
总之，使用数组的多对多数据关联方式，有其优势，也有一些缺陷，是否采用，主要取决于实际的使用场景和技术环境是否合适，如果比较合适，可以大大简化数据库的设计和冗余信息，提高开发和执行的效率。但不管情况如何，这起码也是一个新的问题处理和解决的思路和方式。
附: 示例数据
下面是本文讨论和实验涉及到的一些示例数据和数据结构。可以结合示例SQL语句，帮助读者来进行一些实际操作和理解。
部门数据表: departments
| ID | Name |
|---|---|
| 101 | 办公室 |
| 102 | 财务部 |
| 103 | 开发部 |
| 104 | 市场部 |
员工数据表 emploees
| ID | Name | Dep |
|---|---|---|
| 201 | 刘玄德 | 101 |
| 202 | 曹孟德 | 101 |
| 203 | 张翼德 | 102 |
| 204 | 关云长 | 103 |
| 205 | 赵子龙 | 103 |
| 206 | 陆伯言 | 103 |
| 207 | 马孟起 | 104 |
| 208 | 袁本初 | 104 |
| 209 | 孙仲谋 | 104 |
| 210 | 周公瑾 | 104 |
| 211 | 诸葛孔明 | 104 |
项目数据表 projects
| ID | Name |
|---|---|
| 301 | 官渡之战 |
| 302 | 赤壁之战 |
| 303 | 夷陵之战 |
项目员工(关联)表：
| PrjID | EmpID | Prj(关联) | Emp(关联) |
|---|---|---|---|
| 301 | 202 | 官渡之战 | 曹孟德 |
| 301 | 208 | 官渡之战 | 袁本初 |
| 302 | 201 | 赤壁之战 | 刘玄德 |
| 302 | 202 | 赤壁之战 | 曹孟德 |
| 302 | 210 | 赤壁之战 | 周公瑾 |
| 303 | 201 | 夷陵之战 | 刘玄德 |
| 303 | 206 | 夷陵之战 | 陆伯言 |
改进后的项目数据表 projects (带关联员工，数组方式):
| ID | Name | Employees |
|---|---|---|
| 301 | 官渡之战 | [202,208] |
| 302 | 赤壁之战 | [201,202,210] |
| 303 | 夷陵之战 | [201,206] |