Abfrage innerhalb einer Tabelle

[Dirk]

Liebe Forum-Mitglieder,
ich bin gerade erst in MYSQL eingestiegen  und habe keine Probleme mit verknüpften Tabellen. Für eine Website suche ich jetzt zu bestimmten Artnamen von Pflanzen und Tieren, die entsprechenden Angaben zu Gattung, Familie, Ordnung etc.. Es gibt ein taxonomisches Datenbank-System, dass die Infos zum download unter www.itis.usda.gov/ zur Verfügung stellt. Leider ist mir dort aufgefallen, dass die Einträge nicht über verschiedende Tabellen verknüpft sind sondern alle in einer einzigen Tabelle enthalten sind. Ein Beispiel könnte wie folgt aussehen: A = Art, G = Gattung, UF = Unterfamilie, F = Familie, UO = Unterordnung, O = Ordnung, K = Klasse.
Dabei fällt auf, dass die Verknüpfung von Art bis hin zur Klasse unterschiedlich lang sein können, d.h. bei einigen Gruppen fehlen die Angaben zu UF und UO, so dass man einmal 5 ein anders mal 6 oder eben 7 Infos abfragen muss.

Nun aber zur Tabelle:

id Name Verknüpfung Bezeichnung
1 A1 2 Art
2 G1 4 Gattung
3 A2 2 Art
4 F1 5 Familie
5 O1 6 Ordnung
6 K1  Klasse
7 A3 8 Art
8 G2 9 Gattung
9 UF1 10 Unterfamilie
10 F2 6 Familie

Als Ergebnis möchte ich gerne folgendes angezeigt haben:

Art A1
Gattung G1
Familie F1
Ordnung O1
Klasse K1

bzw.:
Art A3
Gattung G2
Unterfamilie UF1
Familie F2
Ordnung O1
Klasse K1

Hat jemand von Euch eine Idee wie ich die Abfrage gestalten und mit php generieren könnte. Eine Zerlegung der Tabelle in mehrere möchte ich unter allen Umständen vermeiden.
Im Vorhinein schon einmal ganz herzlichen Dank
Dirk

[Ilja]

yo,

Hat jemand von Euch eine Idee wie ich die Abfrage gestalten und mit php generieren könnte.

hirachische strukturen lassen sich immer schwer mit rdbms abbilden. mysql bietet dazu glaube ich einige funktionalitäten an, wobei ich nicht weiß, wie und ab welcher version. muss du am besten selbst mal bei der doku im netzt nachschauen.

da du allerdings die maximale anzahl "tiefe" kennst, könntest du es auch mit SELFJOINS lösen, genauer gesagt mit OUTER SELFJOINS, da ja nicht immer jede ebene besetzt ist. bei einer maximalen tiefe von 7 wären es dann 7 SELFJOINS.

Eine Zerlegung der Tabelle in mehrere möchte ich unter allen Umständen vermeiden.

was würde den dagegen sprechen, das design zu normalisieren ?

Ilja

[Dirk Schories]

Hallo zusammen,
erst einmal ganz herzlichen Dank für eure Tips und Anmerkungen. Sorry, dass ich mich nicht gleich bei Euch gemeldet habe. Ich dachte ich würde eine email als Benachrichtigung bekommen, falls eine Antwort auf meine Anfrage eintrifft. Ich möchte vor allem die Zerlegung der ITIS-Datenbank vermeiden, da sie über eine Million Einträge enthält und ich die Daten hin und wieder aktualisieren möchte, d.h. dann wieder auf die Original-Datenbank zugreifen muss. Meine Idee war es Teilabfragen aus ITIS zu generieren, so dass ich beispielsweise einen kleineren Datensatz habe, da beispielsweise nur die Klasse der Krebse (Crustacea) berücksichtigt wird, und ich dann die outer selfjoins generieren könnte. Ich werde es auf jeden Fall ausprobieren und mir die vorherigen Hinweise durchlesen.
Lieben gruß
Dirk

yo,

Hat jemand von Euch eine Idee wie ich die Abfrage gestalten und mit php generieren könnte.

hirachische strukturen lassen sich immer schwer mit rdbms abbilden. mysql bietet dazu glaube ich einige funktionalitäten an, wobei ich nicht weiß, wie und ab welcher version. muss du am besten selbst mal bei der doku im netzt nachschauen.

da du allerdings die maximale anzahl "tiefe" kennst, könntest du es auch mit SELFJOINS lösen, genauer gesagt mit OUTER SELFJOINS, da ja nicht immer jede ebene besetzt ist. bei einer maximalen tiefe von 7 wären es dann 7 SELFJOINS.

Eine Zerlegung der Tabelle in mehrere möchte ich unter allen Umständen vermeiden.

