Johnny Bee.: Normalisierung und Abfrage (MySQL)

Moin moin,

ich knobele gerade an der optimalen Abbildung folgenden Sachverhaltes und würde mich über Hilfestellung sehr freuen:

Meine Datenbank erfasst in einer Tabelle 'tour' gefahrene Touren mit Angaben wie Absender, Empfänger und Preis.
1. Tabelle 'tour' mit Spalten
   'id', 'datum', 'absender', 'empfaenger', 'preis'

Nun soll die Möglichkeit hinzukommen, einer Tour eine oder mehrere Besonderheiten zuzuordnen. Eine Besonderheit ist z.B. die Benutzung der Hebebühne oder des Hubwagens.
2. Tabelle 'besonderheit' mit Spalten
   'id', 'name'
   Datensätze z.B. 'hebe' und 'hub'

Ich habe dort eine m-zu-m-Beziehung, da eine Tour mehrere Besonderheiten haben kann und eine Besonderheit bei mehreren Touren vorkommen kann. Der Normalisierung folgend behebe ich diese Beziehung durch das Erstellen einer Verbindungsentität 'tourbesonderheit':
3. Tabelle 'tourbesonderheit' mit Spalten
   'id', 'tour_id', 'besonderheit_id'

Dies scheint mir aus Sicht der Datenstruktur die optimale Lösung. Nur leider blicke ich nicht, wie ich die Abfrage dazu passend gestalten kann. Das Ergebnis der Abfrage soll z.B. die Liste aller Touren eines Tages mit den Eigenschaften sein, also vereinfacht etwa so:

Datum | absender | empfaenger | preis | hebe | hub
--------------------------------------------------------
11.01 | hein     | klaas      | 15    |      |  x
11.01 | pit      | jan        | 25    |  x   |
11.01 | mary     | ann        | 52    |  x   |  x

Die Schwierigkeit hierbei ist für mich, daß in den Spaltenüberschriften alle Besonderheiten auftauchen sollen. Es gibt keine feste Anzahl an Besonderheiten. Es können neue dazukommen. Also müßten die Zeilen der Tabelle 'besonderheit' ausgelesen werden und als Spaltenüberschrift des Ergebnisses der Abfrage verwendet werden. Ob sowas überhaupt mit einer mySQL-Abfrage möglich bzw. sinnvoll ist, weiß ich nicht. Selbst die vergleichbar einfache Abfrage: gib mir alle Touren vom 11.01 mit der Besonderheit 'hub' blicke ich nicht, da ich ja über die Tabelle 'tour' weiter über 'tourbesonderheit' zur Tabelle 'besonderheit' kommen muß. Ach, haariges Zeug, dieses SQL.

Ich habe daraufhin gedanklich wieder zurückgerudert und dachte, ich füge der Tabelle 'tour' einfach stumpf ein Feld 'besonderheit' an. In dieses Feld schreibe ich dann die Namen der Besonderheit hintereinander weg und kann diese dann mit %LIKE% abfragen. Aber das ist irgendwie auch bääh. Also so würde das dann aussehen:

1. Tabelle 'tour' mit Spalten
   'id', 'datum', 'absender', 'empfaenger', 'preis', 'besonderheit'
2. Tabelle 'besonderheit' mit Spalten
   'id', 'name'

Abfrageergebnis:

Datum | absender | empfaenger | preis | besonderheit
--------------------------------------------------------
11.01 | hein     | klaas      | 15    | hub
11.01 | pit      | jan        | 25    | hebe
11.01 | mary     | ann        | 52    | hebe hub

Diese Version würde mir das Leben erstmal einfacher machen, wobei ich es unsauber finde und mir auch vorstellen kann, daß mich dieser Vorteil irgendwann später wieder einholt und zu einem großen Nachteil mit Anomalien wird.

Mir bliebe die Möglichkeit, das Abfrageergebnis mittels mehrerer einzelner mySQL-Querys und Perl zusammenzustückeln. Nur dabei lerne ich nichts über SQL, und daran hapert es bei mir. Ich könnte auch mit jeder neuen Besonderheit die Tabelle 'tour' um eine neue Spalte erweitern, aber das ist superbääh, weil damit sehr viele leere Spalten entstehen.

