Alternativ-Artikel in SQL abbilden

Question

Hallo zusammen,

ich brauch gerade mal einen Stups in die richtige Richtung.

Ich habe Artikel zu denen ich Alternativ-Artikel speichern will. Soweit kein Problem => Neue Tabelle mit 2 Spalten Artikel und Alterntivartikel.

Jetzt ist es aber doch so, dass der Artikel für den Alternativartikel doch logischerweise auch die Alternative darstellt. Nun will ich ja nicht alls doppelt speichern, alsoes soll nicht so aussehen:

Spalte Artikel   |   Spalte Alternative
AAA                |     BBB
AAA                |     CCC
BBB                |     AAA
CCC                |     AAA

Außerdem sollte  BBB auch als Alternative für CCC gefunden werden.

Wie setzt man so etwas vernünftig um?

Schon mal danke

Volker

---

Und Abends ein Glas Wein von AMAVINO

Answer 1

Wenn es hier um bidirektionale Abbildung geht, dann heisst es lediglich Permutationen zu vermeiden. Z.B.

USE tempdb;

CREATE TABLE AlternativeArtikel
    (
      ArtikelID CHAR(3) NOT NULL ,
      AlternativeArtikelID CHAR(3) NOT NULL ,
      Magic AS CASE WHEN ArtikelID < AlternativeArtikelID THEN ArtikelID + AlternativeArtikelID
                    ELSE AlternativeArtikelID + ArtikelID
               END PERSISTED ,
      CONSTRAINT UQ_Magic UNIQUE ( Magic )
    );

INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'BBB' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'CCC' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'BBB', 'AAA' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'CCC', 'AAA' );

SELECT  *
FROM    AlternativeArtikel;

DROP TABLE AlternativeArtikel;

Das Finden der Alternativen geht dann im Grunde dadurch das du hieraus einen Graphen aufbaust und ausgehend alle Knoten sammelst welche zum gesuchten Knoten mittels Pfad verbunden sind.

Allerdings bleibt das Problem das du mit dieser Art der Anlage Zyklen erzeugen kannst. Z.B. (AAA,BBB), (BBB,CCC), (CCC,AAA). Hier braucht es schon das du einen Graphen aufbaust, und schaust ob er diesen Pfad schon enthält.

Die vereinfachte Version:

USE tempdb;

CREATE TABLE AlternativeArtikel
    (
      ArtikelID CHAR(3) NOT NULL ,
      AlternativeArtikelID CHAR(3) NOT NULL ,
      Magic AS CASE WHEN ArtikelID < AlternativeArtikelID THEN ArtikelID + AlternativeArtikelID
                    ELSE AlternativeArtikelID + ArtikelID
               END PERSISTED ,
      CONSTRAINT UQ_Magic UNIQUE ( Magic )
    );

INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'BBB' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'FFF' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'BBB', 'CCC' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'CCC', 'DDD' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'CCC', 'EEE' );

SELECT  *
FROM    AlternativeArtikel;

DECLARE @AnzahlErgebnisse INT = 0;
DECLARE @SucheAlternativeFuerArtikelID CHAR(3) = 'BBB';
DECLARE @ErgbnisAlternativen TABLE ( ArtikelID CHAR(3) );

INSERT  INTO @ErgbnisAlternativen
        SELECT  ArtikelID
        FROM    AlternativeArtikel
        WHERE   AlternativeArtikelID = @SucheAlternativeFuerArtikelID;
INSERT  INTO @ErgbnisAlternativen
        SELECT  AlternativeArtikelID
        FROM    AlternativeArtikel
        WHERE   ArtikelID = @SucheAlternativeFuerArtikelID;

