Historia zmian rekordów

Mam tabelę nadrzędną oraz kilka podrzędnych, które po stronie kodu tworzą jakieś obiekty biznesowe z kolekcjami innych obiektów biznesowych etc.
Może przykład:

Zamówienie
Id
OfertaId
Cena

Kupujący
Id
ZamówienieId
Imię
Nazwisko
AdresId (kilku klientów może mieć ten sam adres, pewnie w 99% przypadków tak będzie)

Adresy
Id
Ulica
Kod
Miasto

DokumentyZamówienia
Id
ZamówienieId
Nazwa

Oświadczenia
ZamówienieId
OświadczenieId

Oczywiście w rzeczywistości każda tabela to jeszcze kilka kolumn, no jest kilka tabel więcej.
Zamówienie (na pewnym etapie) może zostać zmienione. Można dodać/usunąć kupujących, zmienić adresy, dodać dokumenty, zaznaczyć więcej oświadczeń, ...
Dla celów audytowych oraz na użytek klienta potrzeba zachować historię zmian i móc ją udostępnić (wyświetlić).

Jak widać tabel jest kilka, więc to trochę utrudnia trzymanie pełnej historii. Mam kilka pomysłów, ale jestem ciekaw czy macie jakieś ciekawe sugestie jak podejść do tego.
Wszystkie modyfikacje będą wykonywane przez kontrolowaną przeze mnie DAL (java), więc triggery nie są konieczne, raczej nawet wolałbym ich nie używać.
Zamówienie (czyli nasz główny/nadrzędny obiekt) może zmienić typ oferty oraz cenę. Bez problemu można dodać potrzebne kolumny techniczne.

generalnie są dwa wyjścia

1. w tabeli masz dodatkową kolumnę - aktualny/usuniety/stary/cokolwiek - która będzie mówiła czy dany rekord to wersja archiwalna czy bieżąca. Potrzebujesz też dwóch różnych ID - jedno normalne, unikalne ID w ramach całej tabeli i drugie nieunikalne ID w ramach tabeli ale unikalne dla rekordów, z aktualny = true i po tym drugim ID musiał byś łączyć dane i większość baz nie pozwoli Ci zakładać na takich indeksach kluczy obcych.
2. do każdej tabeli dodatkowa tabela o takiej samej strukturze i tam by lądowały wszystkie archiwalne rekordy.

Pierwsze jest trudniejsze w ogarnięciu i dodaje duży narzut danych w głównej tabeli, drugie siłą rzeczy dodaje tyle tabel ile chcesz trzymać archiwalnych danych ale nie masz dodatkowych danych w głównej tabeli (szybkość przeszukiwania), nie ma problemu z FK ale komplikuje się wyświetlanie historii.

Jest jeszcze trzecia opcja - jedna tabela z kolumnami ID, tabela, pole, wersja, wartość ale wyciąganie z niej danych i ogólnie zarządzanie nią to porażka

Jak miałem kiedyś taki problem to zastosowałem rozwiązanie 2. - łatwiejsze w wykonaniu i później w utrzymaniu. Wyświetlenie historii zmian też jest banalne, wystarczy select z tabeli archiwalnej.
Rozwiązanie 1. jest po prostu przekombinowane, głównie utrudnia wykonanie i obniża wydajność.

Dzięki. Myślałem że może jest jeszcze jakiś koncept na jaki nie wpadłem.
Postawie pewnie na rozwiązanie 2, bo łatwiej zarządzać danymi archiwalnymi, m.in. łatwiej przenieść tabele archiwalne do innej bazy niż bawić się w partycjonowanie, szczególnie że dane archiwalne raczej nie będą często potrzebne. I generalnie dla prostego programisty jest to łatwiejsze do ogarnięcia. Poza tym wyrób bazopodobny z jakim przyjdzie mi pracować trochę przytyka się na nieco bardziej skomplikowanych query.
Podejście nr 2 jest proste i wygodne kiedy struktura tabel jest prosta, ale przy bardziej skomplikowanej strukturze (kilka tabel zależnych) może przysporzyć trochę pracy, czyli jest pracochłonne, ale nieskomplikowane.
Wersji 3 używałem w jednym systemie, wartości pól były częściami xml'a, a DAL był linq2sql z przeciążonym SubmitChanges w DataContext i wg mnie sprawdzało się to całkiem nieźle. Oczywiście na ms sql 2005, więc zapytania wybierające pełną historię jakiegoś rekordu (czy nawet pola) śmigały wyśmienicie przy wsparciu dla xquery. Oczywiście jeśli trzeba było zaprezentować to dla audytu lub prześledzić jakąś sytuację, nie twierdzę żeby było to dostępne dla userów, którzy namiętnie wyświetlali sobie historie zmian.

massther napisał(a)

Podejście nr 2 jest proste i wygodne kiedy struktura tabel jest prosta, ale przy bardziej skomplikowanej strukturze (kilka tabel zależnych) może przysporzyć trochę pracy, czyli jest pracochłonne, ale nieskomplikowane.

nie zgodzę się - jeśli chodzi o zasilanie tabel historycznych to można to zrobić tak, że będą się one zasilały same - odpowiednie wyzwalacze i/lub procedury wbudowane i programista nawet nie musi wiedzieć, że coś takiego się dzieje. Co do pobierania tych danych to praktycznie poza podmianą nazw tabel w zapytaniu i ew. dodaniu warunku na konkretną wersję niczym się to zapytanie nie będzie różniło od zapytania pobierającego dane właściwe. Nie wiem jak to wygląda od strony orma czy czegoś podobnego ale podejrzewam, że jeszcze prościej

Ja to bym chyba nie robił tego ORMem tylko wyzwalaczami. Nazwy tabel chyba też mogą być takie same, niech tylko schemat będzie inny.