mySql - Beim Update alte Werte archivieren

[Ozymandias]

Hallo Community, ich hab da mal ein Problem.

Ich habe eine mySql Datenbank, in der es eine große Tabelle mit zahlreichen Spalten gibt (ca. 40). Die Werte in den Spalten sind über ein PHP Skript änderbar und es soll bei jeder Änderung festgehalten werden, wer wann welche Werte geändert hat und wie die Werte vorher waren, um ggf. die Datensätze wieder auf einen vorherigen Stand zu bringen.

Hab schon lange gegoogelt, aber ich bin noch zu keinem vernünftigen Ergebnis gekommen. Ein zusätzliches Problem ist, dass zu dieser Tabelle im Laufe der Zeit weitere Spalten kommen könnten... :upps

Mein erster Ansatz war, einfach eine zweite Tabelle mit den gleichen Spalten und zusätzlich je einer Spalte für eine UserID und einen Timestamp zu erststellen und dort alles zu speichern. Das Problem dabei ist, dass meist nur ein Wert geändert wird, und trotzdem ein kompletter Datensatz neu angelegt wird. Wie sieht es da beim Speicherverbrauch aus, wenn nur die Werte gesichert werden, die sich geändert haben und der Rest NULL bleibt?

Der zweite Ansatz war, eine Tabelle für die Änderungen anzulegen, die so aussieht: ID, Datensatz_ID, Spalte, zeitpunkt_der_Änderung, user_id, Wert

Also festhalten, bei welchem Datensatz welche Spalte wann von welchem User geändert wurde und welchen Wert sie hatte. Natürlich gibt es da das Problem, daß die Ursprungstabelle natürlich unterschiedliche Datentypen in den Spalten hat. Also müsste ich alle Änderungen als TEXT speichern und beim Rückgängig machen dann wieder in den jeweiligen Typ umwandeln. Ginge schon, ist aber ziemlich unschön.

Der dritte Ansatz ist, für jede Spalte eine eigene Tabelle für die Änderungen anzulegen. Bei 40 Spalten gibt das 40 Tabellen und beim Rollback habe ich richtig Spaß, alle Änderungen abzufragen...

Ich halte dennoch den Ansatz 3 für am geeignetsten für dieses Problem. Ich muss aber auch zugeben, dass ich ein ziemlicher Anfänger bin, was das Designen von Datenbanken betrifft...

Was meint ihr, welchen Ansatz sollte ich nehmen, oder gibt es noch eine andere Möglichkeit, auf die ich noch nicht gekommen bin? Alle Tipps sind herzlich willkommen.

[lupo49]

Alle Abfragen protokollieren und dann das Log filtern?

MySQL :: MySQL 5.1 Referenzhandbuch :: 5.12 Die MySQL-Logdateien

Du könntest auch ein Trigger erstellen, der beim Löschen/Ändern der Quelltabelle die alten Datensätze zuvor in die Sicherungstabelle packt.

http://stackoverflow.com/questions/779230/using-mysql-triggers-to-log-all-table-changes-to-a-secondary-table

[Ozymandias]

Eine Lösung mit Log Dateien möchte ich nicht unbedingt anstreben, zumal ich wahrscheinlich, wenn die Sache fertig ist, keinen Zugriff auf diese Dateien habe. Oder kann man die mit select, show oder sonst was auslesen?

Das mit dem Trigger mache ich genau in dieser Form schon. Also die bisherige Lösung sieht so aus, dass ich wirklich für jede änderbare Spalte meiner Ursprungstabelle eine Tabelle anlege, in der ich die Änderungen festhalte mit UserID, Datum, ID des Datensatzes und dem alten Wert der Spalte. Der jeweilige Eintrag in diese Tabellen wird bei jedem Update automatisch von einem Trigger erstellt. Funktionieren tut es, ich halte es nur nicht für allzu elegant...

[flashpixx]

Der zweite Ansatz war, eine Tabelle für die Änderungen anzulegen, die so aussieht: ID, Datensatz_ID, Spalte, zeitpunkt_der_Änderung, user_id, Wert

Also festhalten, bei welchem Datensatz welche Spalte wann von welchem User geändert wurde und welchen Wert sie hatte. Natürlich gibt es da das Problem, daß die Ursprungstabelle natürlich unterschiedliche Datentypen in den Spalten hat. Also müsste ich alle Änderungen als TEXT speichern und beim Rückgängig machen dann wieder in den jeweiligen Typ umwandeln. Ginge schon, ist aber ziemlich unschön.

Ich denke, das wird aber die beste Lösung sein. Nur hier macht man nicht eine einfach Text2Datentyp Konvertierung. Das Fachwort dafür heißt "serialisieren". In Deine Log-Tabelle kommt ein CLOB / BLOB Feld hinein und Du schreibst eine Stored-Procedure, die für jeden Datentyp eine passende Konvertierung erzeugt. Der Vorteil dabei ist, dass selbst, wenn der Feldtyp in der Ursprungstabelle ändert, Du ohne Probleme zurück konvertieren kannst.

Unter PHP gibt es dafür die Methoden PHP: serialize - Manual bzw PHP: unserialize - Manual Du musst letztendlich nur so etwas direkt in der Datenbank implementieren.

[dr.dimitri]

Also die bisherige Lösung sieht so aus, dass ich wirklich für jede änderbare Spalte meiner Ursprungstabelle eine Tabelle anlege, in der ich die Änderungen festhalte mit UserID, Datum, ID des Datensatzes und dem alten Wert der Spalte. Der jeweilige Eintrag in diese Tabellen wird bei jedem Update automatisch von einem Trigger erstellt. Funktionieren tut es, ich halte es nur nicht für allzu elegant...

Es wird elegant, wenn Du Dir für jede Tabelle anhand der mysql Systemtabellen eine Historisierungstabelle generieren läßt (dazu muss man nur einmalig ein kleines Programm schreiben.

Diese Tabelle sieht genauso aus und hat zusätzlich noch die von Dir benötigten Spalten. Für den Tabellennamen kannst Du z.B. den Prefix HIST o.ä. verwenden.

Der zugehörige Trigger wird ebenfalls generiert.

Evtl. musst Du noch eine Tabelle anlegen, in der Du ablegst für welche Tabellen Dein Generierungsprogramm die Objekte erzeugen soll falls Du Tabellen hast, die nicht davon betroffen sind.

Damit hast Du eine vollautomatische Lösung, die Du bei jedem Build mitlaufen lassen kannst.

Den Ansatz alles in einen BLOB zu konvertieren halet ich für nicht so optimal, denn, mal vom Performance Aspekt abgesehen, entfernst Du ohne Not die bereits vorhandene Struktur und überführst diese in ein strukturloses Format.

Dim