PRINT '<';  
PRINT @AnzahlErgebnisse
WHILE @AnzahlErgebnisse < ( SELECT  COUNT(*)
                            FROM    @ErgbnisAlternativen
                          ) 
    BEGIN
        PRINT '.';  

        INSERT  INTO @ErgbnisAlternativen
                SELECT  AlternativeArtikelID
                FROM    AlternativeArtikel A
                        INNER JOIN @ErgbnisAlternativen E ON E.ArtikelID = A.ArtikelID
                WHERE AlternativeArtikelID <> @SucheAlternativeFuerArtikelID;

        SELECT  @AnzahlErgebnisse = COUNT(*)
        FROM    @ErgbnisAlternativen;					           
    END;

PRINT '>';

SELECT  *
FROM    @ErgbnisAlternativen;

DROP TABLE AlternativeArtikel;

Answer 2

USE tempdb;

CREATE TABLE AlternativeArtikel
    (
      ArtikelID CHAR(3) NOT NULL ,
      AlternativeArtikelID CHAR(3) NOT NULL ,
      Magic AS CASE WHEN ArtikelID < AlternativeArtikelID THEN ArtikelID + AlternativeArtikelID
                    ELSE AlternativeArtikelID + ArtikelID
               END PERSISTED ,
      CONSTRAINT UQ_Magic UNIQUE ( Magic )
    );

INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'BBB' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'AAA', 'FFF' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'BBB', 'CCC' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'CCC', 'DDD' );
INSERT  INTO AlternativeArtikel
VALUES  ( 'CCC', 'EEE' );
// INSERT  INTO AlternativeArtikel  <====
// VALUES  ( 'EEE', 'CCC' );        <====

SELECT  *
FROM    AlternativeArtikel;

DECLARE @AnzahlErgebnisse INT = 0;
DECLARE @SucheAlternativeFuerArtikelID CHAR(3) = 'BBB';
DECLARE @ErgbnisAlternativen TABLE ( ArtikelID CHAR(3) );

INSERT  INTO @ErgbnisAlternativen
        SELECT  ArtikelID
        FROM    AlternativeArtikel
        WHERE   AlternativeArtikelID = @SucheAlternativeFuerArtikelID;
INSERT  INTO @ErgbnisAlternativen
        SELECT  AlternativeArtikelID
        FROM    AlternativeArtikel
        WHERE   ArtikelID = @SucheAlternativeFuerArtikelID;

PRINT '<';  
PRINT @AnzahlErgebnisse
WHILE @AnzahlErgebnisse < ( SELECT  COUNT(*)
                            FROM    @ErgbnisAlternativen
                          ) 
    BEGIN
        PRINT '.';  

        INSERT  INTO @ErgbnisAlternativen
                SELECT  AlternativeArtikelID
                FROM    AlternativeArtikel A
                        INNER JOIN @ErgbnisAlternativen E ON E.ArtikelID = A.ArtikelID
                WHERE AlternativeArtikelID <> @SucheAlternativeFuerArtikelID;

        SELECT  @AnzahlErgebnisse = COUNT(*)
        FROM    @ErgbnisAlternativen;					           
    END;

PRINT '>';

SELECT  *
FROM    @ErgbnisAlternativen;

DROP TABLE AlternativeArtikel;

Super! Der Ansatz ist gut, hat aber noch einen kleinen Fehler, aber das bekomme ich dann schon hin. Eine Änderung bei <=== ergibt unterschiedliche Ergebnisse.

Volker

---

Und Abends ein Glas Wein von AMAVINO

Answer 3

Hallo Volker,
eine etwas komplexere Lösung für den Graphen habe ich mal hier beschrieben:
http://www.insidesql.org/blogs/cmu/sql_server/routenfindung-in-graphen
Routenfindung in Graphen  

Falls man in dem Skript dort deine Daten anstelle von meinen Wegen einfügt, erhält man alle möglichen Wege (Alternativartikel):

insert into t_Connection values('A','B');
insert into t_Connection values('A','C');
insert into t_Connection values('B','A');
insert into t_Connection values('C','A');

HTH!

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu