Tabelle mit Zeitscheiben sauber aufbauen

[Dae1812]

Hallo zusammen,

ich habe ein Projekt, in dem ich von Objekten verschiedene Zeitscheiben habe.
Als Beispiel habe ich ein logisches System, das wird geplant aufgebaut, ab und an mal geändert und irgendwann abgebaut.
Für jede Änderung gibt es eine neue Version, diese hat ein Erstellungsdatum und ein Datum, ab dem Sie gültig ist, da man auch Versionen vorsorglich anlegen kann.

Im aktuellen Modell sind eine UUID und eine Versionsnummer Primärschlüssel, von der Versionsnummer möchte ich als Datenfeld allerdings weg, da das immer Probleme macht, wenn man eine Version zwischen zwei Versionen möchte. Also hatte ich im ersten Entwurf UUID und Startdatum als Primärschlüssel, so kann man einfach Versionen zwischen zwei Startdaten anlegen.

Allerdings versuche ich kombinierte Primärschlüssel zu vermeiden.
Deswegen hatte ich über ein Modell nachgedacht, wo jeder Satz einen technischen Primärschlüssel hat und zusätzlich einen fachlichen, der dann beim gleichen logischen System immer gleich ist, so kann man über den fachlichen Schlüssel gruppieren.

Z.B. haben wir auch eine Entität für Institute. Diese ändern sich auch über die Zeit, Anhand der Anzahl der Arbeitsplätze und auch der Name kann sich ändern.
Ein logisches System ist in einem Institut, wie verknüpfe ich jetzt die beiden sinnvoll?

Wenn ich das Modell mit dem technischen und fachlichen Schlüssel durchziehe, dann muss ich ja eigentlich den technischen Schlüssel des Institut beim logischen System hinterlegen. Dann ist aber am loschen System fest eine bestimmte Version des Instituts hinterlegt.
Macht das so Sinn? Ich male und schreibe hier alles voll, komm aber nicht auf eine sinnvolle Lösung mit echten Schlüsseln und Abhängigkeiten zu arbeiten.

Die Datenbank ist eine Oracle, ich glaube 19, hab es nicht mehr richtig im Kopf. Eigentlich programmiere ich das Ganze in Java.

Danke für eure Ideen und Unterstützung.

 

[dabadepdu]

Klingt nach einer n:m beziehung zwischen Versionierung und abhängigen Objekten.
Wenn im Grunde schon die Datenänderung eines Objektes eine Versionsänderung bedeuten kann, dann kann man sich tot versionieren.

Bist Du sicher, dass es kombinierte Primärschlüssel sind?
Ein Primärschlüssel ist idR ein technischer, sollte es zumindest. Und dass es darüber hinaus in der Realität viele fachliche Schlüssel gibt, ist auch nichts Besonderes. Eher nur die Frage, ob es für das Modell wichtig ist, das irgendein Feld auch ein fachlicher Schlüssel ist. Meistens reicht es ja, wenn man sowas in der Anwendung suchen und aussteuern kann. Umgekehrt ist es viel schlimmer, wenn ein fachlicher Schlüssel als Primärschlüssel verwendet wird, dann in Echt seinen Aufbau verändert und einen riesen Haufen Probleme aufwirft, das beim technischen Primärschlüssel aufzufangen.

Verschiedene Fremdschlüssel oder mehrteilige sind in einem halbwegs komplexen Modell auch nicht außergewöhnlich.

Alternativ:
Was ist denn, wenn Du über alle Tabellen / Felder, die Anteil an der Version haben, einen Hash machst und das mit einer fortlaufenden Zahl in einer Tabelle ablegst?
Der Hash hat eigentlich keine Bedeutung, außer dass er durch Veränderung einen Versionswechsel anzeigt. Die nächste Version wird einfach hochgezählt. Kann man so oft aufrufen wie man will, immer wenn der Hash sich ändert, ist es eine neue Version.
Das deckt so einfach glaub ich nicht alle Deine Probleme ab, aber wäre eine andere Herangehensweise.

Wenn Du tatsächlich in Deinen Objekten irgendwo eine Änderungshistorie mitführst, könnte man die dort integrieren.

Und aus Entwicklersicht klingt Dein Problem auch irgendwie so, als würde man Deine Versionen am liebsten mit Git verwalten und dort nach Belieben Branches anlegen.

 

[Dae1812]

