如何为我的数据创建交叉引用表/查询？ [英] How do I create a cross reference table/query for my data?

问题描述

我的数据库中有两个简单的表。包含卡的ID，名称和文本的卡表，以及包含卡的ID的裁定表，以及详细说明卡的裁定的文本。

在规则文本中通常足够，引用数据库中的另一张卡。在文本中找到这一点很容易，因为每个卡都封装在文本中的引号内。在裁决文本中引用多张卡并不罕见。

我想做的是创建交叉引用表（或过程如果它是足够有效的），所以当我提交一张卡的查询时，我可以找到通过Id直接引用卡的所有裁决记录，并获得文本中引用卡名称的所有裁决记录。 / p>

最好的方法是什么？我的环境是SQL 2005，但是任何类型的数据库不可知论解决方案在这里得到很大的接受。

解决方案

通过交叉引用表解决的简单和常见的关系问题。例如：

  CREATE TABLE dbo.Cards（
 id INT NOT NULL，
 name VARCHAR（50 ）NOT NULL，
 card_text VARCHAR（4000）NOT NULL，
 CONSTRAINT PK_CARD 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主键集合（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 ，ruleing_number，child_card_id）
）
 GO 
 ALTER TABLE dbo.Card_Rulings 
 ADD CONSTRAINT FK_CardRulings_Cards FOREIGN KEY（card_id）参考dbo.Cards（id）
 GO 
 ALTER TABLE dbo.Card_Ruling_Referenced_Cards 
 ADD CONSTRAINT FK_CardRulingReferencedCards_CardRulings FOREIGN KEY（parent_card_id，ruling_number）参考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）参考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 
  

这都是我头顶，没有测试，所以可能会出现句法错误等。 p>

您的前端将负责维护引用。这可能是可取的，因为它避免了有人忘记在规则文本等内围绕卡片名称引用的问题。

I have two simple tables in my database. A "card" table that contains Id, Name, and text of a card, and a "rulings" table which contains the Id of the card, and text detailing the rulings for the card.

Often enough in the ruling text, there is a reference to another card in the database. It is easy enough to find this in the text because each card is encapsulated within quotes in the text. It is not uncommon to have multiple cards referenced within the text of the ruling.

What I'd like to do is be able to create a cross reference table (or procedure if it is efficient enough) so that when I submit a query for a card, I can find all the ruling records that directly reference the card through the Id and get all of the ruling records where the card name is referenced in the text.

What would be the best way to approach this? My environment is SQL 2005, but any kind of "DB agnostic" solutions are greatly accepted here.

解决方案

This seems like a fairly simple and common relational problem that is solved by a cross-reference table. For example:

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

To get all card rulings for a card:

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

To get all cards referenced in a ruling by a given card:

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

This was all off the top of my head and is not tested, so there might be syntactic errors, etc.

Your front end would be responsible for maintaining the references. This is probably desirable since it avoids the issue of someone forgetting to put quotes around a card name in a ruling text, etc.

这篇关于如何为我的数据创建交叉引用表/查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！