多对多的自我参照表

Is there a good way to implement many-to-many relation between rows in single table?

有没有一种很好的方法在单个表中实现行之间的多对多关系?

Example: table to store word synonyms:

示例:用于存储单词同义词的表:

-- list of words
CREATE TABLE word (
    id    integer      PRIMARY KEY,
    word  varchar(32)  NOT NULL UNIQUE
);
INSERT INTO words (id, word) VALUES (1, 'revolve');
INSERT INTO words (id, word) VALUES (2, 'rotate');

-- M:M link between words
CREATE TABLE word_link (
    word1  integer      REFERENCES word(id) NOT NULL,
    word2  integer      REFERENCES word(id) NOT NULL,
    PRIMARY KEY (word1, word2)
);

Obvious solution results in probably not-1NF table, containing duplicate data:

明显的解决方案可能导致非1NF表,包含重复数据:

INSERT INTO word_link(word1, word2) VALUES (1, 2);
INSERT INTO word_link(word1, word2) VALUES (2, 1);

While duplication can be dealt by adding (word1 < word2) check, it makes SELECTs much more complex (union comparing to trivial join) and is pretty arbitrary. This specific case can benefit from auxiliary table (such as 'meaning', so words are M:N linked to common meaning and not to each other, giving cleaner schema), but I'm interested in some general solution.

虽然可以通过添加(word1 )检查来处理重复,但它使select更复杂(联合比较简单的连接)并且非常随意。这个特定情况可以从辅助表中受益(例如'含义',因此单词是m:n链接到常见含义而不是相互关联,给出更清晰的模式),但我对一些通用解决方案感兴趣。

So is there a better (and hopefully common) way to implement such M:M relation?

那么实现这样的M:M关系是否有更好的(并且希望是常见的)方式?

2 个解决方案

#1

In this case I'd add a CHECK CONSTRAINT on UPDATE and on INSERT to enforce that word1 is always less than word2 and vice-versa.

在这种情况下,我会在UPDATE和INSERT上添加一个CHECK CONSTRAINT来强制word1始终小于word2,反之亦然。

#2

I'd create a view that was the following:

我创建了一个如下视图:

select distinct
    case when word1 < word2 then word1 else word2 end as word1,
    case when word1 < word2 then word2 else word1 end as word2
from
    word_link

That way, you always have a clean, no duplicate list that's easy to select from. I've found that's about as clean of a way as you can have to do a many-to-many relationship.

这样,您总是有一个干净,没有重复的列表,很容易从中选择。我发现这是一种干净的方式,因为你可以做多对多的关系。

#1