Reflexive 1:n-Beziehung zu m:n-Beziehung

Manchmal legt man eine 1:n-Beziehung zwischen zwei Tabellen an, um später festzustellen, dass eine m:n-Beziehung doch die funktionalere Variante ist. Ein Beispiel sind reflexive Beziehungen, mit denen man etwa Vater-Kind-Beziehungen abbildet oder Partner-Beziehungen. Andere Beispiele sind solche wie zwischen Produkten und Kategorien – man dachte zunächst, dass es reicht, wenn man jedes Produkt nur einer Kategorie zuordnen kann, aber man dann erkennt, dass es für verschiedene Anwendungsfälle doch günstiger wäre, wenn man ein Produkt mehr als einer Kategorie zuordnen kann. Ähnliche Fälle sind Mitarbeiter und Funktionen oder Abteilungen. In diesem Beitrag schauen wir uns das Beispiel eines Kunden an, der in seinem Sportverein partnerschaftliche Beziehungen über ein Fremdschlüsselfeld der Tabelle tblMitglieder auf diese selbst abgebildet hat. Hier gab es mehrere Gründe, um diese Beziehung in eine m:n-Beziehung umzuwandeln. Welche das sind und wie wir die Umwandlung durchgeführt haben, lesen Sie in diesem Beitrag.

Das Abbilden von reflexiven Beziehungen über eine 1:n-Beziehung ist an sich nicht falsch. Im Beispiel geht es um einen Sportverein, dessen Mitglieder mit einer Access-Datenbank verwaltet werden. In der Tabelle tblMitglieder gibt es zwei Felder, um die partnerschaftliche Beziehung zwischen zwei Mitgliedern abzubilden. Das erste heißt PartnerID und dient dazu, die ID eines anderen Datensatzes dieser Tabelle zu referenzieren und damit anzugeben, welches andere Mitglied mit diesem Mitglied verbunden ist. Zusätzlich gibt es ein Ja/Nein-Feld namens Ehepartner, das angibt, ob es sich um eine amtlich dokumentierte Beziehung handelt, sprich um eine eheliche Beziehung (Ja) oder um eine Beziehung ohne Trauschein (Nein). Der Entwurf der Tabelle sieht in abgespeckter Form wie in Bild 1 aus.

Bild 1: Entwurf der Tabelle tblMitglieder

Das erste Problem, das sich daraus ergibt, ist die wechselseitige Abhängigkeit. Wenn wir für Mitglied A das Mitglied B als Partner festgelegt haben, müssen wir für Mitglied B auch Mitglied A als Partner hinterlegen – und für beide muss das Feld Ehepartner den gleichen Wert enthalten. Anderenfalls erhalten wir einen Fall von inkonsistenten Daten.

Diese Inkonsistenz kann nicht nur den Fall enthalten, dass die Beziehung nur in einer Richtung dokumentiert ist, etwa von Mitglied A zu Mitglied B. Es kann auch passieren, dass nicht nur Mitglied B als Partner von Mitglied A angegeben wird, sondern auch noch Mitglied C als Partner von Mitglied B. Es konnte auch vorkommen, dass ein Mitglied als Ehepartner von mehreren anderen Mitgliedern angegeben wird.

Das zweite Problem bei der Tabelle tblMitglieder bei diesem Kunden war, dass bereits sehr viele andere Tabellen auf die Tabelle tblMitglieder verwiesen hatten und somit die Grenze von maximal 32 Beziehungen erreicht war.

Diese Grenze gilt für Beziehungen, die über ein Fremdschlüsselfeld in dieser Tabelle hergestellt werden, wobei wir hier sehr viele Lookup-Tabellen vorgefunden haben.

Die Beziehung war wie in Bild 2 aufgebaut, wobei das Fremdschlüsselfeld PartnerID auf das Feld MitgliedID der gleichen Tabelle verweist.

Bild 2: Reflexive Beziehung der Mitglieder

Mehrere Partner für das gleiche Mitglied verhindern

Die Möglichkeit, dass ein Mitglied für mehr als ein anderes Mitglied als Partner ausgewählt wird, konnten wir durch das Setzen eines eindeutigen Schlüssels für das Fremdschlüsselfeld PartnerID erreichen.

Dadurch, dass nun jede MitgliedID nur einmal in das Feld PartnerID eingetragen werden konnte, stellen wir sicher, dass es nur monogame Beziehungen gibt. Hier lassen wir außen vor, dass es Kulturen gibt, in denen dies möglich ist.

Auswahl einer Beziehung mit sich selbst verhindern

Eine weitere mögliche Fehleingabe ist die Auswahl des Mitglieds selbst als Partner.

Dies können wir verhindern, indem wir im Formular, das wir gleich beschreiben, nur die anderen Datensätze der Tabelle tblMitglieder anzeigen.

Nur eindeutige Partnerschaften erlauben

Wenn wir sicherstellen wollen, dass die in einer Partnerschaft befindlichen Mitglieder immer wechselseitig miteinander verbunden werden, müssen wir das über die Anwendungslogik realisieren – allein über den Tabellenentwurf können wir das mit einem Fremdschlüsselfeld, das die gleiche Tabelle referenziert, nicht erreichen.

Wir müssen also im Formular zum Verwalten der Mitglieder eine VBA-Funktion einbauen, die beim Auswählen des Partners eines Mitglieds automatisch auch das Feld PartnerID des Partners einstellt – und die auch den gleichen Wert im Ja/Nein-Feld Ehepaar für die verknüpften Datensätze festlegt.

Im Entwurf der Tabelle tblMitglieder haben wir für das Fremdschlüsselfeld PartnerID ein Nachschlagefeld definiert, damit man den Partner einfach auswählen kann (siehe Bild 3).

Bild 3: Auswahlfeld für den Partner

Formular zum Verwalten der Mitglieder und Partnerschaften

Um die Mitglieder zu verwalten und die Partner zuordnen zu können, haben wir ein Formular wie in Bild 4 vorgefunden.

Bild 4: Formular zur Mitgliederverwaltung

Für das Steuerelement cboPartnerID haben wir zunächst die folgende Datensatzherkunft eingestellt, um aus allen Mitgliedern auswählen zu können:

SELECT MitgliedID, Vorname & '' '' & Nachname FROM tblMitglieder

Damit beim Anzeigen eines Datensatzes nicht das aktuelle Mitglied selbst erscheint und auch nicht solche Mitglieder, die bereits einer Partnerschaft zugeordnet sind, haben wir für das Ereignis Beim Anzeigen die folgende Prozedur hinterlegt:

Private Sub Form_Current()
     Me.cboPartnerID.RowSource = "SELECT MitgliedID, " _
         & "Vorname & '' '' & Nachname FROM tblMitglieder " _
         & "WHERE NOT (MitgliedID = " & Me.MitgliedID & ") " _
         & " AND (MitgliedID NOT IN (" _
         & "    SELECT PartnerID FROM tblMitglieder " _
         & "    WHERE PartnerID IS NOT NULL))"
End Sub

Für das Mitglied mit dem Wert 1 im Feld MitgliedID erhalten wir so die folgende Abfrage:

SELECT MitgliedID, Vorname & '' '' & Nachname 
FROM tblMitglieder 
WHERE NOT (MitgliedID = 1) 
AND (MitgliedID NOT IN (
     SELECT PartnerID FROM tblMitglieder 
     WHERE PartnerID IS NOT NULL)
)

Damit erscheinen, wenn wir noch keinem Mitglied einen Partner zugewiesen haben, für jedes Mitglied nur die aktuell verfügbaren Partner im Feld cboPartnerID – siehe Bild 5.

Bild 5: Formular zur Mitgliederverwaltung in der Formularansicht

Partnerschaft für den Partner einstellen

Wenn wir nun für das Mitglied mit dem Wert 1 im Feld MitgliedID einen Partner auswählen, etwa den mit dem Wert 2, soll für das Mitglied 2 das Mitglied 1 als Partner eingestellt werden.

Dazu hinterlegen wir für das Ereignis Nach Aktualisierung des Kombinationsfeldes cboPartnerID die folgende Prozedur:

Private Sub cboPartnerID_AfterUpdate()
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE tblMitglieder SET PartnerID = " _
         & Me.MitgliedID & " WHERE MitgliedID = " _
         & Me.cboPartnerID, dbFailOnError
End Sub

Die so ausgeführte Aktionsabfrage lautet beispielsweise:

UPDATE tblMitglieder SET PartnerID = 1 WHERE MitgliedID = 2

Damit erhalten wir das gewünschte Ergebnis in der zugrunde liegenden Tabelle, aber wenn wir zum Datensatz des gewählten Partners wechseln und dann zurück, zeigt das Kombinationsfeld nun für beide gar keinen Wert mehr an. Das liegt daran, dass wir die bereits vergebenen Partner vollständig ausschließen und diese somit auch für den ausgewählten Eintrag nicht mehr angezeigt werden. Das Kombinationsfeld enthält zwar den korrekten Eintrag, was wir im Direktbereich mit folgender Anweisung ermitteln können:

  Screen.ActiveControl.Value
  2 

Aber da der Eintrag nicht in der Datensatzherkunft des Kombinationsfeldes enthalten ist, erscheint der Anzeigewert nicht.

Wir müssen die Datensatzherkunft also nochmals anpassen, indem wir mit der OR-Klausel am Ende den gewählten Partner wieder mit in die Auswahl hineinnehmen – hier der besseren Lesbarkeit halber direkt mit den Werten 1 für MitgliedID und 2 für PartnerID:

SELECT MitgliedID, Vorname & '' '' & Nachname 
FROM tblMitglieder 
WHERE NOT (MitgliedID = 1) AND (MitgliedID NOT IN (
     SELECT PartnerID FROM tblMitglieder 
     WHERE PartnerID IS NOT NULL) 
OR MitgliedID = 2)

Damit sehen wir nun den ausgewählten Partner sowohl für den ersten als auch für den zweiten Datensatz. In der OR-Bedingung sorgen wir außerdem mit der Nz-Funktion dafür, dass im Falle des Wertes NULL im Feld PartnerID der Wert 0 verwendet wird – sonst erhalten wir beim Wechsel von einem Datensatz ohne PartnerID zu einem anderen Datensatz einen Fehler.

Schließlich müssen wir auch noch dafür sorgen, dass der Wert für das Feld Ehepaar für das als Partner angegebene Mitglied so einstellen wie für das Mitglied selbst. Dazu fügen wir eine Prozedur für das Ereignis Nach Aktualisierung des Kontrollkästchens chkEhepaar hinzu:

Private Sub chkEhepaar_AfterUpdate()
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE tblMitglieder SET Ehepaar = " _
         & IIf(Me.chkEhepaar = True, "True", "False") _
         & " WHERE MitgliedID = " & Me.cboPartnerID, _
         dbFailOnError
End Sub

Warum auf m:n-Beziehung umstellen?

Eingangs haben wir erwähnt, dass sich viele der hier beschriebenen Probleme beheben lassen, wenn wir die reflexive Beziehung über die 1:n-Verknüpfung aufheben, die Felder PartnerID und Ehepaar entfernen und beides in eine m:n-Beziehung auslagern.

Wir schauen uns erst einmal an, wie die Verknüpfungstabelle aussieht und wie wir diese mit der Tabelle tblMitglieder verknüpfen. Damit das Beispiel mit der reflexiven 1:n-Beziehung erhalten bleibt, erstellen wir in der Beispieldatenbank eine Kopie der Tabelle tblMitglieder (siehe Bild 6) unter dem Namen tblMitgliederMN. Außerdem fügen wir die Tabelle tblPartnerschaften hinzu, die folgende Felder enthält:

Bild 6: Aktueller Zustand der Daten in der Tabelle tblMitglieder

• PartnerschaftID: Primärschlüsselfeld der Tabelle
• PartnerA: Erster Partner, Fremdschlüsselfeld zur Tabelle tblMitglieder, eindeutiger Index, Eingabe erforderlich
• PartnerB: Zweiter Partner, Fremdschlüsselfeld zur Tabelle tblMitglieder, eindeutiger Index, Eingabe erforderlich
• Ehepaar: Ja/Nein-Feld, das angibt, ob es sich um ein Ehepaar handelt

Dadurch, dass wir einen eindeutigen Index auf die Felder PartnerA und PartnerB legen, stellen wir sicher, dass jedes Mitglied nur einmal als PartnerA oder PartnerB angegeben werden kann.

Wir haben allerdings noch eine kleine Lücke: Wir können die Kombination aus Mitglied 1 und Mitglied 2 immer noch zweimal angeben – einmal mit dem Wert 1 im Feld PartnerA und 2 in PartnerB und umgekehrt. Außerdem können wir theoretisch auch Mitglied 1 sowohl in das Feld PartnerA als auch in PartnerB eingeben.

Dies verhindern wir, indem wir für die Tabelle eine Gültigkeitsregel anlegen, die wir wie in Bild 7 definieren.

Bild 7: Tabelle tblPartnerschaften

Wenn wir nun eine Partnerschaft eingeben, bei welcher der Wert von PartnerA kleiner oder gleich der von PartnerB ist, erhalten wir die Meldung aus Bild 8.

Bild 8: Meldung beim Eingeben ungültiger Daten

Diese Regel ist nur für den Fall vorgesehen, dass jemand versucht, Daten direkt über die Tabelle einzugeben. Das eigentliche Anlegen erledigen wir ohnehin über das Formular.

Beziehung zwischen Mitgliedern und Partnerschaften

Damit nur Werte aus dem Primärschlüsselfeld der Tabelle tblMitglieder eingegeben werden können, fügen wir dem Datenmodell die beiden Beziehungen aus Bild 9 hinzu.

Bild 9: Beziehungen für die Verwaltung von Partnerschaften per m:n-Beziehung

Wichtig ist hier die Richtung, in der wir die Beziehungspfeile ziehen. Da die beiden Felder PartnerA und PartnerB mit einem eindeutigen Index versehen wurden, müssen wir den Pfeil von tblMitglieder auf tblPartnerschaften ziehen, sodass die Mitgliedertabelle unter Tabelle/Abfrage und die Partnerschaftstabelle unter Verwandte Tabelle/Abfrage angegeben wird.

Andersherum könnten wir keine referenzielle Integrität für die Beziehungen festlegen, da dann das Feld MitgliedID der Tabelle tblMitglieder als Fremdschlüsselfeld betrachtet würde und es darin Werte gibt, die zurzeit noch nicht im Feld PartnerA oder PartnerB der Tabelle tblPartnerschaften enthalten sind.

Daten migrieren

Es gibt bereits eingetragene Partnerschaften in der Tabelle tblMitarbeiterMN, die wir nun über die Verknüpfungstabelle tblPartnerschaften abbilden wollen.

Das ist anspruchsvoll, weil wir die Partnerschaften so eintragen müssen, dass der Wert im Feld PartnerA immer kleiner als der in PartnerB ist. Wir könnten starten, indem wir die Partnerschaften aufsteigend nach der ID des Mitglieds sortieren und zusätzlich die PartnerID und den Ehepaar-Status mit folgender Abfrage ausgeben lassen:

SELECT
     tblMitgliederMN.MitgliedID,
     tblMitgliederMN.PartnerID,
     tblMitgliederMN.Ehepaar
FROM
     tblMitgliederMN
WHERE
     (((tblMitgliederMN.PartnerID) IS NOT NULL))
ORDER BY
     tblMitgliederMN.MitgliedID;

Dies liefert jede Partnerschaft in doppelter Ausführung, jeweils einmal mit jeder Mitglieds-ID im Feld MitgliedID und einmal im Feld PartnerID (siehe Bild 10).

Bild 10: Ausgabe aller Partnerschaften laut tblMitgliederMN

Wenn wir hier allerdings den Ausdruck, den wir als Gültigkeitsregel für die Tabelle tblPartnerschaften verwendet haben, als Kriterium einsetzen, erhalten wir jede Partnerschaft nur noch einmal:

SELECT
     tblMitgliederMN.MitgliedID,
     tblMitgliederMN.PartnerID,
     tblMitgliederMN.Ehepaar
FROM
     tblMitgliederMN
WHERE
     (
         ((tblMitgliederMN.MitgliedID) < [PartnerID])
         AND ((tblMitgliederMN.PartnerID) IS NOT NULL)
     )
ORDER BY
     tblMitgliederMN.MitgliedID;

Für diese Abfrage aktivieren wir nun den Abfragetyp Anfügen und geben als Zieltabelle die Tabelle tblPartnerschaften an. Im Entwurf weisen wir das Feld MitgliedID dem Feld PartnerA und das Feld PartnerID dem Feld PartnerB hinzu. Das Feld Ehepaar weisen wir dem gleichnamigen Feld der Tabelle tblPartnerschaften zu (siehe Bild 11).

Bild 11: Anfügeabfrage für die Partnerschaftstabelle

Führen wir diese Abfrage aus, erhalten wir das Ergebnis aus Bild 12. Durch das Kriterium landen auch nur gültige Datensätze in dieser Abfrage – sollte die Tabelle tblMitgliederMN zuvor Datensätze enthalten haben, bei denen PartnerA gleich PartnerB wäre, oder es gäbe inkonsistente Daten, würden diese nicht angelegt werden.

Bild 12: Ergebnis der Anfügeabfrage

Es ist allerdings nicht auszuschließen, dass für Mitglied 1 eine Partnerschaft mit Mitglied 2 angelegt war und für Mitglied 2 eine Partnerschaft mit Mitglied 3. Dies können wir über das Datenmodell in der aktuellen Form nicht ausschließen und müssten es letztlich über die Anwendungslogik steuern. Allerdings bietet Access auch noch die Datenmakros an. Wie wir damit auch die letzte mögliche Fehleingabe verhindern, zeigen wir in den folgenden Abschnitten.

Doppelte Eingabe in verschiedenen Feldern per Datenmakro verhindern

Datenmakros sind eine Art Trigger für Access-Tabellen. In diesem Fall nutzen wir das Ereignis Vor Aktualisierung, um vor dem Speichern des Datensatzes ein Datenmakro auszulösen. Dieses legen wir in der Datenblattansicht mit dem Ribbonbefehl Tabelle|Vorabereignisse|Vor Änderung an (siehe Bild 13).

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo