Hallo
ich benoetige in einer Datenbank fuer eine Teilmenge der Tabellen folgende Funktionalitaet:
- es sollen aeltere Staende eines Eintrags mit Gueltigkeitszeitpunkt gespeichert werden und geloeschte sollen als invalid gekennzeichnet, aber nicht geloescht werden
- es soll jederzeit moeglich sein, zu einem aelteren Stand unter Angabe des gewuenschten Zeitpunkts zurueckzukehren
- es soll moeglich sein, von Zeit zu Zeit “aufzuraeumen”, d.h. Eintraege aelter als n Stunden zu loeschen
- es gibt bereits definierte FrontEnds (Viewer) fuer die Datenbank, die nicht angefasst werden sollen
Was ich mir ueberlegt habe:
zunaechst einmal will ich den entsprechenden Tabellen zwei weitere Spalten hinzufuegen, eine, die den Gueltigkeitsbeginn (“t”) einer Zeile enthaelt, eine, die ein “valid”-Flag als boolean enthaelt, der primary key wuerde sich dann zusammensetzen aus dem alten primary key (“id”) und dem “t”.
dann muessten die Funktionen zur Bearbeitung von Eintraegen ueberschrieben werden:
ein INSERT muesste wie ein normales INSERT funktionieren, aber zusaetzlich automatisch den aktuellen Zeitpunkt in “t” eintragen und “valid” auf true setzen
ein UPDATE muesste die entsprechende Zeile/Zeilen unveraendert lassen und stattdessen fuer jede Zeile eine Kopie anlegen, die den nun aktuellen Zeitpunkt und den neuen Zustand der Zeile enthaelt
ein DELETE sollte eine neue Zeile anlegen als Kopie der bislang gueltigen, aber mit dem valid-Flag auf false
ein SELECT weiterhin sollte fuer jede “id” nur den Inhalt der neuesten Zeile liefern und Zeilen, bei denen valid false ist, nicht zurueckliefern
ein VIEW sollte immer die aktuellen Zeilen anzeigen, dh. wenn eine Zeile durch ein UPDATE sich aendert, sollte nicht mehr die alte nun nicht mehr gueltige Zeile im VIEW stehen, sondern die neue (bzw. nach DELETE sollte sie aus dem VIEW verschwinden)
dann sollte es eben eine Funktion “jump_to_time(t)” geben, die für alle Einträge eine neue Zeile mit der aktuellen Zeit und dem Inhalt, der zum Zeitpunkt t gültig war, anlegt (bzw. sofern dieser Eintrag dort noch nicht existierte, eine Zeile mit aktueller Zeit und valid false anlegt)
dann müsste es eine Funktion “kill_before(t)” geben, die bei allen Einträge jeweils den Gültigkeitszeitpunkt der Zeile, die zum Zeitpunkt t gültig war, auf t setzt und alle Zeilen dieses Eintrages, die älter sind, löscht
Nun meine Fragen:
- ist diese Problemstellung neu oder gibt es bereits Funktionensammlungen zu diesem Thema für postgresql?
- ist mein Konzept in Ordnung oder müsste man das Thema anders angehen?
- habe ich etwas übersehen, was ebenfalls für diesen Ansatz implementiert werden müsste? Wie sieht es zB. in Bezug auf Trigger oder foreign-key Handling aus? Würde ein FOREIGN KEY CASCADE ON UPDATE / DELETE automatisch funktionieren?
Viele Fragen, ich hoffe Ihr könnt mir helfen
Markus