Änderungen verfolgen bzw Revisionssystem?

[Stoke]

Hallo,

ich bin im Zuge der Erstellung eines Datenbankmodells auf ein Problem gestoßen, das etwas kompliziert zu sein scheint. Ich hoffe ihr könnt mir hierbei behilflich sein

Konkret geht es um die Verfolgung von Änderungen in Entitäten: Zeigt zum Beispiel Entität A zum Zeitpunk x auf Entität B (<fk>), dann möchte ich dass mir eine entsprechende Query die mir das karthesische Produkt der beiden Tabellen zurückgibt, die Entität B zum Zeitpunkt x liefert. Und zwar unabhängig davon ob seit x Änderungen an B durchgeführt wurden.

Das hat jetzt bestimmt niemand verstanden, deshalb ein vereinfachtes Beispiel
Nehmen wir an es gibt eine Tabelle "User" mit unter anderem dem Feld "Geburtsdatum" und eine Tabelle "Projekte" mit einem Foreign Key auf den User der das Projekt erstellt hat. Nun erstellt ein User ein Projekt. Irgendwann ändert der User sein Geburtsdatum. Ich möchte aber immer das Geburtsdatum wissen, das zu dem Zeitpunkt als das Projekt erstellt wurde aktuell war.
Ich hoffe so ist mein Problem nun verständlich.

Mir fällt jetzt kein guter Lösungsweg ein. Immer eine Kopie des Users mitzuspeichern wäre keine gute Idee glaube ich. In meinem konkreten Fall gibt es viele Werte die sich ändern können und einige Tabellen wo dies auftreten könnte.
Ich bin sicher nicht der erste mit diesem Problem, gibt es hierzu vielleicht sogar schon Best Practices?
Ein Design Pattern das in diese Richtung geht ist mir leider nicht bekannt.
Ich glaube ein Revisionssystem wäre hier die optimale Lösung, gibt es hier bereits praxiserprobte Implementierungen?

Bin über Tipps aller Art dankbar
Vielen Dank!

 

[Kalito]

Sowas ist meines Wissen nach nicht realisierbar. Es sei den du speicherst in einer extra Tabelle alle alten Werte mit dem Timestamp der erstellung, was aber zu redudanz führt und zu Speicherplatzverbrauch. Entweder verzichtest du auf solche Abfragen oder verhinderst die Änderung

 

[port29]

Hi,

ich stand vor einer Weile vor einem ähnlichen Problem. Es gibt für dein/mein Problem keine "perfekte" Lösung. Ich habe mich dazu entschieden, auf eine Lösung mit relationalen Datenbanken zu verzichten und stattdessen auf ein Document Based Datenbank-Modell zu gehen.

Ich habe dir jetzt mal ein Auszug aus der Datenbank zu meinem Projekt hochgeladen:
http://nopaste.info/b0f6fa1c1b.html

Wie du siehst, wird unter dem User Dokument bei jeder Änderung eine Revision gespeichert. Damit sind alle Änderungen nachvollziehbar.

Realisiert ist das ganze mit Mongodb und Rails+Mongoid. Die Revisionen werden übrigens automatisch gespeichert, ohne dass ich da etwas coden musste.

 

[Stoke]

Danke für die Antworten

Die Idee mit MongoDB ist wohl eine Überlegung wert, das muss ich mir mal ansehen. Ich befürchte aber dass ganze Revisionen zu speichern den Speicherbedarf unnötig in die Höhe treibt.

Ich habe mir inzwischen mal folgendes überlegt, das ist jetz allerdings mehr oder weniger ungetesteter Pseudocode, aber ich glaube man versteht was gemeint ist. Ein Auszug aus dem Modell mit den relevanten Tabellen befindet sich im Anhang.

Die Abfrage welche Person bei welchem Contest teilgenommen hat wie üblich:

SELECT * FROM contest c 
	JOIN participation pt ON (c.contestId = pt.contestId) 
	JOIN person p ON (p.personId = pt.personId)

Danach müsste für jede Person eine weitere Abfrage gemacht werden:

SELECT DISTINCT attribute, value 
	FROM personchangelog 
	WHERE personId = " + personId + " AND validUntil > " + contestDate + " 
	ORDER BY validUntil ASC

Dies liefert das Attribut/Wert Paar für das Attribut das am nähesten beim Datum des Contests liegt sofern es ein solches gibt.
Danach muss ich nur noch die vorhandenen Werte ersetzen.

Selbstverständlich wird vorausgesetzt dass die Log Tabelle mit den Änderungen entsprechend gepflegt wird. Jedes mal wenn ein Wert bei einer Person geändert wird kommt da der alte Wert samt Datum rein.

Diese Lösung sollte eigentlich Funktionieren (Flüchtigkeitsfehler vorbehalten... ^^), die Performance dürfte allerdings in den Keller sacken.

Mir fällt jetzt aber kein Weg ein, das ganze in eine einzige Query zu packen. Hat da jemand einen besseren Vorschlag?
Danke sehr

 

[port29]

Bei deiner Lösung mit dem Log gibt es zwei Schwächen. Zunächst einmal wäre da der Log. Denn mit jedem neuen Eintrag verlangsamt sich die Suche in deinem Log. Ich kenne jetzt die Dimensionen deines Projektes nicht, aber ab so ca. einer Million Einträge merkt man schon Einbußen bei der Performance.

Dann gibt es noch das Problem, dass immer alle Revisionen deines Ergebnisses durchgehen musst, um einen Datensatz zu einer bestimmten Zeit zu erhalten.

Für mich war die Speicherplatzbelegung kein Problem. Denn dadurch wird die Performance der Datenbank nicht langsamer, da ich eh nur über indizierte Werte auf die Datensätze zugreife. Deshalb sind Tabellen der Größenordnung von 20GB kein Problem. Und Festplattenspeicher ist heute eh genug vorhanden.

 

[Alex F.]

Welches Datenbanksystem möchtest Du denn nutzen? Der Microsoft SQL Server hat schon ein paar Features die für dieses Problem ausgelegt sind - Change Data Capture
http://www.simple-talk.com/sql/lear...change-data-capture-(cdc)-in-sql-server-2008/
oder auch SQL Server Auditing : http://msdn.microsoft.com/en-us/library/cc280526.aspx

Die Queries auf Änderungen sind damit mit Sicherheit nicht die Schnellsten, allerdings kann man hier eventuell dann gefilterte Sichten verwenden die dann auch bei mehreren Tausend Datensätzen noch performant laufen.

Eine weitere Möglichkeit wäre bei benötigten Tabellen eine Kopie anzulegen und diese bei Änderungen über einen Trigger zu aktualisieren.

Grüße Alex