在单个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.

感谢您的时间,让我知道我是否可以澄清这个问题.

解决方法

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,因为您只使用它来选择一个b中的所有a. ix_b上的RANGE SCAN将比ix_a_b上的SKIP SCAN快.

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

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在这里允许您使用上面的索引并避免额外的唯一性排序.

以上所有都适用于对称和反自反的关系.这意味着：

>如果a与b相关,那么b与a有关> a永远不会与a相关

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!