存储对称的多对多关系

Question

所以我有一个扩展现有代码库的项目，它是在MySQL数据库上构建的。我有一个表，它的元组表示建筑物中的位置，我正在制作一个表来表示连接图中这些位置的边，用于寻路。这只是一个简单的两列表，每列都有一个ID，该ID是引用位置表的外键。总之，情况是这样的：

CREATE TABLE node (
    ID int(10) AUTO_INCREMENT NOT NULL,
    (...)
    PRIMARY KEY(ID)
);
CREATE TABLE edge(
    ID_1 int(10),
    ID_2 int(10),
    FOREIGN KEY (ID_1) REFERENCES node(ID),
    FOREIGN KEY (ID_2) REFERENCES node(ID)
);

这是一个现有的假设，即图是对称的，即如果节点A有一条到节点B的边，则节点B必须有一条对节点A的边。单向门的情况在这里被忽略，但似乎不适用于我的域。因此，给定节点A和B是连接的，它们的连接可以通过以下三种方式中的任何一种存储在边缘表中：

{(A,B)}
{(B,A)}
{(A,B),(B,A)}

我用这个表来回答的问题是"给定这个节点，它的邻居是什么节点？"目前我的应用程序使用这样的查询来实现这一点：

SELECT * FROM node
WHERE ID IN(
    SELECT ID_2
    FROM edge
    WHERE ID_1=?
    UNION
    SELECT ID_1
    FROM edge
    WHERE ID_2=?
);

然而，我的直觉是使用CHECK约束来确保只能存储后一种结构，MySQL显然不支持这种结构。根据这个答案，为了模拟这样的约束，我必须为每个操作编写触发器，使每个边都存储在两个顺序中。优点是它的性能更符合你的预期，并且只需就可以回答这个问题

SELECT * FROM node
WHERE ID IN(
    SELECT ID_2
    FROM edge
    WHERE ID_1=?
);

当我读到关于模拟CHECK的触发器时，我和一位朋友讨论了一下，他建议确保反射存储是浪费的（存储使用量翻倍），应该让应用程序正确处理数据库。现在我有点不确定什么是真正的最佳解决方案——数据库应该通过使用触发器加倍插入和删除来确保反射性，还是应用程序应该继续读取非反射性存储的数据，使其看起来像反射性？允许我的数据库以多种方式表示相同的数据让我有点担心。这不合理吗？工会和这样的组织是否对绩效产生了明显的影响？

（针对相对较小的站点，该系统不太可能通过数万个节点，典型节点最多有6条边）。

Answer 1

实际上，在您的情况下，最好将每个连接视为一个连接（这样您就可以在需要时进行阻塞！）。如果房间之间有多个连接怎么办？就像一个有两扇独立门的房间在同一个大厅里？

不管怎样，这些都是很好玩的地方。。。

就我个人而言，我会根据方向存储每个连接，所以如果a和b之间有一个双向门，我会这样存储

tbl_connections
current_room_id edge      connecting_room_id
1               north     2
2               south     1
1               east      3
1               west      4
4               east      1
1               south     5

是的，如果你有可用的数据，我会包括连接的边缘——当他们决定包括它时，这可能会有所帮助。。。。（在上面的结构中，可以从1到3，但不能从3到1）。

存储，这样你就可以简单地获得房间邻居（和方向）使用：

SELECT connecting_room, edge
FROM tbl_connections
WHERE current_room_id = 1;

这应该会给你：

2, north
3, east
4, west
5, south

您还可以根据您所在的房间以及使用此模式要经过的边缘来映射路径。

存储反射性数据是一种非常常见的解决方案，在这种情况下，我认为这是正确的做法。