在单个SQL表中记录之间表示多对多关系的最佳方式是什么？ [英] What is the best way to represent a many-to-many relationship between records in a single SQL table?

问题描述

我有一个SQL表如下：

更新：我将示例表更改为原始数据（州，城市，学校）掩盖了一个事实，即项目之间需要一个简单的关系。

 实体
 id name 
 1 Apple 
 2 Orange 
 3 Banana 
 4 Carrot 
 5蘑菇
  

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

在数据库中表示这些关系，然后查询和更新这些关系的最佳方式是什么？

我看到的一种方式...

我的直觉表示这样的关系表：

  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。

解决方案

感谢您的时间，让我知道是否可以澄清这个问题。 >定义约束： 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_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 并避免对唯一性进行额外排序。

上述内容对于对称和反反射关系都有效。这意味着：

• 如果 a 与 b 那么
  b 与




• 与

I have a SQL table like so:

Update: I'm changing the example table as the existing hierarchical nature of the original data (State, Cities, Schools) is overshadowing the fact that a simple relationship is needed between the items.

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

I want to define two-way relationships between these entities so a user viewing one entity can see a list of all related entities.

The relationships are defined by an end user.

What is the best way to represent these relationships in the database and subsequently query and update them?

One way as I see it...

My instinct says a relationship table like so:

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

That being the case, given a supplied entity_id of 4, how would one get all related records, which would be 1 and 5?

Likewise a query of entity_id = 1 should return 2, 3, 4, and 5.

Thanks for your time and let me know if I can clarify the question at all.

解决方案

Define a constraint: entity_id_a < entity_id_b.

Create indexes:

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);

Second index doesn't need to include entity_id_a as you will use it only to select all a's within one b. RANGE SCAN on ix_b will be faster than a SKIP SCAN on ix_a_b.

Populate the table with your entities as follows:

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

Then select:

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 here lets you use above indexes and avoid extra sorting for uniqueness.

All above is valid for a symmetric and anti-reflexive relationship. That means that:

• If a is related to b, then b is related to a

• a is never related to a

这篇关于在单个SQL表中记录之间表示多对多关系的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！