was würde den dagegen sprechen, das design zu normalisieren ?

Ilja

[Vinzenz Mai]

Hallo Dirk,

ich bin gerade erst in MYSQL eingestiegen  und habe keine Probleme mit verknüpften Tabellen. Für eine Website suche ich jetzt zu bestimmten Artnamen von Pflanzen und Tieren, die entsprechenden Angaben zu Gattung, Familie, Ordnung etc.. Es gibt ein taxonomisches Datenbank-System, dass die Infos zum download unter www.itis.usda.gov/ zur Verfügung stellt.

diese möchtest Du offensichtlich nur importieren und direkt verwenden

Leider ist mir dort aufgefallen, dass die Einträge nicht über verschiedende Tabellen verknüpft sind sondern alle in einer einzigen Tabelle enthalten sind.

Solche hierarchischen Beziehungen trifft man öfter. Für relationale Datenbankmanagementsysteme wie MySQL ist dies eine ungünstige Struktur.

Ein Beispiel könnte wie folgt aussehen: A = Art, G = Gattung, UF = Unterfamilie, F = Familie, UO = Unterordnung, O = Ordnung, K = Klasse.
Dabei fällt auf, dass die Verknüpfung von Art bis hin zur Klasse unterschiedlich lang sein können, d.h. bei einigen Gruppen fehlen die Angaben zu UF und UO, so dass man einmal 5 ein anders mal 6 oder eben 7 Infos abfragen muss.

Nun aber zur Tabelle:

id Name Verknüpfung Bezeichnung
1 A1 2 Art
2 G1 4 Gattung
3 A2 2 Art
4 F1 5 Familie
5 O1 6 Ordnung
6 K1  Klasse
7 A3 8 Art
8 G2 9 Gattung
9 UF1 10 Unterfamilie
10 F2 6 Familie

Als Ergebnis möchte ich gerne folgendes angezeigt haben:

Art A1
Gattung G1
Familie F1
Ordnung O1
Klasse K1

bzw.:
Art A3
Gattung G2
Unterfamilie UF1
Familie F2
Ordnung O1
Klasse K1

Hat jemand von Euch eine Idee wie ich die Abfrage gestalten und mit php generieren könnte.

_Die_ Abfrage gibt es nicht. Du musst die Tabelle rekursiv von der Art bis zur Klasse durchlaufen, d.h. eine Serie von Abfragen absetzen. Jetzt kommt meine Lieblingsfrage:

Welche Version von MySQL verwendest Du? Auch wenn das sukzessive Absetzen von Queries an die Datenbank nicht gerade effizient ist, so könntest Du, MySQL 5 und neuer vorausgesetzt, Dein Problem mit einer Stored Procedure lösen.

Eine Zerlegung der Tabelle in mehrere möchte ich unter allen Umständen vermeiden.

Warum? Für Deine Problemstellung bieten sich "Nested Sets" an. Ich vermute, dass sich die Änderungen im Vergleich zum Datenbestand in Grenzen halten.

Hier ein paar Archivpostings und -threads zu diesem Thema:

</archiv/2006/2/t123458/#m794964>
</archiv/2006/3/t125135/>
</archiv/2006/3/t125579/>
</archiv/2006/3/t126324/>

Freundliche Grüße

Vinzenz

[Ilja]

yo,

_Die_ Abfrage gibt es nicht. Du musst die Tabelle rekursiv von der Art bis zur Klasse durchlaufen, d.h. eine Serie von Abfragen absetzen.

wenn die maximale tiefe bekannt ist, sollte das gehen.

Ilja

[Vinzenz Mai]

Hallo Ilja,

_Die_ Abfrage gibt es nicht. Du musst die Tabelle rekursiv von der Art bis zur Klasse durchlaufen, d.h. eine Serie von Abfragen absetzen.

wenn die maximale tiefe bekannt ist, sollte das gehen.

prinzipiell gesehen hast Du mit Deiner Selfjoin-Lösung ja recht. Wenn Du die von mir verlinkten Threads durchliest, wirst Du feststellen, dass ich gelegentlich den Selfjoin als Alternative vorschlage :-)

Über den Ergebnisdatensatz muss erneut eine Anwendungslogik laufen, die aus dem Datensatz die gewünschten Spaltenwerte extrahiert. Ich wäre damit nicht glücklich.

Freundliche Grüße

Vinzenz

[Ilja]

yo,

ich gebe zu, ich war ein wenig faul und bin den links nicht gefolgt. auf der anderen seite ist es ein unterschied zu sagen, diese abfrage gibt es nicht oder es ist nicht empfehlenswert. erst recht, wenn du die lösung mit den SELJOINS schont oft selbst vorgeschlagen hast....

Ilja