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

Question

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

sqldatabasemany-to-manyentity-relationshiprelational

6

我有一个类似于以下的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

如果给定一个实体ID为4，如何获取所有相关记录（即1和5）？

同样，查询实体ID = 1应返回2、3、4和5。

感谢您的时间，如果需要澄清问题，请告诉我。

- GloryFish

8个回答

1

链接表的方法似乎不错，但你可能需要一个“关系类型”，以便知道它们为什么相关。

例如，Raleigh和North Carolina之间的关系与Raleigh和Durham之间的关系不同。此外，您可能想知道在关系中谁是“父级”，以防您正在驱动条件下拉列表。(即，您选择一个州，您可以看到该州中的城市)。

根据您的需求复杂程度，您现在拥有的简单设置可能不足够。如果您只需要显示两个记录以某种方式相关，则链接表应该足够。

- Jay S

我明白你的意思。在这种情况下，我们特别不表示层次结构。在该系统中只会有一个状态，并且关系不会用于钻取式导航。 - GloryFish

1

我认为你提出的结构很好。

要获取相关记录，可以这样做

SELECT related.* FROM entities AS search 
LEFT JOIN entity_entity map ON map.entity_id_a = search.id
LEFT JOIN entities AS related ON map.entity_id_b = related.id
WHERE search.name = 'Search term'

希望这能有所帮助。

- Tim Wardle

如果我的搜索词匹配的实体ID仅出现在地图中的entity_id_b中，该怎么办？ - Bill Karwin

换句话说，只有在每个关系都被反向存储两次时，您的查询才有效。例如 (1,4) 和 (4,1)。 - Bill Karwin

1

我已经发布了一种在您的设计中实现它的方法，但如果您的设计有一定的灵活性并且这更符合您的需求，我也想提供这个单独的设计见解。

如果项目属于（非重叠的）等价类，则您可能希望将等价类作为表格设计的基础，其中等价类中的所有内容都被视为等效。这些类本身可以是匿名的：

CREATE TABLE equivalence_class (
    class_id int -- surrogate, IDENTITY, autonumber, etc.
    ,entity_id int
)

entity_id 应该在您的空间的非重叠分区中是唯一的。

这可以避免确保适当的左右手性或强制使用上右关系矩阵的问题。

然后您的查询会有一些不同：

SELECT c2.entity_id
FROM equivalence_class c1
INNER JOIN equivalence_class c2
    ON c1.entity_id = @entity_id
    AND c1.class_id = c2.class_id
    AND c2.entity_id <> @entity_id

或者，等价地：

SELECT c2.entity_id
FROM equivalence_class c1
INNER JOIN equivalence_class c2
    ON c1.entity_id = @entity_id
    AND c1.class_id = c2.class_id
    AND c2.entity_id <> c1.entity_id

- Cade Roux

不错！你也可以测试c2.entity_id <> c1.entity_id，而不是c2.entity_id <> @entity_id。这样你就不必两次传递@entity_id参数了。 - Bill Karwin

我原以为这是一个存储过程，但对于参数化的即席查询爱好者来说，它也是等效的。 - Cade Roux

0

select * from entities
where entity_id in 
(
    select entity_id_b 
    from entity_entity 
    where entity_id_a = @lookup_value
)

- Gordon Bell

0

我可以想到几种方法。

使用CASE的单次遍历：

SELECT DISTINCT
    CASE
        WHEN entity_id_a <> @entity_id THEN entity_id_a
        WHEN entity_id_b <> @entity_id THEN entity_id_b
    END AS equivalent_entity
FROM entity_entity
WHERE entity_id_a = @entity_id OR entity_id_b = @entity_id

或者两个过滤查询联合在一起：

SELECT entity_id_b AS equivalent_entity
FROM entity_entity
WHERE entity_id_a = @entity_id
UNION
SELECT entity_id_a AS equivalent_entity
FROM entity_entity
WHERE entity_id_b = @entity_id

- Cade Roux

0

根据您更新的模式，此查询应该可以工作：

select if(entity_id_a=:entity_id,entity_id_b,entity_id_a) as related_entity_id where :entity_id in (entity_id_a, entity_id_b)

其中 :entity_id 绑定到你正在查询的实体

- Doug Martin

-1

我的建议是，你的初始表设计很糟糕。不要在同一张表中存储不同类型的内容。（这是数据库设计的第一条规则，与不要在同一字段中存储多个信息并列）。这样做会使查询变得更加困难，并且将在以后引起重大的性能问题。此外，在输入数据到关系表时也会出现问题——当你进行新的输入时，你如何知道哪些实体需要相关联？更好的方法是设计适当的关系表。实体表几乎总是一个坏主意。从这个例子中我看不出来有任何理由把这种类型的信息放在一个表中。老实说，我会有一个大学表和一个相关地址表。这样查询会更容易，而且性能也会更好。

- HLGEM

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

定义一个约束条件：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，因为您只会使用它来选择一个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 永远不会与自己相关