Najlepsze praktyki dotyczące historii / tabel czasowych?

Załóżmy, że mam obiekt z pewnymi polami, które chcę śledzić historią, i pewnymi polami, których nie chcę śledzić historią. Z perspektywy normalizacji następujący schemat jest w porządku:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

gdzie MyObjectHistory zawiera śledzone pola dla wszystkich z wyjątkiem najnowszej wersji. Lub, czy wszystkie śledzone pola powinny znajdować się w jednej tabeli, a wszystkie wersje, w tym najnowsze, powinny znajdować się w tej tabeli, jak w:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

Ze względów praktycznych związanych z dostępem do danych należy użyć struktury z pierwszej opcji, ale zamiast tego zachować wszystkie wersje wartości śledzonych kolumn, w tym bieżącą wersję, w tabeli historii.

Powodem tego jest to, że ogólnie, gdy chcesz spojrzeć na historię, chcesz uwzględnić obecną i wszystkie poprzednie wersje. Kiedy nie chcesz patrzeć na historię, chcesz ją usunąć z drogi. W wielu przypadkach oznacza to posunięcie się aż do segregowania historii w oddzielny schemat lub bazę danych. Nawet jeśli utrzymasz swoją historię w tym samym schemacie, co bieżące dane, wszelkie zapytania dotyczące danych historycznych (w tym bieżących wartości) będą znacznie bardziej złożone, ponieważ muszą zasadniczo łączyć dwa źródła.

Wolałbym pierwszą wersję, ponieważ prawdopodobnie rzadko trzeba zobaczyć historię, ale często trzeba zobaczyć bieżącą wartość. Tabelę historii należy zapełnić wyzwalaczem, więc nie musisz się martwić, że dane zsynchronizują się. Załóżmy, że masz milion rekordów w MyObject, a następnie masz 10 000 000 rekordów w MyObjectHistory. Czy naprawdę chcesz dołączyć do tabeli z tyloma rekordami, aby uzyskać bieżącą wartość?

Teraz, jeśli będziesz musiał przeszukiwać historię tak często lub częściej niż bieżąca wartość, wtedy druga struktura będzie działać. (A jeśli zamierzasz wyświetlać wartość z określonej daty, miałbym w niej pole początkowe i końcowe, aby uprościć zapytania).

BTW Dodałbym pole daty do tabeli historii, aby móc określić, w jakiej kolejności nastąpiły zmiany. Nie można polegać na tożsamościach w przypadku czasowego porządku. PLus, jeśli pojawi się pytanie o wartość previosu i kiedy się zmieni, będziesz musiał knwo. Mogę również wprowadzić wartości dla aplikacji, z której pochodzi zmiana (jeśli masz wiele aplikacji) i / lub osoby, która dokonała zmiany.

Istnieje kilka ważnych powodów dla # 1. Pierwszy z nich to problem dotyczący wielkości, na który wskazuje HLGEM, ale są też inne ważne.

Zazwyczaj ścieżka audytu będzie się rozwijać wraz z upływem czasu. Może się zdarzyć, że zechcesz śledzić użytkowników bazy danych, czas zmiany itp. Wymagania dotyczące ścieżki audytu i główna tabela prawdopodobnie zmienią się z czasem nieco niezależnie. Wreszcie prawdopodobnie zechcesz wyczyścić dane ścieżki audytu po pewnym czasie niezależnie iw pełni oddzielnej tabeli.

Oczywiście mogą zdarzyć się przypadki, w których chcesz je w pełni scalić (tak jak w przypadku stawek podatkowych w LedgerSMB), ponieważ dane historyczne mogą być wykorzystane do bieżących obliczeń, a liczba rekordów prawdopodobnie będzie stosunkowo niewielka.

Sugeruję jednak, że przechowywanie obiektów w takich tabelach rzadko prowadzi do dobrych, znormalizowanych projektów. Z mojego doświadczenia wynika, że ​​naprawdę potrzebna jest pewna enkapsulacja między dobrą znormalizowaną pamięcią masową a modelem obiektów aplikacji.