Home > Artikel > Ausgabe 9/2015 > Rekursive Tabellen

Rekursive Tabellen

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Ihr Kumpel, der Gemeinderat, kommt auf die geniale Idee, für alle Bewohner des Dorfes einen Stammbaum anzulegen, und betreut Sie als Programmierer mit der Aufgabe, diese in einer Datenbank anzulegen, um das anschließend zu Papier bringen zu können. Das kann kein so kompliziertes Unterfangen sein, denken Sie, und machen sich frohgemut an die Arbeit. Das Datenmodell ist der erste Schritt, der zu bewältigen ist...

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1509_RekursiveTabellen.accdb

Eltern, Kinder, Enkel im Datenmodell

Es scheint gar nicht schwer zu sein. Es muss einfach eine Tabelle für die Eltern her, die einen Primärschlüssel aufweist. Dann eine für die Kinder, in der die Datensätze über einen Fremdschlüssel ElterID auf die Datensätze der Eltern verweisen. Und schließlich gibt es noch Enkel, deren Tabelle ähnlich aufgebaut ist, wie die der Kinder. Im Ergebnis erhalten Sie das Beziehungslayout aus Bild 1. Die Tabellen sind über Referenzielle Integrität eindeutig miteinander verknüpft, jeweils Indizes auf das Feld ElterID gelegt, und die eigentlichen Einträge der Daten bestehen aus dem Feld Person für den Namen der Bürgers und dem Feld mw, welches sein Geschlecht wiedergeben soll. Flux die Daten aus der Excel-Grundlage in die Tabellen eingepflegt, und schon lässt sich dank der automatischen Unterdatenblätter von Access – ein Feature, welches hier wirklich einmal sinnvoll zum Einsatz kommt – ein Baum ausklappen, wie in Bild 2. Spätestens hier kommen Ihnen Bedenken wegen der etwaigen Urenkel oder gar noch weiterer Abkömmlinge. Sollten noch weitere Tabellen für diese angelegt werden? Und wie sieht es später mit der Auswertung dieser Struktur aus? Werden die dafür benötigten Abfragen da nicht recht umfangreich und unübersichtlich?

Der erste Enwurf des Datenmodells für die Stammbaumdatenbank mit allen Tabellen und deren Beziehungen

Bild 1: Der erste Enwurf des Datenmodells für die Stammbaumdatenbank mit allen Tabellen und deren Beziehungen

Nach dem Öffnen der Tabelle tblEltern lassen sich die automatisch generierten Unterdatenblätter mit den Kindern und Enkeln ausklappen

Bild 2: Nach dem Öffnen der Tabelle tblEltern lassen sich die automatisch generierten Unterdatenblätter mit den Kindern und Enkeln ausklappen

Es geht natürlich auch anders! Sie kommen tatsächlich mit nur einer einzigen Tabelle aus, in der rekursiv eine Beziehung zwischen den Datensätzen für Eltern und Kinder hergestellt wird

Rekursive Tabellen

Das Grundprinzip ist ganz einfach. Eine rekursive Tabelle muss einen Primärschlüssel aufweisen, der jeden Datensatz eindeutig identifiziert. Und zusätzlich ein weiteres indiziertes Feld, welches als Fremdschlüsselverweis für den Primärschlüssel dient. Dazu kommen dann die eigentlichen Inhalte der Tabelle. In Bild 3 ist das für die Tabelle tblElternKind realisiert. Die ID ist als Primärschlüssel vom Typ Autowert. ParentID vom Typ Long enthält dann jeweils einen Wert, der auf einen anderen Datensatz mit dieser ID verweist. Person und mw sind die eigentlichen Inhalte der Tabelle. Ingbert in Datensatz 7 hat die ParentID 60, was besagt, dass sein Elter weiter unten im Datensatz mit eben dieser ID 60 zu finden ist. Das wäre dann, außerhalb des in Bild 3 sichtbaren Bereichs, Günter. Scrollen Sie zu Günter, so kann es sein, dass dieser ebenfalls eine ParentID enthält, mithin also etwa ein Kind von Klara wäre, und somit Ingbert ein Enkel von Klara. Tatsächlich ist aber das Feld ParentID von Günter leer, was besagt, dass in der Datenbank kein Elter von ihm verzeichnet ist. Günter wäre ein Root-Datensatz der rekursiven Tabelle.