Naja ist halt Quasi die Geräteakte und die möchte man aufschlagen und den Lebenslauf anschauen und nicht einen Zettel mit dem aktuellen Stand erhalten und muss dann im Keller die alten Zettel holen gehen. Also bleiben alle Versionen in der Haupttabelle. Das jede Änderung eine neue Version erzeugt ist leider mittlerweile existenziell, da wir mittlerweile sehr viele schreibende Schnittstellen haben und mittlerweile gerne mal gestritten wird, wer denn einen Wert gesetzt hat. Ob jede Version später in der Historie wichtig ist, war auch ein Streipunkt. Evtl. muss ein Event für eine neue Version ausgelöst werden und reine Änderungen in einer Version müssen dann irgendwie über Trigger anders getrackt werden.

Also die erste Idee war definitiv ein Kombinierter Primärschlüssel, da ich für dasselbe Objekt immer dieselbe UUID vergeben habe und nur über den Timestamp unterschieden wurde. Davon will ich ja eigentlich weg.

Über eine N:M Tabelle habe ich auch nachgedacht, aber dann habe ich immer fest eine Version Objekt A mit einer Version Objekt B verknüpft, d.h. bei einer neuen Version des einen Objektes muss ich auch dran denken eine neue Verknüpfung an zu legen.
Ich denke zwar dass das Java für mich macht, aber so weit bin ich aktuell noch nicht, um das mal aus zu probieren.

Den Ansatz mit dem Hash musst du mir nochmal genauer erklären, was genau würde der mir dann ersetzen?

 

[dabadepdu]

Der Hash wäre nur ein Indikator über alle relevanten Felder in allen Tabellen. Ändert sich der Hash, ist es eine neue Version.
Aber wenn Du einen Lebenslauf brauchst, also eine saubere Historie, reicht das allein sicher nicht.
Vor allem, wenn jetzt schon über die bestehenden Einträge gestritten wird (die dann offenbar nicht durchgängig protokolliert werden)

Wenn Du also nicht in den Keller möchtest, wäre die Frage, in welchem Ausmaß und welcher Form die Änderungshistorie existiert bzw. einsehbar sein soll.

Da sehe ich 3 Möglichkeiten, die u.U. parallel betrieben werden:
Ein Lifecycle Feld je Tabelle
Eine Key-Value Liste mit alten und neuen Werten (je Tabelle)
versionierte Datensätze

Partiell ist das vielleicht bereits vorhanden, mglw auch mit unterschiedlichen Ansätzen.

Und ob das jetzt mit zusammengesetzten PK gemacht wird oder nicht, ist m.E. relativ egal. Gehe von der fachlichen Anforderung aus und modelliere das. Änderungen können bequem per Trigger notiert werden, z.B. in einer versionsabhängigen Tabelle.

Gibt es hinreichend viele Fälle, wo direkt mit einer bestimmten Version gearbeitet werden muss, sind es wahrscheinlich versionierte Datensätze.
Geht es vorwiegend um eine (mögliche) Einsichtnahme, eher die Änderungshistorie. Damit hast Du rein auf DB Ebene jedenfalls erstmal eine saubere Grundlage. Es wird alles protokolliert, was relevant ist oder werden könnte. Das wäre dann eher schon eine Hygienefrage. Die Trigger dafür könnte man sehr wahrscheinlich generieren. Das Prinzip ist ja immer gleich. Ich weiß nicht, was Oracle mittlerweile kann, hatte ich schon lange nicht mehr. Aber in anderen Systemen käme man sogar mit nur einer Trigger Funktion aus.
Die Versionierung selbst und die Darstellung kämen darüber.
Lifecycle Werte und Änderungshistorie sind theoretisch etwas redundant. Da muss man genau darauf achten, was wofür gemacht wird / welche Implementierung welcher Aufgabe dient.

Wichtig ist dabei sicher auch die Frage, ob Versionen automatisch entstehen können oder "komponiert" / deklariert werden.

Wir hatten sowas ähnliches mal in einer Oracle DB, so eine Art CRM. Es gab eine große Änderungstabelle für alle wichtigen Merkmale, das betraf Stammdaten, Buchungen, Anschreiben. Es wurde aber nicht versioniert, viele Objekte hatten ursprünglich nur lifecycles. Am Ende möchte dann doch immer jemand wissen, warum in einem bestimmten Feld nun dieser oder jener Wert steht.

 

[Dae1812]