Ich würde mich sehr über Anregungen freuen
JOhnnY

  1. Fuer die 2. Variante wuerde ich mal das Stichwort: GROUP_CONCAT ins spiel bringen wollen. Die 1. Variante erreichst du eventuell durch dynamischen Zusammenbau eines passenden SELECT Statements oder durch Verarbeitung der einzelnen Resultate mithilfe von (assoziativen) Arrays in Perl.

    Cheers, Frank

    1. Hallo,

      Fuer die 2. Variante wuerde ich mal das Stichwort: GROUP_CONCAT ins spiel bringen wollen. Die 1. Variante erreichst du eventuell durch dynamischen Zusammenbau eines passenden SELECT Statements

      wozu Du Dir folgende Archivbeiträge ansehen kannst:

      </archiv/2006/3/t125385/#m808450>
      </archiv/2008/6/t173119/#m1136008>
      </archiv/2009/3/t184124/#m1220523>

      Bei den von Dir geposteten Beispieldaten kämst Du mit

      SELECT  
          t.datum,  
          t.absender,  
          t.empfaenger,  
          t.preis,  
          -- je eine der folgenden Zeilen für jeden Eintrag in der Tabelle besonderheiten:  
          MAX(CASE WHEN b.id = 1 THEN 'x' ELSE '' END) AS hebe,  
          MAX(CASE WHEN b.id = 2 THEN 'x' ELSE '' END) AS hub  
      FROM  
          tour t  
      LEFT OUTER JOIN  
          tourbesonderheit tb  
      ON  
          t.id = tb.tour_id  
      LEFT OUTER JOIN  
          besonderheit b  
      ON  
          tb.besonderheit_id = b.id  
      GROUP BY  
          t.datum,  
          t.absender,  
          t.empfaenger,  
          t.preis  
      ORDER BY  
          t.id  
      
      

      zu dem von Dir angegebenen Ergebnis. Ausführliche Erläuterungen findest Du in den verlinkten Threads.

      oder durch Verarbeitung der einzelnen Resultate mithilfe von (assoziativen) Arrays in Perl.

      Freundliche Grüße

      Vinzenz

      1. Bei den von Dir geposteten Beispieldaten kämst Du mit

        ...

        zu dem von Dir angegebenen Ergebnis. Ausführliche Erläuterungen findest Du in den verlinkten Threads.

        Freundliche Grüße

        Vinzenz

        Wahnsinn! Du bist wirklich ein SQL-Genie. ;-)
        Ich bin tief beeindruckt und verneige mein Haupt in Ehrfurcht. Mein erster Test hat vielversprechend geklappt. Nun werde ich morgen mal sehen, inwieweit ich das Perl-Modul Data::Table damit füttern kann. Und verstehen, wie genau die Abfrage funktioniert. Die Links sind sehr hilfreich. Ich glaube so ganz langsam komme ich dahinter, wie der SQL-Hase läuft.

        Nochmal ein DICKES DANKESCHÖN!
        Lieben Gruß
        JOhnnY

      2. Yeah, ich hab's endlich verstanden!

        Seit langem knabbere ich an den JOINs herum und immer wieder hab ich nicht geblickt, wie man da denken muß - und dabei ist es eigentlich gar nicht kompliziert.

        Juhu, die Energie des Verstehens... :-)

      3. Hallo Vinzenz,

        das hat mich ja richtig nach vorne gebracht. Endlich hab ich die Joins kapiert - und dabei ist das eigentlich gar nicht so kompliziert. Ich bin auch dem Hinweis von Frank nachgegangen und habe mir GROUP_CONCAT angeschaut. Damit habe ich genau das erreicht, was ich haben wollte.

        Nun gibt es noch eine Feinheit, die mich zwar nicht sehr stört, aber die ich trotzdem gerne verstehen/beheben würde. Mein Query ist folgender:

          
        SELECT  
            t.id,  
            t.datum,  
            t.referenz,  
            t.preis,  
            tb.id,  
            GROUP_CONCAT( distinct(b.beschreibung) ) Besonderheiten,  
        FROM  
            tour t  
        LEFT OUTER JOIN  
            tour_besonderheit tb  
        ON  
            t.id = tb.tour_id  
        LEFT OUTER JOIN  
            besonderheit b  
        ON  
            b.id = tb.besonderheit_id  
        WHERE t.firma_id = 4  
        GROUP BY  
            t.id  
        ORDER BY  
            t.id  
        
        

        Wenn ich nun alle Touren mit einer bestimmten Besonderheit anzeigen lassen möchte, erweitere ich die WHERE-Bedingung um ein

        AND b.id = 4

        Das klappt auch, wobei GROUP_CONCAT dann nur die ausgewählte Besonderheit aufzählt. Schöner wäre, wenn in der Auflistung trotzdem alle Besonderheiten aufgezählt wären. Hast Du da 'nen Tipp für mich?

        Besten Dank
        JOhnnY

        1. Nun gibt es noch eine Feinheit, die mich zwar nicht sehr stört, aber die ich trotzdem gerne verstehen/beheben würde. Mein Query ist folgender:

          es ist doch nicht nur eine Feinheit...

          Wenn ich nach zwei Besonderheiten gleichzeitig filtere, findet er nichts, auch wenn es Touren gibt, die dem entsprechen...

        2. Hallo,

          Nun gibt es noch eine Feinheit, die mich zwar nicht sehr stört, aber die ich trotzdem gerne verstehen/beheben würde. Mein Query ist folgender:

          das Statement wird nur von MySQL akzeptiert, da Du nicht nach allen Spalten gruppierst, auf die Du keine Aggregatsfunktion anwendest.

          SELECT
              t.id,

          -- im Falle der direkt von t.id abhängigen Spalten ist das kein echtes Problem
          -- da die Werte eh' alle die gleichen sind.

          t.datum,
              t.referenz,
              t.preis,

          -- für die folgende Spalte bekommst Du selbstverständlich irgendeinen
          -- beliebigen möglichen Wert. Bei mehrfachem Ausführen der gleichen Abfrage
          -- kann dieser durchaus unterschiedlich sein

          tb.id,
              GROUP_CONCAT( distinct(b.beschreibung) ) Besonderheiten,
          FROM
              tour t
          LEFT OUTER JOIN
              tour_besonderheit tb
          ON
              t.id = tb.tour_id
          LEFT OUTER JOIN
              besonderheit b
          ON
              b.id = tb.besonderheit_id
          WHERE t.firma_id = 4
          GROUP BY
              t.id
          ORDER BY
              t.id

          
          >   
          > Wenn ich nun alle Touren mit einer bestimmten Besonderheit anzeigen lassen möchte, erweitere ich die WHERE-Bedingung um ein  
            
          
          > `AND b.id = 4`{:.language-sql}  
            
          Nein. Wie Du gemerkt hast, liefert dies nicht das gewünschte Ergebnis. Außerdem gibt's noch ein paar andere Kleinigkeiten :-)  
            
          Fangen wir an: Du suchst alle Touren mit einer bestimmten Besonderheit. Daraus folgt, dass Dich Touren \*ohne\* Besonderheit \*nicht\* interessieren. Du weißt, dass die Dich interessierenden Touren mindestens eine Entsprechung in der Tabelle tour\_besonderheit haben. Alle Datensätze der Tabelle tour\_besonderheit haben eine Entsprechung in der Tabelle besonderheiten (das solltest Du über referentielle Integrität sicherstellen :-)  
            
          Statt OUTER JOINS kannst Du somit zwei INNER JOINS verwenden, was Die Sache nur schneller machen kann :-)  
            
          Wenn es nun nur um eine Besonderheit geht und außerdem gilt, dass die gleiche Besonderheit in der gleichen Tour maximal einmal vorkommen kann (UNIQUE-Index über die Spaltenkombination (tb.tour\_id, tb\_besonderheit\_id), dann kannst Du das Problem mit einem Join erledigen. Allerdings wirst Du sehen, dass diese Lösung nicht flexibel ist und sich nicht leicht erweitern läßt.  
            
          ~~~sql
            
          SELECT  
              t.id,  
              t.datum,  
              t.referenz,  
              t.preis,  
              GROUP_CONCAT( distinct(b.beschreibung) ) Besonderheiten,  
          FROM  
              tour t  
          -- wir joinen ein zweites Mal mit den tour_besonderheiten,  
          -- um nur die Touren zu erhalten, die die Besonderheit mit der  
          -- id 4 enthalten.  
          INNER JOIN  
              -- wir müssen daher einen anderen Aliasnamen verwenden,  
              -- als im Join für die Detais  
              tour_besonderheit tb2  
          ON  
              t.id = tb2.tour_id  
          AND  
              -- und packen auch die Einschränkung in die Joinbedingung  
              tb2.besonderheit_id = 4  
            
          -- und schauen uns von diesen Touren  
          -- alle passenden Einträge an  
          INNER JOIN  
              tour_besonderheit tb  
          ON  
              t.id = tb.tour_id  
          INNER JOIN  
              besonderheit b  
          ON  
              b.id = tb.besonderheit_id  
          WHERE  
              t.firma_id = 4  
          GROUP BY  
              t.id  
              t.datum,  
              t.referenz,  
              t.preis  
          -- in MySQL ist diese ORDER-BY-Klausel überflüssig, da MySQL ohnehin  
          -- in der Reihenfolge der Spalten der GROUP-BY-Klausel sortiert  
          ORDER BY  
              t.id  
          
          

          Dein Problem, wenn zwei verschiedene Besonderheiten erfüllt sein sollen, könntest Du nun mit einem weiteren INNER JOIN auf die zweite gewünschte Besonderheit (und einem dritten Alias) erweitern.

          Vom Grundsatz her ist es das gleiche Problem, das asmodin in diesem Archivthread hatte. Schau Dir meine dortigen Vorschläge an und wende sie auf Dein Problem an. Vergiss nicht das Messen mit einer signifikanten Zahl von Datensätzen :-)

          Freundliche Grüße

          Vinzenz

          1. Hi Vinzenz,

            vielen Dank für Deine ausführliche Antwort. Hab ich verstanden! Funktioniert! Genial...

            So langsam wird das was mit mir und meinem mySQL-Verständnis. Danke Dir!
            JOhnnY