1:n-Beziehung mit Standardzuordnung verwalten

2023, 6/2023, Tabellen und Datenmodellierung

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Im Beitrag “Rechnungsverwaltung: Kundenadressen ausgliedern” (www.access-im-unternehmen.de/1470) verwalten wir zu jedem Kunden mehrere Adressen, die dem Kunden über ein Fremdschlüsselfeld in der Adresstabelle zugewiesen sind. Für jeden Kunden legen wir in dieser Tabelle eine Standardadresse fest. Damit ist einiger Pflegeaufwand verbunden, denn wir müssen sicherstellen, dass immer genau eine Adresse je Kunde als Standardadresse markiert ist. In diesem Beitrag schauen wir uns an, wie wir sicherstellen können, dass die Daten immer konsistent sind und wir nicht plötzlich mehrere Standardadressen je Kunde vorfinden – oder gar keine.

Elemente der hier behandelten Konstellation

Die Konstellation, die wir in diesem Beitrag vorstellen, enthält folgende Elemente:

  • Hauptentität: Die Hauptentität repräsentiert den übergeordneten Datensatz oder die Hauptkomponente, zu der die Beziehung besteht. Im einführenden Beispiel ist die Hauptentität der Kunde.
  • Unterentitäten: Die Unterentitäten sind mit der Hauptentität verknüpft und repräsentieren zusätzliche Informationen oder Komponenten, die zu jeder Hauptentität gehören. Im Beispiel sind die Unterentitäten die Adressen.
  • Fremdschlüssel: Um die Beziehung zwischen der Hauptentität und den Unterentitäten herzustellen, wird ein Fremdschlüsselfeld in den Unterentitäten verwendet, das auf die Hauptentität verweist. In unserem Beispiel ist dies das KundeID-Feld in der Adresstabelle.
  • Standardentität: Es gibt eine Möglichkeit, eine der Unterentitäten als Standard oder Hauptentität für jede Hauptentität festzulegen. Dies wird durch ein zusätzliches Feld wie Standardadresse erreicht.

Weitere Beispiele für diese Konstellation

Neben dem in der Einleitung vorgestellten Beispiel mit den Kunden und den zugeordneten Adressen (siehe Bild 1) gibt es noch weitere Beispiele:

Beispiel für eine 1:n-Beziehung mit Standardzuordnung

Bild 1: Beispiel für eine 1:n-Beziehung mit Standardzuordnung

  • Personen und verschiedene Telefonnummern oder E-Mail-Adressen, die in einer separaten Tabelle gespeichert werden und von denen eine als Standard-E-Mail-Adresse oder Standardtelefonnummer markiert wird
  • Produkte und verschiedene Lieferanten, von denen einer als Standardlieferant festgelegt wird
  • Produkte und Variante: Für jedes Produkt gibt es verschiedene Varianten, von denen eine als Standardvariante vorgesehen wird.

Pflege von 1:n-Beziehungen mit Standardzuordnung

Übliche 1:n-Beziehungen, deren Pflege durch die Definition der Beziehung und der referenziellen Integrität mit oder ohne Löschweitergabe oder Aktualisierungsweitergabe geregelt ist, benötigen keinen zusätzlichen Aufwand – man legt einen Datensatz in der Detailtabelle an, beispielsweise tblAnreden, und wenn man einen Datensatz in der Mastertabelle wie tblKunden hinzufügt, wählt man einen der Datensätze aus der Detailtabelle aus.

Bei unserem Beispiel ist es im Prinzip genauso: Wir wählen für jeden Datensatz der Tabelle tblAdressen über das Fremdschlüsselfeld KundeID einen Datensatz aus der Tabelle tblKunden aus.

Nun wollen wir jedoch angeben, welche der Adressen aus der Tabelle tblAdressen die Standardadresse ist, die zum Beispiel beim Anlegen neuer Rechnungen oder Bestellungen auf Basis dieses Kunden herangezogen werden soll (siehe Bild 2).

Die 1:n-Beziehung mit Standardzuordnung im Formular

Bild 2: Die 1:n-Beziehung mit Standardzuordnung im Formular

Wenn wir einem Kunden eine erste Adresse hinzufügen, soll diese direkt als Standardadresse markiert werden.