Die Eltern-Kind-Tabelle in Datenblattansicht mit dem Verweisfeld ParentID

Bild 3: Die Eltern-Kind-Tabelle in Datenblattansicht mit dem Verweisfeld ParentID

Mehr gibt es dazu eigentlich nicht zu sagen. Immer dann, wenn Datensätze hierarchisch voneinander abhängen und die Zahl der Kindelemente, also der Unterebenen, nicht bekannt ist, kommt so eine rekursive Struktur infrage.

Sie finden in jeder Access-Datenbank dafür ein Beispiel. Die Systemtabelle MSysObjects enthält rekursive Einträge. Auch sie hat den Primärschlüssel ID und das Verweisfeld ParentID. Öffnen Sie sie einmal in der Beispieldatenbank, was voraussetzt, dass in den Optionen des Navigationsbereichs der Eintrag Systemobjekte anzeigen aktiviert ist. Unter der Name Spalte Name suchen Sie die Tabelle tblElternKind. Im Datensatz finden Sie unter ParentID den Wert 251658241.

Nun navigieren Sie zum Datensatz mit dieser ID. Dort steht unter Name Tables. Das ist der Container für alle Tabellen der Datenbank. Er selbst hat die ParentID 251658240, was allerdings zu keinem gültigen Datensatz in der Tabelle führt...

Ein anderes Beispiel wäre die Systemtabelle MSysAccessStorage, welche alle Objekte der Datenbank enthält, außer den Tabellen und Abfragen. Sie ist noch stärker verschachtelt und hat als Root-Datensatz die ID 1 für den bezeichnenden Eintrag MSysAccessStorage_ROOT.

Sie sparen sich mit dieser rekursiven Tabelle also eine Menge Arbeit für die Stammbaumdatenbank ein. Doch wie sieht es nun mit der Darstellung des Baums aus? Gab es im ersten Entwurf noch voneinander abhängige Tabellen, die als Unterdatenblätter der Elterntabelle eingeblendet werden konnten, so scheint diese Möglichkeit bei nur einer Tabelle nunmehr auszuscheiden.

Anzeige rekursiver Tabellen

Tatsächlich kommt aber nur Access von sich aus nicht auf die Idee, in einer Tabelle als Unterdatenblatt eben die gleiche Tabelle einzutragen. Doch genau dies ist machbar. Nehmen Sie den Entwurf der Tabelle tblElternKind in Bild 4. Dort ist ein Ausschnitt des Eigenschaftenblatts der Tabelle eingeblendet. Als Unterdatenblatt ist die tblElternKind selbst eingetragen und die Verknüpfung über ParentID und ID gewährleistet. Nachdem Sie diese Änderung abgespeichert haben, zeigt das Datenblatt die bekannten Plus-Symbole und klappt zu Eltern tatsächlich die Kinder aus, diese wiederum die Enkel, diese die Urenkel, und so fort. Access erzeugt hier also für jede Unterebene eine neue Instanz der Tabelle und verknüpft diese im Hintergrund mit dem Datensatz der übergeordneten Instanz.

Die Eltern-Kind-Tabelle im Entwurf mit einem Teil des Eigenschaftenblatts

Bild 4: Die Eltern-Kind-Tabelle im Entwurf mit einem Teil des Eigenschaftenblatts

Für einen ersten Überblick mag das praktisch sein. Doch bei genauerem Hinsehen stört dann doch, dass bei allen Kindebenen etwa überflüssigerweise eine Zeile für einen neuen Datensatz angezeigt wird, und überhaupt sind die Spalten ID und ParentID wenig informativ. Hier greifen wir besser zu Abfragen, die die Ansicht aufräumen.

Die Tabelle selbst zeigt in der Datenblattansicht auf erster Ebene alle Datensätze an. Also nicht nur die Eltern, sondern genauso die Enkel und Urenkel. Wir möchten dies so einschränken, dass nur echte Eltern dargestellt werden. Dazu müssen die Daten gefiltert werden, wie in der Abfrage qryElternKind aus Bild 5. Das Kriterium dafür, dass eine Person kein Elter besitzt, ergibt sich aus ParentID. Ist hier nichts eingetragen, so handelt es sich um einen Root-Datensatz. Das Auswahlkriterium lautet folglich ParentID Ist Null. Da das Feld ansonsten für die Ansicht nichts hergibt, wird es zudem ausgeblendet. Weil sie nun aber eben dieses Verknüpfungsfeld nicht mehr im Ergebnis ausgibt, kann die Abfrage als Unterdatenblatt nicht mehr selbst eingetragen werden. Stattdessen benötigen wir eine weitere Abfrage, die sich für rekursive Verschachtelung eignet. Bild 6 gibt sie mit der Abfrage qryKinder wieder. Sie wird als Unterdatenblatt für qryElternKind festgelegt und die Verknüpfung zu ParentID hergestellt. Einziges Filterkriterium ist wiederum das Feld ParentID, das diesmal aber ausdrücklich Nicht Null sein darf. Als Unterdatenblatt hat sie, ähnlich, wie dies auch schon bei der Haupttabelle geschah, sich selbst eingetragen. Wichtige zusätzliche Einstellung: Im Eigenschaftenblatt der Abfrage geben Sie an, dass es sich beim Recordsettyp um ein Snapshot handeln soll. Damit sind die Daten in der Ansicht nicht aktualisierbar, weshalb auch keine neuen Datensätze in ihr angelegt werden können. Die Zeilen für neue Datensätze verschwinden dadurch.

Die rekursive Detailabfrage qryKinder, welche im Unterdatenblatt mit sich selbst verknüpft ist, gibt Kinder, Enkel, Urenkel und weitere Abkommen wieder

Bild 5: Die rekursive Detailabfrage qryKinder, welche im Unterdatenblatt mit sich selbst verknüpft ist, gibt Kinder, Enkel, Urenkel und weitere Abkommen wieder

Die Hauptabfrage qryElternKind mit dem Unterdatenblatt qryKinder

Bild 6: Die Hauptabfrage qryElternKind mit dem Unterdatenblatt qryKinder

Öffnen Sie die Abfrage qryElternKind nun, so lassen sich die Kinder, Enkel, et cetera, ausklappen, wie in Bild 7. Von der Spalte ParentID ist hier nichts mehr zu sehen. Was nun noch stören könnte, wäre die Spalte ID, die den Anwender sicher nicht interessiert. Aus dem Abfrageergebnis kann sie jedoch nicht entfernt werden, weil sie für die Selbstverknüpfung benötigt wird. Allerdings können Sie hier zum kosmetischen Trick greifen und die Spalte in der Ansicht einfach ausblenden, indem Sie den Spaltenkopf ID rechtsklicken und im Kontextmenü den Eintrag Felder ausblenden auswählen. Wiederholen Sie dies für alle Unterebenen, in denen die Spalte ID auftaucht. Danach speichern Sie das Layout durch Klick etwa auf den Speichern-Button in der Schnellzugriffsleiste oder über das Menü Datei | Speichern. Beim nächsten Öffnen der Abfrage bleibt die Einstellung für ausgeblendete Felder erhalten und das Datenblatt sieht schließlich aus, wie in Bild 8.

Layout der Abfrage qryElternKind in der finalen Datenblattansicht

Bild 7: Layout der Abfrage qryElternKind in der finalen Datenblattansicht

