Как создать таблицу или запрос перекрестных ссылок для моих данных?

У меня есть две простые таблицы в моей базе данных. Таблица «карта», содержащая идентификатор, имя и текст карты, а также таблицу «постановлений», в которой содержится идентификатор карточки, и текст с подробным описанием решений для карты.

Часто в правильном тексте есть ссылка на другую карту в базе данных. Достаточно легко найти это в тексте, потому что каждая карта заключена в кавычки в тексте. Нередко есть несколько ссылок, упомянутых в тексте постановления.

То, что я хотел бы сделать, – создать таблицу перекрестных ссылок (или процедуру, если она достаточно эффективна), чтобы при отправке запроса на карточку я мог найти все правящие записи, которые непосредственно ссылаются на карту через Идентификатор и получить все правящие записи, на которые указано имя карты в тексте.

Какой был бы лучший способ приблизиться к этому? Моей средой является SQL 2005, но здесь широко приняты любые «агностические» решения DB.

    Это похоже на довольно простую и общую реляционную проблему, решаемую таблицей перекрестных ссылок. Например:

    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 

    Все это было от верхней части головы и не проверено, поэтому могут быть синтаксические ошибки и т. Д.

    Ваш внешний интерфейс будет отвечать за поддержание ссылок. Это, вероятно, желательно, поскольку это позволяет избежать вопроса о том, что кто-то забывает ставить котировки вокруг имени карты в правящем тексте и т. Д.

    Я бы рекомендовал создать другую таблицу, в которой хранятся ваши ссылки. Затем создайте триггер insert и update, который поддерживает эту таблицу. Таким образом, у вас будет более быстрый запрос для возврата данных, которые вы ищете.

    Я признаю, что первоначальное заполнение этой таблицы может быть немного сложным, поэтому я показываю некоторые примерные данные (и запрос) ниже, которые вы можете использовать, чтобы начать работу.

     Declare @Card Table(Id Int, Name VarChar(20), CardText VarChar(8000)) Declare @Ruling Table(CardId Int, CardRuling VarChar(8000)) Insert Into @Card Values(1, 'Card 1', 'This is the card ID = 1') Insert Into @Card Values(2, 'Card 2', 'This is the card id = 2.') Insert Into @Card Values(3, 'Card 3', 'This is the card id = 3.') Insert Into @Ruling Values(1, 'This is the ruling for 1 which references "2"') Insert Into @Ruling Values(2, 'This is the ruling for 2 which references nothing') Insert Into @Ruling Values(3, 'This is the ruling for 3 which references "1" and "2"') Declare @CardId Int Set @CardId = 1 Select * From @Card As Card Inner Join @Ruling As Ruling On Card.Id = Ruling.CardId Left Join @Card As CardReferences On Ruling.CardRuling Like '%"' + Convert(VarChar(10), CardReferences.Id) + '"%' 

    РЕДАКТИРОВАТЬ:

    Причина, по которой я предложил другую таблицу, состоит в том, что вы, вероятно, разочаруетесь в производительности этого запроса, особенно для больших таблиц.