Das allein ist schon nicht einfach: Wir können nicht einfach den Standardwert des Feldes Standardadresse auf Wahr einstellen, denn wenn der Benutzer anschließend eine neue Adresse für diesen Kunden hinzufügt, würde das Feld Standardadresse für diesen Kunden ebenfalls auf Wahr eingestellt werden und wir hätten bereits zwei als Standardadresse markierte Adressen für diesen Kunden. Das Einstellen des Feldes Standardadresse auf den Wert Wahr entfällt also bereits.

Daraus lassen sich bereits erste Regeln für unser Vorhaben ableiten:

  • Wenn eine neue Adresse angelegt wird und dies ist die erste Adresse für diesen Kunden, soll das Feld Standardadresse den Wert Wahr erhalten.
  • Wenn eine neue Adresse angelegt wird und es ist nicht die erste Adresse für diesen Kunden, müssen wir prüfen, ob es bereits eine Standardadresse gibt. Aber was dann – welche Adresse legen wir dann als Standardadresse fest? Wir schlagen vor, dass in diesem Fall die zuletzt hinzugefügte Adresse als Standardadresse festgelegt werden soll.

Davon abgesehen müssten wir eine Vorgehensweise etablieren, die beim Löschen einer der Adressen in Aktion tritt. Mit dieser müssen wir prüfen, ob einer der verbleibenden Adressdatensätze die Standardadresse für diesen Kunden ist und falls nicht, müssten wir eine der verbleibenden Adressen zur neuen Standardadresse machen.

Man könnte hier einfach die zuerst angelegte Adresse zur Standardadresse machen oder auch die zuletzt angelegte – oder auch eine ganz andere, beispielsweise eine, die besonders oft für Bestellungen oder Rechnungen verwendet wurde. Wir können auch den Benutzer fragen, welche Adresse die neue Standardadresse werden soll. Und wenn wir sie einfach festlegen, sollten wir dem Benutzer darüber informieren.

Entfernen des Status als Standardadresse

Der Benutzer könnte auf die Idee kommen, den Haken für das Feld Standardadresse zu entfernen, weil er diese Adresse nicht mehr als Standardadresse verwenden möchte. Das wollen wir verhindern – der Benutzer soll die aktuelle Standardadresse nur durch Setzen einer neuen Standardadresse entfernen können.

Setzen des Status als Standardadresse

Wenn der Benutzer eine neue Standardadresse einstellen möchte, soll das durch einfaches Aktivieren des Kontrollkästchens für das Feld Standardadresse möglich sein. Wenn dies geschieht, müssen wir allerdings dafür sorgen, dass das Feld Standardadresse für alle anderen Datensätze der Adresstabelle, die diesem Kunden zugeordnet sind, entfernt wird.

Ermitteln der aktuellen Standardadresse

Schließlich wird es Situationen geben, in denen wir, beispielsweise zum Steuern der Darstellung in Formularen, die aktuelle Standardadresse für einen Kunden ermitteln müssen.

VBA-Funktionen zum einfachen Umsetzen der Anforderungen

Damit wir die oben genannten Anforderungen einfach umsetzen können, wollen wir dazu einen flexibel einsetzbaren Satz an VBA-Funktionen bereitstellen, mit denen wir nicht nur die Standardadresse aus den Adressen eines Kunden, sondern auch die Standarddatensätze für andere Datenmodelle ermitteln können.

Welche Funktionen benötigen wir also dazu?

  • Ermitteln des aktuellen Standardwertes. Wenn diese Funktion den Wert 0 zurückliefert, wenn also kein Standardwert vorhanden ist, haben wir gleichzeitig bereits eine Funktion, um zu prüfen, ob ein Standardwert vorhanden ist.
  • Setzen des aktuellen Standardwertes. Setzt voraus, dass zuvor bisherige Standardwerte entfernt wurden.
  • Entfernen bisheriger Standardwerte. Setzt voraus, dass anschließend ein neuer Standardwert gesetzt wird.
  • Ermitteln des neuen Standardwertes, falls keiner festgelegt ist.

Diese Funktionen müssen wir nun noch so verknüpfen, dass sie sinnvoll in einer Anwendung einsetzbar sind. Außerdem müssen wir noch festlegen, zu welchen Zeitpunkten die Integrität dieser Daten geprüft werden soll – also beispielsweise, ob der aktuell angezeigte Kundendatensatz eine Standardadresse aufweist. Also schauen wir uns nun die Zeitpunkte an, zu denen wir auf die Daten zugreifen und diese ändern. Dies ist dann die Grundlage für die tatsächlich zu definierenden Funktionen zur Pflege der 1:n-Beziehung mit Standardzuordnung.