Ja, das Thema hatten wir vor drei Jahren noch nicht so. Aber seitdem haben wir eine neue Schnittstelle, die recht aggressiv neue ätze anlegen und alte verändern darf. Und da bin ich permanent am nachlaufen, weil die nach jedem neuen Release Schrott liefern. Und dann geht der Tanz los, wer hat jetzt da in das Feld einen falschen Wert geschrieben.
Dann gibt es viele Differenzen bei Soll und Ist, was hat der Kunde bestellt, was wurde installiert.
Und dann die Versionierung: gibt es eine "echte" Änderung, also hat das System mehr Speicher ab einem Zeitpunkt, eine neue IP, oder ändert sich der Zustand?
Oder ist es eine Korrektur, d.h. der eingetragene Speicher oder die IP waren falsch eingetragen.
Mich persönlich interessieren nur die echten Änderungen, aber wenn eine Schnittstelle das Recht hat Daten zu "korrigieren", dann muss eben auch protokolliert werden, wer hat wann etwas geändert und wie war der Zustand vorher.

Es handelt sich um ein Migrationsprojekt, also die Datenbank wandert von SQL Server in Oracle und die Anwendung von lokalem VB.NET Client in eine Webanwendung.
Die aktuelle Datenstruktur ist richtig gruselig, die Tabelle für die Laufdaten sieht so aus

Spalte	Datentyp	Eigenschaft
UUID	Uniqueidentifier	PK
VERSION	int	PK
FELDINDEX	int	PK
WERT	String
Changedate	timestamp

Mal ab davon, dass ich für einen Datensatz einer Version teilweise 60 Zeilen laden muss, werden auch alle Daten als String Konvertiert und abgelegt. Das führt z.B. dazu, dass Datumsangaben in verschiedenen Formaten abgelegt sind, weil sich das Format ab und zu mal geändert hat.

Die Historytabelle mussten wir letztens bereinigen, weil dort bei jeder Änderung die Zeile vorher und die Zeile nachher abgelegt worden ist, das wiederum auf 60 Felder (weil die ja sinnvoller Weise immer alle geschrieben werden und nicht nur das mit der Änderung) macht 180 Operationen für eine Änderung. Da hatten wir auf einmal einige Timeouts, von Pagelocks ganz zu schweigen.

Deswegen will ich das Datenmodell jetzt Horizontal machen, braucht jemand ein Spalte mehr, soll er nen Change einstellen und warten, bis der Zyklus durch ist.

Wegen den Zeitscheiben werde ich dann wohl Zwischentabellen machen müssen, über die ich mir noch ein wenig heute den Kopf zerbreche. Konnte seit 4:45 Uhr heute nicht mehr schlafen, weil mir das im Kopf rum geht.

 

[dabadepdu]

mmh, also auf jeden Fall saubere Datentypen nutzen! (Oder notfalls ausschließlich mit fixen Konvertierungen / Formatmodellen arbeiten)
Fieldindex ist natürlich auch ziemlich gruselig, eher ein no-go.
Was Du auch überlegen kannst, die Änderungen als JSON oder sowas aufzunehmen. Also ein einziges Feld, das das Delta beinhaltet.
Dann kann man überlegen, welche Formen von Änderungen "automatisch" durchgehen und welche eine Freigabe/"redaktionelle Nachbearbeitung" brauchen.
Die horizontale Variante da bin ich skeptisch. Nicht sehr pflegeleicht und skalierbar und irgendwie redundant, besonders wenn eh schon irgendwo Datensätze komplett versioniert werden (Was eher die nette Variante von horizontal wäre).
Und was MSSQL angeht und Page Locks, willkommen im Club. Oder anders:
Tritt (Page)lock auch bei Oracle auf? (Pagelock ist für mich eher ein Begriff aus der MS SQL Welt)
Ich hab jedenfalls bei MSSQL damit schlechte Erfahrungen gemacht / machen lassen.

"Wegen den Zeitscheiben werde ich dann wohl Zwischentabellen machen müssen"

Was meinst Du genau? N:M ?

 

[Dae1812]

Ich hab es jetzt mal grafisch umgesetzt. Muss ja mal irgendwann meinen Kram dokumentieren.

Am Beispiel Logisches System.

• Ein logisches System hat einen Eintrag in der Tabelle Logisches System.
  Aktuell ist da erstmal nur der PK drin, weil mir kein Feld eingefallen ist, dass über den kompletten Lebenszyklus gleich bleibt
• Dieser Eintrag ist mit mindestens einer Zeitscheibe verbunden, quasi dem Ur-Satz
• Jetzt kann ich so viele Zeitscheiben an das logische System hängen, wie ich will, der Über-Pk ändert sich nie und alle Verknüpfungen bleiben stabil.

Genauso verhält es sich mit den Servern

• Eine Über Entität, die den PK und die Serverart halten
• Viele Zeitscheiben, die auf den einen Server verweisen

Und bei den Institutsdaten.

Bei Laufwerken muss ich das Grün noch weg machen.
Eine Zeitscheibe hat 1 bis n Laufwerke.
Wenn die sich nicht verändern dann könnte man die an weiteren Zeitscheiben wiederverwenden.
Ändert sich das Laufwerk, dann gibt es einen neuen Satz, der an der neuen Zeitscheibe hängt.

IPs ändern sich nie, die werden einfach nur immer an irgendeine Zeitscheibe gehängt und diese dürfen nicht überlappen.

Deine Idee mit dem JSON Gap ist garnicht mal blöd. So könnte man versionsinterne Änderungen dokumentieren und müsste dafür keine Zwischenversionen anlegen. Das könnte man sogar in eine, oder wenige History-Tabellen auslagern.

 

[dabadepdu]

• Ein logisches System ....
  Aktuell ist da erstmal nur der PK drin, weil mir kein Feld eingefallen ist, dass über den kompletten Lebenszyklus gleich bleibt
• ..
• Jetzt kann ich so viele Zeitscheiben an das logische System hängen, wie ich will, der Über-Pk ändert sich nie und alle Verknüpfungen bleiben stabil.

Das kommt mir etwas kurios vor. Du hast weiter oben schon sowas ähnliches geschrieben.
Es ist vollkommen ok, sogar empfohlen, einen technischen PK zu nutzen, um genau das zu erreichen, was Du im 3 Spiegelstrich ausführst. Das ist kein Trick oder Kniff oder Hack, so macht man das. PK (und FK) haben ausschließlich den Zweck, Referenzen zu definieren und zwar unabhängig vom fachlichen Inhalt, wie Surrogatschlüsseln oder sonstigen fachlichen Daten.
Deine Formulierung lässt befürchten, dass Du es später umändern / "verbessern" möchtest. Das wäre wahrscheinlich keine gute Idee.

In dem Bild habe ich jetzt noch keine Zeitscheiben gesehen.

 

[dabadepdu]

Mich persönlich interessieren nur die echten Änderungen, aber wenn eine Schnittstelle das Recht hat Daten zu "korrigieren", dann muss eben auch protokolliert werden, wer hat wann etwas geändert und wie war der Zustand vorher.

An der Stelle wäre die Frage, was ist echt, "korrekt" bzw. was ist immerhin erlaubt.
Hier würde ich nach Möglichkeit mit Constraints arbeiten, die im Zweifel ein Insert bzw. eine ganze Transaktion platzen lassen.
Ich habe mal als Erweiterung für Konfigurationsparameter eines Systems eine Funktion gebaut, die neben dem eigentlichen Konfigurationseintrag eine Expression mitführte, die den Konfigurationseintrag validiert hat. Im Prinzip eine Art Constraint, aber nur für einen spezifischen Datensatz gültig statt eine komplette Spalte. Enstanden ist aus eine losen KV Konfigurationstabelle, in die man ursprünglich (als Entwickler) beliebige Werte eintragen konnte, jenachdem wie weit die Routinen darunter entwickelt waren. Während manche Parameter einfach Grenzwerte waren (Datum, Min / Max Angaben usw. und tatsächlich "beliebig"), waren andere Werte eher Flags, die ein Algorithmus steuern. Über die Expressions konnte man dann satzweise die Konfigurationsparameter schützen, wenn nötig. Bewacht hat das dann ein Trigger, der die Expression verwendet hat. Dazu gehört natürlich, dass man eine Expression ändern muss, wenn die erlaubte Konfiguration sich ändert. Aber das ist dann bloß ein simples Daten Update auf der Expression, statt ein DDL für einen globalen Table Constraint.
Am Ende des Tages will man ja doch gut schlafen.

 

[Dae1812]

In dem Bild habe ich jetzt noch keine Zeitscheiben gesehen.

Mi Scusi, das habe ich in der unsichtbaren Legende gehabt. Jede Zeitscheiben Entität hat zusätzlich die Felder Startzeit, Createdate und Createuser. Ich möchte Bewusst auf die Ende-Zeit verzichten, weil das wieder erfordern würde, dass Versionen dazwischen schieben mehr Aufwand bedeutet.
Und je länger ich über meinen aktuellen Stand nachdenke, umso besser gefällt er mir und ich sehe wenig "Verbesserungsbedarf"

Ich weiß, was du mit "Korrekten Daten" meinst. Ein Hostname ist auf der Datenbank ein einfacher String, aber nur, weil da Bockwurst drin steht ist das noch lange kein Hostname. Das wird wohl im ersten Wurf in der Frontend-Logik passieren.
Die Migration der Daten von absoluter Schrott, ohne Typsicherheit hin zu relationalem Datenmodell mit einfacher Typsicherheit ist erstmal ein riesen Schritt und ich bin echt froh, wenn das funktioniert hat.

