sql - 如何为我的数据创建交叉引用表/查询？

Question

我的数据库中有两个简单的表。一个“卡片”表，其中包含卡片的 ID、名称和文本，以及一个“裁决”表，其中包含卡片的 ID 以及详细说明该卡片的裁决的文本。

在裁决文本中，经常会引用数据库中的另一张牌。在文本中很容易找到这一点，因为每张卡片都被封装在文本中的引号内。裁决文本中引用多张牌的情况并不罕见。

我想要做的是能够创建一个交叉引用表(或者过程，如果它足够高效的话)，这样当我提交对卡片的查询时，我可以找到直接引用该卡片的所有裁决记录通过Id获取card，获取文本中引用该卡名的所有裁决记录。

解决这个问题的最佳方法是什么？我的环境是 SQL 2005，但这里非常接受任何类型的“与数据库无关”的解决方案。

Answer 1

这似乎是一个相当简单且常见的关系问题，可以通过交叉引用表来解决。例如:

CREATE TABLE dbo.Cards (
    id        INT            NOT NULL,
    name      VARCHAR(50)    NOT NULL,
    card_text VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Cards PRIMARY KEY CLUSTERED (id)
)
GO
CREATE TABLE dbo.Card_Rulings (
    card_id        INT            NOT NULL,
    ruling_number  INT            NOT NULL,
    ruling_text    VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Card_Rulings PRIMARY KEY CLUSTERED (card_id, ruling_number)
)
GO
CREATE TABLE dbo.Card_Ruling_Referenced_Cards (
    parent_card_id    INT    NOT NULL,
    ruling_number     INT    NOT NULL,
    child_card_id     INT    NOT NULL,
    CONSTRAINT PK_Card_Ruling_Referenced_Cards PRIMARY KEY CLUSTERED (parent_card_id, ruling_number, child_card_id)
)
GO
ALTER TABLE dbo.Card_Rulings
ADD CONSTRAINT FK_CardRulings_Cards FOREIGN KEY (card_id) REFERENCES dbo.Cards(id)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_CardRulings FOREIGN KEY (parent_card_id, ruling_number) REFERENCES dbo.Card_Rulings (card_id, ruling_number)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_Cards FOREIGN KEY (child_card_id) REFERENCES dbo.Cards(id)
GO

要获取一张牌的所有牌裁决:

SELECT *
FROM dbo.Cards C
INNER JOIN dbo.Card_Rulings CR ON CR.card_id = C.id
WHERE C.id = @card_id

要获取给定卡牌在裁决中引用的所有卡牌:

SELECT C.*
FROM dbo.Card_Rulings CR
INNER JOIN dbo.Card_Ruling_Referenced_Cards CRRC ON CRRC.parent_card_id = CR.card_id
INNER JOIN dbo.Cards C ON C.id = CRRC.child_card_id
WHERE CR.card_id = @card_id

这都是我的想法，未经测试，因此可能存在语法错误等。

您的前端将负责维护引用。这可能是可取的，因为它避免了有人忘记在裁决文本中的卡名周围加引号等问题。