sql - son - ¿Cómo creo una tabla/consulta de referencia cruzada para mis datos?

¿cuáles son los métodos para crear una consulta de tabla de referencias cruzadas?

Question

Tengo dos tablas simples en mi base de datos. Una tabla de "tarjetas" que contiene Id, Nombre y texto de una tarjeta, y una tabla de "reglas" que contiene el Id de la tarjeta, y un texto que detalla las reglas para la tarjeta.

Con bastante frecuencia en el texto de referencia, hay una referencia a otra tarjeta en la base de datos. Es bastante fácil encontrar esto en el texto porque cada tarjeta está encapsulada entre comillas en el texto. No es raro tener varias tarjetas referenciadas dentro del texto del fallo.

Lo que me gustaría hacer es poder crear una tabla de referencias cruzadas (o un procedimiento si es lo suficientemente eficiente) para que cuando envíe una consulta para una tarjeta, pueda encontrar todos los registros gobernantes que hacen referencia directamente a la tarjeta a través del Id y obtenga todos los registros gobernantes donde se hace referencia al nombre de la tarjeta en el texto.

¿Cuál sería la mejor manera de abordar esto? Mi entorno es SQL 2005, pero cualquier tipo de solución "DB agnóstico" es ampliamente aceptada aquí.

Answer 1

Esto parece ser un problema relacional bastante simple y común que se resuelve con una tabla de referencias cruzadas. Por ejemplo:

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

Para obtener todas las resoluciones de tarjeta para una carta:

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

Para obtener todas las cartas a las que se hace referencia en un fallo de una carta determinada:

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

Todo esto estaba fuera de mi cabeza y no fue probado, por lo que podría haber errores sintácticos, etc.

Su parte delantera sería responsable de mantener las referencias. Esto es probablemente deseable ya que evita el problema de que alguien se olvide de poner comillas alrededor de un nombre de tarjeta en un texto de referencia, etc.

Answer 2

Le recomendaría que cree otra tabla que almacene sus referencias. Luego, crea un disparador de inserción y actualización que mantenga esta tabla. De esta manera, tendrías una consulta más rápida para devolver los datos que estás buscando.

Reconozco que poblar inicialmente esta tabla puede ser un poco difícil, por lo que estoy mostrando algunos datos de muestra (y consulta) a continuación, que puede usar para comenzar.

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) + ''"%''

EDITAR:

La razón por la que sugerí otra tabla es porque probablemente se decepcionará con el rendimiento de esta consulta, especialmente para tablas grandes.