Die qryElternKind in der Datenblattansicht mit einem kompletten Baum

Bild 8: Die qryElternKind in der Datenblattansicht mit einem kompletten Baum

Bliebe zu erwähnen, dass es natürlich noch andere Abfragemöglichkeiten gibt. Die Reihenfolge ließe sich auch umkehren: Statt der Eltern könnten auch die Kinder auf oberster Ebene erscheinen. Dazu müsste dann in den Unterdatenblättern nicht mehr die ParentID mit der ID verknüpft werden, sondern einfach die ID mit der ParentID. Beim Ausklappen der Datensatzknoten zeigten dann die Unterebenen jeweils die Eltern der Person an.

Auch sonst können Sie die Abfragen nach weiteren Kriterien filtern. Setzen Sie etwa als Kriterium für die Spalte mw den Ausdruck ="m" ein, so zeigten sich im Ergebnis nur die männlichen Verwandten.

Wir haben hier gezeigt, wie das Feature Unterdatenblatt von Access in Tabellen und Abfragen für Baumansichten genutzt werden kann. Die Einflussmöglichkeiten auf die Gestaltung sind dabei jedoch begrenzt. Das System lässt sich im Prinzip aber genauso auf Formulare mit Unterformularen und Berichte mit Unterberichten einsetzen. Auch hier kann etwa für einen Unterbericht der gleiche Bericht eingesetzt und mit sich selbst über ID und ParentID verknüpft werden. Damit hätten Sie eine druckbare Version mit deutlich mehr Gestaltungsspielraum.

Die ultimative Lösung für Baumansichten wäre jedoch ein Treeview. Das von Office immer mitinstallierte ActiveX-Steuerelement Microsoft Treeview Control kann allerdings nicht direkt an eine Datenquelle gebunden werden, sondern muss per VBA-Code gefüllt werden. Die Darstellung einer solchen Lösung liegt außerhalb des Rahmens dieses Beitrags, doch der grundsätzliche Zugriff auf rekursive Daten über VBA soll Ihnen hier nicht vorenthalten werden.

Datenzugriff auf Rekursive Tabellen

Nehmen wir an, Sie hätten ein Recordset auf Basis der Tabelle tblElternKind angelegt und als Kriterium angegeben, dass ParentID gleich Null sein soll, so, wie es bei der Abfrage qryElternKind geschah. Wie enumerieren Sie dann die jeweiligen Kinder eines Datensatzes? Und wie die Kinder der Kinder? Wenn Sie nicht zu einer Lösung mit hartkodierten Verhältnissen kommen möchten, wie eingangs im ersten Entwurf der Stammbaumdatenbank mit ihren separaten Tabellen für Eltern, Kinder und Enkeln, so kommen Sie auch hier nicht um rekursive Prozeduren herum. Erläutern wir das anhand der Prozedur RekursiveTabelleDurchlaufen des Moduls mdlRekursiv der Beispieldatenbank. In Listing 1 steht der Code dieser Prozedur samt ihrer rekursiven Unterprozedur fuKinder. Zunächst wird ein ungefiltertes Recordset rs auf alle Datensätze der Tabelle tblElternKind geöffnet. Erst in der Do-Loop-Schleife, welche alle Datensätze durchläuft, kommt es zur Unterscheidung, ob ein Personendatensatz kein Elter aufweist, weil ParentID auf Null steht. In diesem Fall wird der Datensatz per Debug.Print im VBA-Direktfenster ausgegeben und der Ausdruck "Elter: " vorangestellt. Zur Ermittlung der Kinder dieses Datensatzes ruft die Prozedur nun die Unterfunktion fuKinder auf.

Sub RekursiveTabelleDurchlaufen()

     Dim rs As DAO.Recordset

     Dim Level As Long

     

     Set rs = CurrentDb.OpenRecordset( _

         "SELECT * FROM tblElternKind", dbOpenDynaset)

     Do While Not rs.EOF

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!