Witam!
Jak zachować wszystkie UPDATE i DELETE w bazie? Czyli jak nie nadpisywać danych tylko mieć całą historie zmian.

Od początku.
Mam bazę o strukturze
id, pole1, pole2, data_aktualizacji, użytkownik
Dostęp do bazy ma kilku użytkowników, każdy może aktualizować i usuwać rekordy.
Chodzi mi o to ze chce mieć całą historie zmian w bazie a nie tylko aktualny rekord.
Oczywiście użytkownik ma widzieć tylko aktualny wpis lub jego brak jeśli jest usunięty, czyli dla użytkownika baza działa klasycznie, (usuwa i aktualizuje rekordy i widzi wynik), ale ja chce mieć wgląd w operacje na bazie.

Mam taki pomysł
dokładam pole id_update i ewentualnie pole status które informuje czy rekord jest usunięty i wychodzi coś takiego
id, id_update, status, pole1, pole2, data_aktualizacji, użytkownik
kluczem głównym będą 2 pola id i id_update
po aktualizacji rekordu nie robi UPDATE tylko INSERT nowego rekordu o tym samym id a id_update zwiększa się o 1
wtedy mam łatwy wgląd w historie aktualizacji
Przy DELETE to samo (czyli id_update zwiększa się o 1) tylko status zmienia się na usunięty

I tu moja prośba
Czy w ten sposób nie próbuje wyważyć otwartych drzwi. Może ten problem już dawno jest rozwiązany i sprawdzone jego działanie. Może ktoś ma inny (prostszy, szybszy, wypróbowany) sposób niż tu zaproponowałem? Może są gotowe funkcje w MySql, które to rozwiązują.
Proszę o sugestie.

Dzięki

Jeśli to ma być w tej samej tabeli cały czas to ja widzę tylko jedną możliwość i jest ona z Twoją zbieżna.
Rozszerzasz dotychczasowe rekordy o 2 kolumny: modified i child
Obie mają domyślnie wartość 0
W razie DELETE i UPDATE kolumna modified ustawia się na 1.
W razie UPDATE tworzysz nowy rekord insertem jako kopia aktualizowanego. Kolumnę child starego ustawiasz jako id rekordu nowego. Modified nowego ustawiasz na 0 i dajesz obojgu rekordom current. Przy DELETE też datę ustawiasz na current.

Jakie korzyści? Twoja WIDOCZNA baza to zawsze te rekordy, które mają modified ustawione na 0, a te z modified równym 1 to historia zmian. Całość jest niewidoczna z poziomu usera, jeśli nie porówna id.
Pole data_aktualizacji przechowuje moment działania, więc jest idealna przy zestawieniach, raportach.
Pamiętaj by co jakiś czas przenosić rekordy usunięte i zmodyfikowane z bazy (te z modified = 1), bo z czasem zacznie ich być więcej niż tych z 0 i całość zacznie mocno zwalniać.