Ich muss mir noch überlegen wie ich sauber migriere, die Reihenfolge ist ja am Ende auch wichtig.

Was meinst du mit KV?

 

[dabadepdu]

Ich möchte Bewusst auf die Ende-Zeit verzichten

Man sollte vor allem auf Datenhaltung bzw. -Verwendung verzichten, deren Korrektheit man nicht verifizieren kann.
Vielleicht möchtest Du statt Endezeit einen Verweis auf den Nachfolger verwenden .. (weiß nicht ob es Sinn macht für den Anwendungsfall)

Versionen dazwischen schieben mehr Aufwand bedeutet

Was meinst Du damit? Wenn Du die Timestamps auf Werte setzt, die "zwischen" 2 anderen Zeitscheiben liegen, dann ist das mit der Definition der Beginn (und Endezeit) erledigt. Jenseits der Datenhaltung benötigst Du an dieser Stelle bswp. vielleicht eine Business Operation, die eine bestehende Zeitscheibe splittet. (Ich weiß nicht, ob es sich überlappen darf. Da Du kein Ende definierst, kann das ja bereits bei 2 Zeitscheiben geschehen)

Hostname .. nur, weil da Bockwurst drin steht ist das noch lange kein Hostname

Wen interessieren Hostnamen? Kann man protokollieren / aufnehmen, aber am Ende sind die Schall und Rauch. Falls der Hostname über DHCP definiert wird, kann man recht zuverlässig sagen, dass er mit einem DNS Namen übereinstimmt. Dann würde Redundanz wegfallen und das System robuster. Der DNS ist wiederum die Autorität, die entscheidet, welche IP dahinter liegt und die kann bei Bedarf variieren (Failover, ..). Also hier das gleiche, warum DNS, wenn auch IP gespeichert werden oder umgekehrt? Lieber nur DNS speichern bzw. verwenden, IP vielleicht protokollieren. Noch lieber eindeutige Servicenamen verwenden (und erfassen). Am Ende läuft der ganze Kram in irgendwelchen Kubernetes Nodes und IP usw. sind eh nicht mehr das, was sie mal waren.

KV für Key-Value Listen, einfach Parameter und Wert, in diesem Fall in einer Mandanten abhängigen Tabelle. Könnte ebenso ein Config File sein, aber da es dort um Parameter für den fachlichen Betrieb innerhalb des Mandanten ging und die Anwendungslogik in der DB lag, hab ich es eben über Config Parameter in einer Tabelle realisiert. Kann dann genauso bequem editiert werden, wie alles andere in Tabellen und ist unterbrechungsfrei wirksam.

 

[Dae1812]

Hi, das dazwischenschieben ist zwar selten, aber gängig Praxis bei uns. Man nehme einen Server und seinen Lifecycle. Jetzt weiß ich vielleicht heute schon, dass das System nächstes Jahr abgebaut wird, also gibt es eine neue Zeitscheibe für den 1.1.2023, an der sich der Status ändert auf "Abgebaut". Durch die Migration wird aber kurz vor Ende mehr Speicher benötigt, also brauche ich vor der Ende-Zeitscheibe noch eine neue mit mehr Speicher. Das kommt teilweise mehrmals vor dem Abbau vor.
Das mit dem "Vorgänger merken" erinnert mich stark an das Prinip einer Blockchain. Könnte man im Zweifeldfall verwenden, um die Suche zu beschleunigen.

Hostnamen sind hier bei uns ne halbe Objektbeschreibung, neben dem Institut, dem Betriebssystem und der Umgebung kann man teilweise sogar die Funktion des Servers nur aus dem Namen lesen. IPs sind so ne andere Sache, angeblich haben wir Systeme mit 13IPs (hab noch keins gesehen) aber 3 IPs sind keine Seltenheit. IPs sollten eindeutig sein, sind aber selbstverständlich wieder verwendbar.

Ja, die Config vom System hab ich bis auf ein paar Kleinigkeiten in der Datenbank gelagert. Hab sogar mittlerweile Mini-Tabellen für Configs angelegt, wie z.B. eine eigene Tabelle für den Log-Level, so kann ich den über die Redundanten Server hinweg einheitlich im laufenden Betrieb umstellen.
Config-Dateien lassen sich hier nur im Release ändern und erfordern meistens einen Server-Neustart. In der Datenbank bin ich da unabhängig.

 

[Dukel]

Das klingt für mich, dass Ihr eine CMDB aufbaut. Schonmal vorhandene Lösungen angeschaut?