在单个SQL表中表示记录之间的多对多关系的最佳方法是什么？

Question

我有一个这样的SQL表：

更新：我正在更改示例表，因为原始数据（州，城市，学校）的现有层次结构性质掩盖了项目之间需要简单关系的事实。

entities
id      name               
1       Apple     
2       Orange            
3       Banana             
4       Carrot                
5       Mushroom

我想在这些实体之间定义双向关系，以便查看一个实体的用户可以看到所有相关实体的列表。

这些关系是由最终用户定义的。

在数据库中表示这些关系并随后查询和更新它们的最佳方法是什么？

我所看到的一种方式…

我的直觉说像这样的关系表：

entity_entity
entity_id_a       entity_id_b
1                 2
5                 1
4                 1
5                 4
1                 3

在这种情况下，假设提供的entity_id为4，将如何获得所有相关记录（分别为1和5）？

同样，对entity_id = 1的查询应返回2、3、4和5。

感谢您的时间，让我知道是否可以澄清所有问题。

Answer 1

定义约束：entity_id_a < entity_id_b。

创建索引：

CREATE UNIQUE INDEX ix_a_b ON entity_entity(entity_id_a, entity_id_b);
CREATE INDEX ix_b ON entity_entity(entity_id_b);

第二个索引不需要包括在内，entity_id_a因为您将使用它仅选择a一个内的所有索引b。RANGE SCANonix_b将比SKIP SCANon快ix_a_b。

使用您的实体填充表，如下所示：

INSERT
INTO entity_entity (entity_id_a, entity_id_b)
VALUES (LEAST(@id1, @id2), GREATEST(@id1, @id2))

然后选择：

SELECT entity_id_b
FROM entity_entity
WHERE entity_id_a = @id
UNION ALL
SELECT entity_id_a
FROM entity_entity
WHERE entity_id_b = @id

UNION ALL 在这里，您可以使用上述索引，并避免为了唯一性而进行额外的排序。

以上对于对称和反身关系都是有效的。这意味着：

• 如果 一个 关系到 b ，则 b 是关系到 一个

• a* 从未与 a 相关 *