In Datenbanken werden verschiedene Arten von Datensätzen gespeichert, je eine pro Tabellen.
Es gibt zum Beispiel Girokonten oder Depots bei Banken, Auftragspositionen in Warenwirtschaftssystemen, Personen, Telefonnummern, …
Zu jeder Art gibt es einen fachlichen Schlüssel, bei einer Rechnungs-Unterposition zum Beispiel Rechnungs-, Auftrags-, Positions- und Unter-Nummer. Also ein recht langer Schlüssel.
Für eine Adresse ist der fachliche Schlüssel die Kunden-Nummer, die Adress-Art und eine laufende Nummer (es kann mehrere Adressen einer Art, zum Beispiel „Ferienwohnung“ geben), eigentlich auch zu lang.
Insbesondere, wenn noch ein Unterscheidungsmerkmal für die zeitlich Abfolge benötigt wird.
Eine Adresse kann nicht einfach in der Datenbanktabelle überschrieben werden, die bisherige Adresse muss ja nachvollziehbar sein. Also ist die zeitliche Abgrenzung der bisherigen und der neuen Adresse über den Zeitpunkt „gültig-von“ oder „erfasst-am“ notwendig.
Ein Kunde kann viele Adressen haben, seinen Wohnsitz, ein Postfach, eine oder mehrere Urlaubsadressen, … Letztlich sind Telefon, Fax, Email-Adresse, … auch Adressen.
Und einige Kunden können identische Adressen haben.
Ein Mann als KFZ-Halter ist Kunde einer Versicherung.
Dieser Mann ist auch als Ehemann in dem „Gemeinschaftskunden“ Ehepaar vorhanden. Das Ehepaar hat zum Beispiel eine Hausratversicherung. Damit ist der Mann, also eine Person, als zwei unterschiedliche Kunden gespeichert.
Trotzdem hat er als Person sicher nur einen Wohnsitz, und wenn das Ehepaar umzieht, dann zieht nicht nur der Mit-Vertragspartner der Hausratversicherung, sondern auch der KFZ-Halter um.
Ist es nicht sinnvoll, den Wohnsitz oder die Telefonnummer nur einmal zu speichern und über eine Zuordnung-Tabelle beiden Kunden zuzuordnen, aber unter welcher Kundennummer? Wir haben hier m:n Beziehungen, also mehrere Kunden zu mehreren Adressen.
Was passiert, wenn der Kunde seine bisherige Ferienwohnung zur Hauptadresse macht? Ist es sinnvoll, die Adress-Art in der eindeutigen Schlüssel für die technischen Zugriffe aufzunehmen?
In Datenbanken gibt es meist den Primär-Schlüssel, den die Datenbank auch für interne Zwecke nutzt. Ein Feld des Primär-Schlüssels kann nicht geändert werden, es ist eine Kopie des bisherigen Datensatzes mit dem geänderten Feld zu erstellen und der bisherige Datensatz zu löschen. Mittlerweile kann auch DB2 ein Update auf ein Primär-Schlüsselfeld durchführen, also „Update Kunden-Tabelle Set Kunden-Nummer = ‚4712‘ Where Kunden-Nummer = ‚4711‘, aber intern führt die Datenbank sicher keinen Update, sondern das oben genante Verfahren durch. Ein Update auf ein Feld des Primär-Schlüssels sollte, da hier auch historische Zusammenhänge verloren gehen, immer die Ausnahme sein, die im Rahmen von Reorganisationen stattfinden, aber nie in einem Anwendungsprogramm verwendet werden.
Am Besten ist es, wenn jeder Datensatz seine eindeutige ID erhält, dazu kommt dann noch für die Eindeutigkeit im zeitlichen Ablauf der Zeitpunkt (Timestamp), an dem die Version eingefügt wurde.
Die hierarchischen Verbindung zu seinem übergeordneten Datensatz geschieht über die ID des übergeordneten Datensatzes. Bei einer Auftrags-Unterposition, also der ID, nicht der Nummer, der Auftragsposition plus die Unternummer.
Wird die Position innerhalb des Auftrags oder zu einem anderen Auftrag verschoben, ist nur deren Positionsnummer anzupassen, die Unterpositionen verweisen ja nur auf die ID und wandern mit.
Der Timestamp der Auftragsposition gehört nicht in die Unterposition, denn ändert sich die Position, erhält also einen neuen Timestamp, bleibt die Unterposition ja weiterhin die Unterposition.
Bei einer Adresse ist eine Verknüpfungstabelle besser, also Kunden-ID, Adress-ID, Adress-Art. Zusätzlich dann als technische Hilfe noch der Adress-Typ (Post-Adresse, Postfach, Telefon, …) angegeben werden, dann kann sofort auf die richtige Datenbank-Tabelle zugegriffen werden.
Jeder Datenbank-Tabelle in einem kommerziellen Umfeld besitzt die Felder
– “erstellt-durch“ für den Anwender, der diese Satz eingefügt oder geändert hat. Bei einer Änderung durch ein Batch-Programm gibt es keinen Anwender, hier kann der Name des Programms als „technischer User“ genutzt werden. Das Feld wird immer automatisch gesetzt.
– „erfasst-am“, wann (Timestamp) der Datensatz in dieser Version in die Datenbank eingestellt wurde.
Einige Satztype werden nie ungültig, ein Beispiel sind Journal-Sätze in einer Buchhaltung.
Bei einige Satztypen reicht ein Kennzeichen, dass sie ab „jetzt“ ungültig sind.
Andere Satztypen haben einen Gültigkeitszeitraum, also „gültig-von“ und „gültig-bis“, zum Beispiel Adressen.
Die beiden letzten Typen benötigen also ein Feld
– „ersetzt-am“, wann dieser Datensatz ungültig wurde, wobei bei Adressen dieses Feld angibt, wann sie durch eine anderer Version mit einen anderen „gültig-bis“ ersetzt wurden.
Ob dieses Feld „ersetzt-am“ per Update-Anweisung gesetzt wird, oder in eine „Ersetzungs-Tabelle“ ausgelagert wird, um nur mit dem Insert-Statement arbeiten zu können, ist eine taktische Entscheidung.
Und jetzt das alles entscheide Feld:
– „Satz-ID“, dieses ist eine grosse Zahl (64-Bit Integer), ein technischer Begriff, kein Anwender sollte sie je sehen, sie wird auch über keine Schnittstelle an andere Systeme geliefert oder von diesen empfangen und nur über sie werden innerhalb der Datenbank Verknüpfungen aufgebaut.
Der Primär-Schlüssel jeder Datenbank-Tabelle ist „Satz-ID“ plus „erfasst-am“.
Da es nur einen gültigen Datensatz geben kann, die ersetzten Datensätze ausgeblendet sind, kann bei Abfragen das „erfasst-am“ wegfallen.
Die Satz-ID wird durch den ID-Geber, einem zentralen Prozess, eindeutig im Gesamt-System vergeben. Eine Satz-ID kann also immer nur einmal und innerhalb einer Datenbank-Tabelle vorkommen.
Was bringt dieses Vorgehen:
Zum einen lassen sich Relationen sehr einfach aufbauen.
Ersetzte Datensätze sind natürlich auszublenden.
Statt der Relation über viele fachliche Schlüsselfelder, ich habe in Projekten Tabellen mit einem Dutzend fachlichen Schlüsseln gesehen (Skontro trennende Merkmale), basieren alle Relationen nur auf der Satz-ID.
Ein weiterer Vorteil ist das Zusammenspiel von verschiedenen Umgebungen.
Ein Unternehmen, das Software entwickelt und nutzt, hat zumindest folgende Umgebungen:
– Entwicklung
– Test
– Schulung
– Produktion
Einige meiner Kunden hatten noch weitere Umgebungen, Vor-Entwicklung (hier wurden zentrale Komponenten entwickelt), statt einer Entwicklungs-Umgebung je eine für das nächste und das übernächste Release, Entwicklungs-Umgebungen pro Team, mehrere Test-Stufen, eine Schulungs-Umgebung mit dem produktiven Release, eine Schulungs-Umgebung mit dem nächsten Release, eine Umgebung für Pilot-Kunden, …
Wie die Software, also die Sourcen und die Lademodule verteilt sind, ist ein Thema, auf das ich an anderer Stelle eingehen möchte.
Jede Umgebung hat ihren eigenen Datenbestand, also ihre eigene Datenbank.
Zu jeder Test- und Produktions-Umgebung gibt je eine Wartungs-Umgebung.
Hier werden notwendige Bugfixes entwickelt. Zu Test dient ein eigener Datenbestand. Zwischen der Produktion und deren Wartungsumgebung sollte auch noch ein Test-Umgebung vorhanden sein.
Die Schulungsumgebung ist ein besonderer Datenbestand, hier ist eine überschaubare Menge an didaktisch aufbereiten, künstlichen Daten vorhanden, alle Personen sind „erfunden“, die Provisionssätze maximal die Hälfte der minimalen Provision, die ein Vertriebsmitarbeiter erhält, …
Und von der Schulungsumgebung existiert ein Backup, dass vor jeder Schulung aufgespielt wird.
Wichtig ist der Zusammenhang zwischen einer Produktions- beziehungsweise Test-Umgebungen, jeweils mit ihre Wartung–Umgebungen.
In der Produktion ist testen verboten!
Entwickler-Tests, also die ersten Versuche des Entwicklers, die noch einiges durcheinander bringen können, sollten auch nicht die Umgebung für die Tester ausser Gefecht setzen.
Manchmal wird beim Entwickeln ein direkter schreibender Zugriff auf die Daten benötigt, ein SQL Script lässt sich direkt schneller austesten, und diese Rechte werden auch in einer Test-Umgebung aus guten Gründen nicht gewährt.
Datenkonstellationen, die zu Problemen führen, müssen also schnell und unkompliziert in die Umgebung, auf die ein Entwickler direkt zugreifen kann, kopiert werden.
Dabei Namen, Anschriften und anderer personenbezogene Daten zu anomysieren ist selbstverständlich. Dabei darf natürlich kein Zusammenhang für den Testfall zerstört werden. Bei einer KFZ Haftpflichtversicherung und einem Problem bei der Betragsberechnung könnte der Wechsel der Daten einer 50 jährigen Beamtin gegen die eines 19 jährigen männlich Mauerlehrling nicht den benötigten Testfall ergeben, oder bei einer Hausratversicherung der „Umzug“ aus der Flussniederung auf einen Hügel.
Aber das eigentliche Problem sind die Daten, die schon innerhalb der Testumgebung vorhanden sind. Tritt der Fehler in der Produktion beim Kunden 4711 auf und gibt es schon einen Kunden 4711 in der Testumgebung, was tun?
Für alle fachlichen Schüssel neue Werte, die bisher noch nicht in der Testumgebung genutzt werden, suchen und umschlüsseln? Geht es um triviale Datenkonstellation (der Kunde und seine Telefonnummer), funktioniert es einfach, aber solche Daten sind schneller händisch aufgebaut. Was ist aber bei Verträgen mit mehreren Personen in verschiedenen Rollen, die unterschiedliche relevante Merkmale haben? Ist die Ursache für die Probleme beim Testen dann ein Fehler in der Software, oder lag es an einem Fehler beim Umschlüsseln?
Ist es besser, aus der Test-Datenbank alle Daten zu den fachlichen Schlüssel, die zu laden sind, zu löschen? Und nach 5 Minuten stehen die Kollegen, deren aktuelle Testfälle gerade gelöscht wurden, vor dem Schreibtisch.
Es gibt aber eine Lösung!
Wir arbeiten ja mit Satz-IDs als Primärschlüssel und über diese sind alle Verknüpfungen hergestellt.
Und jede Umgebung hat ihren eigenen ID-Generator und für jede Umgebung einen eindeutigen Wertebereich für die IDs.
Der Wertbereich von 0 bis 1 Milliarde (2 hoch 30) ist für Fremdschlüssel, die von den Administratoren per Script angelegt werden, vorgesehen.
Dann kommt der Bereich für die produktive Umgebung, von (2 hoch 30) + 1 bis 2 hoch 63, den Rest, also (2 hoch 63) + 1 bis (2 hoch 64) – 1 werden gerecht auf die verschiedenen Entwicklungs- und Test-Umgebungen verteilt, wobei je „freier“ eine Umgebung ist, desto höher ist der Wertebereich. Gibt es eine Satz-ID mit einem höheren Wert wie der Maximalwert lauf ID-Geber, wurden wohl (zurechtgebogene) Daten aus einer Entwicklungs- in eine Test-Umgebung kopiert. Diese Kontrollmöglichkeit ist für die interne Revision wichtig.
Finden wir eine Satz-ID aus dem Wertebereich der produktiven Umgebung in einer Test- oder Entwicklungs-Umgebung, stammt diese Satz-ID ursprünglich aus der Produktion.
Wird jeder Datensatz mit dieser Satz-ID gelöscht und dann durch die Daten aus der Produktion ersetzt, wird kein mühsam von einem Kollegen erstellten Testfall zerstören.
Also können die Daten aus der produktiven Umgebung entladen, die verwendeten Satz-IDs ermitteln, diese in der Zielumgebung löschen (sind sie nicht vorhanden, umso besser!). Dann können die Daten geladen werden und sofort kann das Problem aus der Produktion nachgestellt werden.
Sollte ein fachlicher Schlüssel, zum Bespiel die Kunden-Nummer 4711, schon vorhanden sein, können die Daten nicht geladen werden, es gibt ja einen Index auf die Kunden-Nummer, und diese ist unique definiert. Aber die Kunden-Nummer steht ja nur in einem Datensatz, und wird sie dort auf 4712 geändert, hat es keine weiteren Auswirkungen auf den Testfall.
Ach ja, darum eigentlich Kunden-Nummer, und das noch als numerischer Wert? Interessiert der Mittelwert der Kunden-Nummer? Solange ein Feld nicht für Additionen genutzt wird, kann es als Zeichenkette definiert werden, und dann kann „4711“ einfach in „P4711“ geändert werden.
In der Vergangenheit traf ich Kollegen, meist kurz nach der Ausbildung, die alle Tabellen „richtig“ normalisieren wollten. Und in der Adress-Tabelle, die von ihnen erstellt wurde, gab es ein Feld „Postleitzahl“, dabei ist doch die Postleitzahl recht einfach aus Ort (Köln), Strasse (Aachener Strasse) und Hausnummer (256) oder der Postfach-Nummer herzuleiten, gehört also nicht in eine normalisierte Datenbank-Tabelle, oder?
Normalisieren soll den Aufwand bei einem Daten-Update minimieren, aber bitte nicht auf Kosten des Aufwands bei Abfragen. Aus diesem Grund packe ich gerne die Postleitzahl (der Lieferadresse zum Zeitpunkt des Auftrags) in Auftrags-Unterpositionen, denn die Verteilung von Artikel, Preis und Postleitzahl ist eine sehr häufige Auswertung.