Logowanie/śledzenie zmian, Jak efektywnie i uniwersalnie logować zmiany czynione przez użytkownik Opcje

[lukaswoj]

Witam

Załóżmy, że mamy aplikację, która operuje na 30 tabelach z czego 20 z nich służy do przechowywania danych, podlegających zmianom na skutek interakcji użytkowników z systemem.

Jedno z wymagań wobec aplikacji brzmi: "system umożliwia wgląd w historię zmian każdego bytu/obiektu".

Dla przykładu: mamy "harmonogram prac" powiązany z wieloma "zadaniami".

Wymaganie, które przytoczyłem w praktyce wymusza obsługę praktycznie każdej operacji typu UPDATE (załóżmy, że w aplikacji nie kasuje się danych, tylko je aktualizuje).

Zastanawiam się jaki może być najlepsze (elastyczne, szybkie, łatwe do oprogramowania) podejście do takiego tematu.

Tak ja do tej pory sobie to wydumałem:
Modyfikuję każdą tabelę wg poniższego schematu:
przed:

[SQL] pobierz, plaintext 
CREATE TABLE harmonogram_prac VALUES(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
...
);
[SQL] pobierz, plaintext 

po:

[SQL] pobierz, plaintext 
CREATE TABLE harmonogram_prac VALUES(
db_id INT UNSIGNED AUTO_INCREMENT PRIMAY_KEY
id INT UNSIGNED NOT NULL,
wersja INT UNSIGNED NOT NULL,
...
);
[SQL] pobierz, plaintext 

I teraz każdy UPDATE produktu o id = 200 to w rzeczywistości:
1. Stworzenie nowego rekordu dla "obecnej wersji"

[SQL] pobierz, plaintext 
$db_id = INSERT INTO harmonogram_prac SELECT FROM harmonogram_prac WHERE id = 200;
[SQL] pobierz, plaintext 

2. Nadanie konkretnego numeru wersji dla stworzonego rekordu

[SQL] pobierz, plaintext 
UPDATE harmonogram_prac SET version = ( SELECT MAX(version) + 1 FROM harmonogram_prac WHERE id = 200) WHERE db_id = $db_id;
[SQL] pobierz, plaintext 

3. Wykonanie pierwotnego polecenia UPDATE

Mamy w tej chwili możliwość pokazania kolejnych wersji naszego harmonogramu prac o id = 200.

W stosunku do tabeli "zadania"...
W zależności od wymagań można tworzyć "snapshot" powiązanych rekordów w momecie tworzenia nowej wersji harmonogramu, lub śledzić tylko zmiany wykonywane w obrębie samej tabeli "zadania" wg powyższego schematu.

Widzę oczywiście też pole do popisu w kwestii optymalizacji.

Można te kolejne wersje rekodów tworzyć w oddzielnej tabeli: każda pierwotnie stworzona tabela miałaby swój odpowiednik z przyrostkiem "_log". Przy odpowiednim podejściu i korzystając z np mySQL'owego "MERGE Storage Engine" - na poziomie aplikacji możemy założyć istnienie tylko jednej tabeli.

Samą logikę można by zaimplementować już na poziomie bazy danych w postaci procedur składowanych.


Oczywiście zakładam, że nie jestem pierwszą osobą na tym forum, która ma podobny "problem" i tym samym liczę na waszą pomoc drodzy forumowicze.
Zapraszam do dyskusji.

[Cienki1980]

Ja podczas projektowania systemu ERP wykorzystałem następujący system śledzenia zmian w bazie danych.

Jest stworzona tabela historia w której przechowywane są rekordy dotyczące zmian w dowolnej tabeli.
W obiekcie zawiadującym bazą danych przy wywołaniu operacji insert/update/delete jest tworzony rekord w tabeli historia gdzie jest zapisywana nazwa tabeli , id rekordu zmienianego oraz zmiany ( wartosc przed zmiana - wartosc po zmianie ). Dane były przechowywane w tabeli w postaci zserializowanej. Odpowiedni system pozwalał na wskazanie konkretnych tabel, których historia miała być rejestrowana.

System działa już w 3 firmach jako ERP i jakoś nikt nie narzeka.