Die Hälfte der SQL DML Befehle sind unnötig?
Klingt erst einmal komisch, aber …
Bei kommerziellen Anwendungen müssen Datenänderungen nachvollziehbar sein, einige Daten dürfen nicht mehr geändert werden, andere sollten nie mehr geändert werden, nur bei einer Gruppe von Daten könnte es die Notwendigkeit von Updates geben.
Die Datentype, Satzarten, die in Datenbanken gespeichert werden, lassen sich in vier Gruppen zusammenfassen:
– Fremdschlüssel
– Journale
– Stammdaten
– Salden
Stammdaten sind zum Bespiel: Kunden-Stamm, Artikel-Stamm, Konten-Stamm. Wertpapier-Stammdaten, …
Journale sind die Bewegungsdaten, Bestandsveränderungen, Aufträge, Ein- und Auszahlungen, Umbuchungen, …
Salden sind die aktuellen Bestände, die sich aus den Journalen ergeben. Zu einem Konto gibt es in eine Datenbanktabelle die Stammdaten, in einer anderen den Saldo. In der Salden-Tabelle sind zwei Felder vorhanden, die „Konto-Nummer“, zu der es Stammdaten gibt sowie einen Betrag (Währung, Stück, …).
Um Manipulationen oder technische Probleme aufdecken zu können, kann es noch drei Felder geben, das Datum der aktuellen Buchung, die Anzahl der Buchungen in der Periode und der Buchungsnummer, die zu diesem Saldo geführt hat.
Bei Stammdaten und Journalen gibt es Feld-Inhalte, die einem Wertebereich zugeordnet sind, zum Beispiel die Konto-Art (Bilanz / GuV), der Anredeschlüssel, …, der Wertebereich ist in jeweils einer Fremdschlüssel-Tabelle hinterlegt.
Jetzt zu den technischen Aspekten.
Fremdschlüssel-Werte
Zum Beispiel die Ausprägungen für „Männlich/Weiblich“, Anredeschlüssel, Konto-Typ, Mehrwertsteuer-Schlüssel, Kunden-Systematik, …, enthalten, dürfen erst gelöscht werden, wenn der letzte Datensatz, der auf einen dieser Schlüssel verweist, aus der Datenbank verschwunden ist.
Änderungen sind denkbar, aber nur bezüglich des Gültigkeitszeitraums.
Als Anfang 2007 der neue Mehrwertsteuersatz von 19% gültig wurde, muss vorher dieser Schlüssel mit der Gültigkeit 01.01.2007 bis 31.12.9999 eingefügt werden. Bei dem bis dahin gültigen Mehrwertsteuersatz 16% musste die Gültigkeit von bisher 01.04.1998 bis 31.12.9999 auf dem 31.12.2006 begrenzt werden, hier ist also ein Update auf das Feld „gültig-bis“ notwendig.
Wann kann ein Schlüssel gelöscht werden? Eigentlich nie, Männer und Frauen wird es geben, solange die Menschheit existiert, nur bei zum Beispiel dem Mehrwertsteuer-Schlüssel wäre eine Löschung denkbar.
Nehmen wir den Mehrwertsteuer-Schlüssel. Er wird in jeder Auftragspositionen zu finden sein. Damit könnte er entfallen, wenn die letzte Auftragsposition mit diesem Mehrwertsteuersatz gelöscht wurde. Die gesetzliche Aufbewahrungsfrist beträgt 10 Jahren, also am 01.01.2017 könnten die letzten Rechnungen …
Falsch! Wenn die Lieferung noch 2006 erfolgte, die Rechnung aber erst 2007 geschrieben wurde, konnte noch der alten Mehrwertsteuersatz verwendet werden. Bei Gutschriften wird auch die Mehrwertsteuer des ursprünglichen Geschäfts genutzt, damit könnte auch Jahre später noch 16% Mehrwertsteuer bei einer Gutschrift genutzt werden.
Für eine Kundenhistorie können die Daten auch deutlich länger gespeichert werden. Wenn heute ein Kunde anruft und ein Ersatzteil für einen 1980 gekaufte Maschine benötigt, ist es doch sinnvoll, heute noch die Daten von damals vorliegen zu haben. Die Umsatzsteuer (damals 13%) braucht man sicher nicht mehr, aber die Artikelnummer bzw. die Artikelbeschreibung ist wichtig.
Also, bei den Tabellen gibt es nie ein Delete. Der Aufwand, alle Daten zu prüfen, ob der Schlüssel doch noch genutzt wird, oder vielleicht sogar den bisherigen Schlüssel durch einen Dummy-Eintrag zu ersetzen, rechtfertig nicht die Einsparung von wenige Bytes in der Datenbank.
Bei einigen Typen mit Gültigkeitszeitraum kann dieser per Update begrenzt werden.
Und diese Fremdschlüssel-Tabellen werden nicht über ein Dialog-Programm gepflegt. In alle Umgebungen (Entwicklung, Test, Schulung, Produktion, …) müssen ja die gleichen Ausprägungen vorliegen, die Tabellen müssen identisch sein, also schreibt ein Administrator ein Script (auf dem Mainframe einen „SPUFI“), der dann nach Freigabe ausgeführt wird.
Journale
Also Bewegungsdaten in einer Buchhaltung, dürfen nie geändert werden.
Mit Buchhaltung ist nicht nur die Finanzbuchhaltung im engeren Sinn gemeint, sondern z.B. auch Barauszahlungen an einem Geldautomaten, Einträge in ein Fahrtenbuch, Material-Entnahmescheine, …
Falls eine Änderung notwendig ist, ist eine Storno-Buchung, also eine Generalumkehr, zu erfassen. Ein Update oder Delete ist hier gesetzlich verboten!
Es gibt gesetzliche Aufbewahrungsfristen, bei vielen Daten beträgt sie 6 oder auch 10 Jahre, Buchungen aus 2004 konnten also Anfang 2015 gelöscht werden. Solche Löschungen passieren in besonderen Reorganisation nach erfolgten Jahresabschluss. Dabei wird meist kein „DELETE FROM tabelle WHERE datum …“ genutzt, sondern die Datenbank wird entladen, die noch notwendigen Sätze kopiert und diese dann in eine neue Tabelle geladen.
Aber bei bestimmten Journalen ist eine längere Aufbewahrungsfrist sinnvoll. Als Beispiel kann der Fall des Sparbuch dienen, das nach 38 Jahren ohne jede Buchung vom Erben gefunden und bei der Bank vorgelegt wurde. Die Bank meinte, sie haben vor über 30 Jahren das Sparbuch für ungültig erklärt, habe aber nur noch Unterlagen über eine Sammelbuchung auf einem Mikrofilm. Da die Bank nicht nachweisen konnte, dass das Geld damals wegen des Verschwinden des Sparbuches auf ein anderes Sparbuch übertragen wurde, durfte sie entsprechend eines Gerichtsurteils das „Guthaben“ zuzüglich Zinsen auszahlen.
Salden
Der eine Datentyp, bei dem ein Update sinnvoll sein könnte.
Bei jedem Insert in ein Journal ändern sich zumindest zwei Salden.
Wieso „zumindest zwei“? Was passiert bei „Kunden zahlt mit Skonto“? Hier ändern sich vier Salden (Bank, Forderungen, Skonto-Aufwand und Umsatzsteuer).
Was macht die Datenbank bei einem Update, sie muss ja in der Lage sein, bei einem Rollback, also dem Verwerfen der Transaktion, den alten Zustand wieder herzustellen? Sie fügt einen neuen Datensatz in die Tabelle ein und setzt das interne Löschkennzeichen für den Datensatz mit dem alten Saldo.
Wichtig ist, den Saldo-Datensatz möglichst kurz zu halten, also nicht die Stammdaten des Kontos dort mit unterzubringen. Die Datenbank muss ja den kompletten Satz kopieren (die meisten Datenbanken arbeiten auch beim Rollback satzweise). Pro Buchung zumindest 2 Salden-Änderungen notwendig, und pro Änderung für die Datenbank intern ein Kopiervorgang, und dann sind 2000 Bytes inklusive Stammdaten doch deutlich mehr wie 40 Bytes ohne Stammdaten.
Der Saldo eines Kontos lässt sich auch aus dem Start-Bestand (Tagesanfangsbestand, zugleich der Tagesendbestand des Vortages) und den Bewegungen, die ja im Journal verzeichnet sind, wieder herstellen. Es könnte also sinnvoll sein, die Salden im Hauptspeicher zu halten und zeitverzögert zu schreiben.
Falls der genaue Bestand / Saldo eines Kontos zu jeden Zeitpunkt in der Vergangenheit ermittelbar sein oder aus anderen Gründen (gleitender Durchschnitt nach HGB) gestaffelt werden muss, muss bei einem rückwirkenden Storno auf den zu dem Zeitpunkt des Stornos aktuellen Saldo aufgesetzt werden. In der Praxis kenne ich das Vorgehen, sich die Salden vom Monats- und Quartals-Anfang zu merken, um nicht immer am Jahresanfang aufsetzen zu müssen. Wenn am 21. Mai ein Storno für den 15. April gebucht wird, wird mit dem Quartal-Anfangs-Saldo (1. April) begonnen und alle Buchungen an dem 1. April, inklusive des Stornos am 21. April, erneut gestaffelt.
Falls statt des Updates auf die Salden-Tabelle immer ein Insert mit dem Buchungszeitpunkt (Buchungsdatum) durchgeführt wird, können alle Salden nach dem Zeitpunkt des Stornos gelöscht werden, der dann jüngste Saldo ist ja der Saldo zum Zeitpunkt des Stornos, und beginnend mit dem Storno werden alle Buchungen neu gestaffelt und die neuen Salden mit dem Zeitpunkt der Buchung eingefügt.
Stammdaten
Also zum Beispiel die Adressen von Kunden. Hier gelten auch die gesetzlichen Aufbewahrungsfristen, und die Änderungen müssen nachvollziehbar sein.
Zieht ein Kunden um, teil uns also seine Adressänderung mit, ist die Gültigkeit der bisherigen Adresse zu begrenzen und die neue Adresse einzufügen.
Wurde eine Adresse 2015 ungültig, kann sie Anfang 2026 aus der Datenbank gelöscht werden.
Wir benötigen also zusätzlich zum fachlichen Schlüssel (Kundennummer plus Art der Adresse) oder zum technischen Schlüssel (hier bietet sich eine eindeutige Satz-ID, also eine 64 Bit Zahl, an), etwas, was die notwendigen Datensätze im Zeitablauf eindeutig macht. Optimal ist hier der Timestamp, an dem der Datensatz in die Datenbank aufgenommen wurde, also das Feld „geändert-am“ / „eingefügt-am“.
Falls wir das „ersetzt-am“, also den Zeitpunkt der fachlichen Änderung, die diesen Satz ersetzt, in eine eigene Tabelle, bestehend aus technischen Schlüssel (ID + „geändert-am“) sowie „ersetzt-am“, auslagern, reicht ein Insert auf diese Tabelle. Die Abfragen laufen dann über einen View, der die Datensätze, deren Schlüssel auch in der „Ersetzt-Tabelle“ enthalten ist, ausblendet.
Nette Kunden informieren schon im Vorfeld eines Umzuges über die neue Adresse.
Was ist fachlich zu tun?
Der bisherige Datensatz muss zum Umzugsdatum abgegrenzt und ein neuer Datensatz eingefügt werden. Dabei sollte aber die Information, wer wann diesen Datensatz zuletzt geändert hat, nicht verloren gehen.
Wie ist die technische Umsetzung?
Ermittlung des aktuellen Timestamps und merken in einer Variable.
Insert in die „Ersetzt-Tabelle“ mit dem technischen Schlüssel des bisherigen Satzes und „ersetzt-am“ mit dem gemerkten Timestamp. Bekommen wir hier einen SQL-Fehler „Doppelter Schlüssel“ hat zwischenzeitlich ein anderer Anwender den Datensatz geändert, damit haben wir also sehr einfach ein Optimistisches Sperren implementiert.
Insert in die Adressen-Tabelle des bisherigen Satzes, nur ist „geändert-am“ der gemerkte Timestamp und „gültig-bis“ das Umzugsdatum.
Addieren von 1 (also einer Nano-Sekunde) zum gemerkten Timestamp.
Insert der neuen Adresse, „geändert-am“ mit dem Timestamp plus 1, wegen der Eindeutigkeit im Zeitablauf, „gültig-ab“ mit dem Umzugsdatum und „gültig-bis“ dem 31.12.9999 füllen.
Fertig!
Drei Insert Statements, optimistisches Sperren sauber implementiert, alle Änderungen nachvollziehbar, besser geht es nicht, und das ohne Update oder Delete.
Nun wird der Fall komplexer, aber bei Bausparkassen, Gebäudeversicherungen und ähnlichem sicher Tagesgeschäft:
Der Kunde baut ein neues Haus und will Anfang 2015 einziehen, die bisherige Wohnung wurde zu Ende Januar 2015 gekündigt. Am 10. November 2015 teilte der Kunde die Adressänderung für den 15.01.2015 mit.
Also läuft der oben beschriebene Prozess ab. Wir haben anschliessend drei Datensätze
„Mietwohnung“, „gültig-bis“ 31.12.9999, „ersetzt-am“ 10.11.2014
„Mietwohnung“, „gültig-bis“ 15.01.2015, „geändert-am“ 10.11.2014, nicht ersetzt
„Haus“, „gültig-ab“ 15.01.2015, „geändert-am“ 10.11.2014 plus 1 Nano-Sekunde, nicht ersetzt
Kurz vor Weihnachten (22.12.2014) teilt der Kunde mit, dass sich der Bezug des Hauses um 2 Monate (16.03.2015) verzögert, er die Mietwohnung nun am 30.01.2015 räumt und dafür zwischenzeitlich bei seinen Eltern unterkommt.
Was ist zu tun?
Ändern des „gültig-bis“ der „Mietwohnung“ auf den 30.01.2015.
Ändern des „gültig-ab“ des „Haus“ auf dem 16.03.2015.
Einfügen eines Satzes „Eltern“ mit „gültig-ab“ 30.01.2015 und „gültig-bis“ 16.03.2015.
Wir haben anschliessend folgende 5 Datensätze
„Mietwohnung“, „gültig-bis“ 31.12.9999, „ersetzt-am“ 10.11.2014
„Mietwohnung“, „gültig-bis“ 15.01.2015, „geändert-am“ 10.11.2014, „ersetzt-am“ 22.12.2014
„Mietwohnung“, „gültig-bis“ 30.01.2015, „geändert-am“ 22.12.2014, nicht ersetzt
„Eltern“, „gültig-ab“ 31.01.2015, „gültig-bis“ 15.03.2015, „geändert-am“ 22.12.2014 plus 1 Nano-Sekunde
„Haus“, „gültig-ab“ 15.01.2015, „geändert-am“ 10.11.2014 plus 1 Nano-Sekunde, „ersetzt-am“ 22.12.2014 plus 2 Nano-Sekunden
„Haus“, „gültig-ab“ 16.03.2015, „geändert-am“ 22.12.2014 plus 2 Nano-Sekunden,. Nicht ersetzt.
Die ersetzten Datensätze können, müssen aber nicht, im Rahmen von Reorganisationen in einer Historien-Tabelle ausgelagert werden. Gehen wir von dem Falls aus, dass die Auslagerung nicht stattfindet, so bleiben die Daten bis Ende der Aufbewahrungsfrist in der Datenbank.
Gehen wir weiter davon aus, dass das Haus termingerecht bezogen werden konnte und der Kunde dort bis ans Ende aller Tage lebt, so ist der weitere Verlauf:
Anfang 2025 können die Sätze
„Mietwohnung“, „gültig-bis“ 31.12.9999, „ersetzt-am“ 10.11.2014
„Mietwohnung“, „gültig-bis“ 15.01.2015, „geändert-am“ 10.11.2014, „ersetzt-am“ 22.12.2014
„Haus“, „gültig-ab“ 15.01.2015, „geändert-am“ 10.11.2014 plus 1 Nano-Sekunde, „ersetzt-am“ 22.12.2014 plus 2 Nano-Sekunden
da 2014 ersetzt, gelöscht werden.
Anfang 2026 können die Sätze
„Mietwohnung“, „gültig-bis“ 30.01.2015, „geändert-am“ 22.12.2014, nicht ersetzt
„Eltern“, „gültig-ab“ 30.01.2015, „gültig-bis“ 16.03.2015, „geändert-am“ 22.12.2014 plus 1 Nano-Sekunde, nicht ersetzt
da 2015 ungültig geworden, gelöscht werden.
Bei der fachlichen und der technischen Gültigkeit („gültig-von“ – „gültig-bis“, „geändert-am“ – „ersetzt-am“) darf es keine Überschneidungen geben. In einem Projekt war das „ersetzt-am“ bzw. das „gültig-bis“ als „gültig-bis-einschliesslich“ definiert, was zu etwas Arbeit führte, da vom „gültig-ab“ des ersetzenden Datensatzes immer eine Nano-Sekunde abzuziehen war. Besser ist eine Definition „gültig-bis-ausschliesslich“. Dann ist der Timestamp, ab dem der neue Datensatz gültig bis identisch mit dem Zeitraum, ab dem der alte Datensatz ungültig ist.
In der SQL Where-Bedingung also
„gueltig_von <= Such-Termin and gueltig_bis > Such-Termin“
stattfindet
„gueltig_von <= Such-Termin and gueltig_bis >= Such-Termin“