Zeitpunkte des Zugriffs auf die 1:n-Beziehung mit Standardzuordnung

Wann also greifen wir tatsächlich auf die Standardzuordnungen zu? Wir sehen die folgenden Zeitpunkte:

  • Öffnen eines Formulars, das die Daten des Masterdatensatzes und des Detaildatensatzes anzeigt: Hier sollte geprüft werden, ob eine Standardzuordnung besteht und gegebenenfalls eingerichtet werden.
  • Wechsel zu einem anderen Datensatz im gleichen Formular
  • Anlegen einer neuen Adresse für einen Kunden: Abfrage, ob eine Standardzuordnung besteht und gegebenenfalls einrichten
  • Löschen einer Adresse eines Kunden: Abfragen, ob nach dem Löschen noch eine Standardzuordnung besteht und gegebenenfalls einrichten
  • Ändern der Standardzuordnung durch den Benutzer: Löschen aller vorherigen Zuordnungen und anlegen der neuen Zuordnung

Funktionen zum Verwalten der Standardzuordnungen

Basierend auf diesen Anforderungen haben wir die folgenden Funktionen abgeleitet, die wir anschließend im Detail betrachten:

  • HoleStandardzuordnung: Liest die ID des Datensatzes mit der Standardzuordnung für die angegebene Tabelle, Standardfeld, Fremdschlüsselfeld und Fremdschlüsselwert ein.
  • SetzeStandardzuordnung: Setzt den Wert des mit Standardfeld übergebenen Feldes in der angegebenen Tabelle für den angegebenen Fremdschlüsselwert im Feld des Parameters Fremdschluesselfeld auf True.
  • EntferneStandardzuordnungen: Stellt für alle Datensätze der mit strTabelle angegebenen Tabelle mit dem Wert aus lngFKID im Feld aus strFKFeld auf False ein.
  • HoleErstezuordnung: Liest den Primärschlüsselwert des ersten Datensatzes der mit strTabelle angegebenen Tabelle ein, deren Feld aus strFKFeld den Wert aus lngFKID aufweist.
  • HoleOderSetzeStandardzuordnung: Kombination aus mehreren der vorgenannten Funktionen, die versucht, die Standardadresse zu holen und falls dies nicht gelingt, eine neue Standardadresse zu setzen.
  • MehrfacheStandardzuordnungenEntfernen: Entfernt für die mit strTabelle angegebene Tabelle alle doppelten Standardzuordnungen mit Ausnahme der Ersten.

Standardzuordnung holen

Die Funktion HoleStandardzuordnung ermittelt mit der DLookup-Funktion den Primärschlüsselwert des Datensatzes der Tabelle aus strTabelle, dessen Fremdschlüsselfeld den Fremdschlüsselwert aus lngFKID enthält, und gibt diesen als Funktionsergebnis zurück (siehe Listing 1).

Public Function HoleStandardzuordnung(strTabelle As String, strStandardfeld As String, strPKFeld As String, _
         strFKFeld As String, lngFKID As Long) As Long
     Dim lngPKID As Long
     lngPKID = Nz(DLookup(strPKFeld, strTabelle, strFKFeld & " = " & lngFKID & " AND " & strStandardfeld & " = -1"), 0)
     HoleStandardzuordnung = lngPKID
End Function

Listing 1: Funktion zum Ermitteln der Standardzuordnung

Standardzuordnung setzen

Die Funktion SetzeStandardzuordnung aktualisiert die mit strTabelle übergebene Tabelle, indem Sie mit einer UPDATE-Anweisung das Feld aus strStandardfeld für den Datensatz auf den Wert -1 aktualisiert, der im Feld aus strPKFeld den Wert aus lngPKID aufweist (siehe Listing 2).

Public Sub SetzeStandardzuordnung(strTabelle As String, strStandardfeld As String, strPKFeld As String, lngPKID As Long)
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE " & strTabelle & " SET " & strStandardfeld & " = -1 WHERE " & strPKFeld & " = " & lngPKID, _
         dbFailOnError
End Sub

Listing 2: Funktion zum Setzen einer Standardzuordnung

Standardzuordnungen entfernen

Die Funktion EntferneStandardzuordnungen stellt das Feld aus strStandardfeld mit einer UPDATE-Anweisung für alle Datensätze der Tabelle aus strTabelle auf den Wert 0 ein, deren Feld aus strFKFeld den Wert aus lngFKID aufweist (siehe Listing 3).

Public Sub EntferneStandardzuordnungen(strTabelle As String, strStandardfeld As String, strFKFeld As String, _
         lngFKID As Long)
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE " & strTabelle & " SET " & strStandardfeld & " = 0 WHERE " & strFKFeld & " = " & lngFKID, _
         dbFailOnError
End Sub

Listing 3: Funktion zum Entfernen von Standardzuordnungen

Erste Zuordnung holen

Die Funktion HoleErsteZuordnung verwendet den Aufruf der DLookup-Funktion, um den Wert des Feldes aus strPKFeld aus dem ersten Datensatz der Tabelle aus strTabelle zu holen, dessen Feld aus strFKFeld den Wert aus lngFKID enthält (siehe Listing 4).

Public Function HoleErsteZuordnung(strTabelle As String, strPKFeld As String, strFKFeld As String, _
         lngFKID As Long) As Long
     HoleErsteZuordnung = Nz(DLookup(strPKFeld, strTabelle, strFKFeld & " = " & lngFKID), 0)
End Function

[

Listing 4: Funktion zum Ermitteln der ersten Standardzuordnung

Standardadresse holen oder setzen, falls nicht vorhanden

Die Funktion HoleOderSetzeStandardzuordnung verwendet einige der zuvor bereits beschriebenen Funktionen (siehe Listing 5). Als Erstes versucht sie mit der Funktion HoleStandardzuordnung, den Wert des Feldes strPKFeld aus der Tabelle aus strTabelle zu holen, die im Feld aus strFKFeld den Wert aus lngFKID enthält.

Public Function HoleOderSetzeStandardzuordnung(strTabelle As String, strStandardfeld As String, strPKFeld As String, _
         strFKFeld As String, lngFKID As Long) As Long
     Dim lngPKID As Long
     lngPKID = HoleStandardzuordnung(strTabelle, strStandardfeld, strPKFeld, strFKFeld, lngFKID)
     If lngPKID = 0 Then
         lngPKID = HoleErsteStandardzuordnung(strTabelle, strPKFeld, strFKFeld, lngFKID)
         If Not lngPKID = 0 Then
             Call SetzeStandardzuordnung(strTabelle, strStandardfeld, strPKFeld, lngPKID)
         End If
     End If
     HoleOderSetzeStandardadresse = lngPKID
End Function

Listing 5: Funktion zum Holen der Standardzuordnung oder, falls nicht vorhanden, zum Setzen einer neuen Standardzuordnung

Liefert dies den Wert 0, liest sie durch einen Aufruf der Funktion HoleErsteStandardzuordnung den Wert des Feldes aus strPKFeld des ersten Datensatzes aus der Tabelle aus strTabelle ein, dessen Feld aus strFKFeld den Wert aus lngFKID enthält.

Liefert dies ebenfalls den Wert 0, ist noch kein zugeordneter Datensatz in der Tabelle aus strTabelle vorhanden. Dann liefert die Funktion den Wert 0 zurück. Anderenfalls ruft die Prozedur die Funktion SetzeStandardzuordnung auf, die in der Tabelle aus strTabelle den Wert des Feldes strStandardfeld für den Datensatz auf den Wert -1 setzt, dessen Feld aus strPKFeld den soeben ermittelten Wert aus lngPKID aufweist. In jedem Fall wird der Wert aus lngPKID als Funktionsergebnis zurückgeliefert.

Mehrfache Standardzuordnungen entfernen

Schließlich fehlt noch die Funktion MehrfacheStandardzuordnungenEntfernen, die man regelmäßig aufrufen sollte, um eventuell entstandene Dubletten zu entfernen. Sie erstellt ein Recordset auf Basis einer SELECT-Abfrage, welche die Werte des Fremdschlüsselfeldes aus strFKFeld für alle Datensätze aus der Tabelle strTabelle liefert, die für den gleichen Wert in strFKFeld mehr als einen Datensatz aufweisen, für den das Feld aus strStandardfeld den Wert -1 enthält.

Dieses Recordset durchläuft die Tabelle, um mit der Funktion HoleStandardzuordnung den Wert des Primärschlüsselfeldes aus dem Feld aus strPKFeld für die angegebenen Parameter holt und diesen in lngErsteStandardzuordnungID speichert. Dann entfernt sie alle Standardzuordnungen mit EntferneStandardzuordnungen und fügt die mit der ID aus lngErsteStandardzuordnungID wieder hinzu (siehe Listing 6).

Public Function MehrfacheStandardzuordnungenEntfernen(strTabelle As String, strStandardfeld As String, _
         strPKFeld As String, strFKFeld As String)
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim lngErsteStandardzuordnungID As Long
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT " & strFKFeld & " FROM " & strTabelle & " WHERE " & strStandardfeld _
         & " = -1 GROUP BY " & strFKFeld & " HAVING Count(*) > 1", dbOpenDynaset)
     Do While Not rst.EOF
         lngErsteStandardzuordnungID = HoleStandardzuordnung(strTabelle, strStandardfeld, strPKFeld, _
             strFKFeld, rst(strFKFeld))
         Call EntferneStandardzuordnungen(strTabelle, strStandardfeld, strFKFeld, rst(strFKFeld))
         Call SetzeStandardzuordnung(strTabelle, strStandardfeld, strPKFeld, lngErsteStandardzuordnungID)
         rst.MoveNext
     Loop
End Function

Listing 6: Funktion zum Entfernen aller doppelten Standardzuordnungen

Funktionen zur Verwaltung von Standardzuordnungen in der Praxis

Die hier vorgestellten Funktionen verwenden wir in einer Lösung, die wir im Beitrag Rechnungsverwaltung: Kundenadressen ausgliedern (www.access-im-unternehmen.de/1470) erläutert haben. Hier haben wir gleich mehrere Adresstabellen namens tblLieferadressen_Kunde und tblRechnungsadressen_Kunde wie im eingangs gezeigten Screenshot des Datenmodells zu sehen. In dieser Lösung fügen wir die Funktionen aus diesem Beitrag an verschiedenen Stellen hinzu. Details dazu finden Sie im genannten Beitrag.

An dieser Stelle schauen wir uns ein sehr einfaches Beispiel an. Dabei wollen wir Personen keine, eine oder mehrere E-Mail-Adressen zuordnen. Das Datenmodell umfasst nur zwei Tabellen: Die erste heißt tblPersonen und enthält nur die Felder ID, Vorname und Nachname.

Die zweite enthält die E-Mail-Adressen sowie ein Fremdschlüsselfeld zum Festlegen der Person, zu der die E-Mail-Adresse gehört und ein Ja/Nein-Feld namens StandardEMail. Damit wollen wir festlegen, welche der E-Mail-Adressen eines Kunden standardmäßig verwendet werden soll (siehe Bild 3).

Datenmodell für ein einfaches Beispiel

Bild 3: Datenmodell für ein einfaches Beispiel

Zum Verwalten der Daten verwenden wir ein Hauptformular namens frmPersonenEMails und ein Unterformular namens sfmPersonenEMails. Das Hauptformular zeigt die Daten der Tabelle tblPersonen an. Das Unterformular liefert alle E-Mail-Adressen, die über das Feld PersonID mit der Person aus dem Hauptformular verknüpft sind (siehe Bild 4).

Entwurf des Beispielformulars

Bild 4: Entwurf des Beispielformulars

Das Kontrollkästchen, das an das Feld StandardEMail gebunden ist, haben wir chkStandardEMail genannt, damit wir es unter VBA eindeutig von dem gebundenen Feld StandardEMail unterscheiden können.

Standardzuordnung beim Anlegen des ersten Datensatzes

Wir gehen die Funktionen, die zum automatischen Verwalten der Standardzuordnung nötig sind, in der Reihenfolge durch, wie sie eingesetzt werden – beginnend mit leeren Tabellen. Wir fügen also zuerst im Hauptformular einen neuen Datensatz hinzu. Dann wechseln wir zum Unterformular, um eine erste E-Mail-Adresse anzulegen.

Wenn noch keine E-Mail-Adresse vorhanden ist, sollte der neue, leere Datensatz im Feld StandardEMail bereits den Wert Wahr als Standardwert enthalten. Dazu braucht es nicht viel: Wir müssen nur beim Anzeigen eines neuen Datensatzes im Unterformular eine Prozedur auslösen, die prüft, ob es für den aktuellen Datensatz im Hauptformular schon eine Standard-E-Mail-Adresse gibt.

Und dafür haben wir ja schon die richtige Funktion vorbereitet – HoleStandardzuordnung. Wenn diese den Wert 0 liefert, gibt es noch keine, und dann soll das Feld StandardEMail auf Wahr eingestellt werden.

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

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar