Projekt bazy danych dla poprawek?

Myślę, że kluczowym pytaniem, które należy tutaj zadać, jest "kto / co będzie używał historii"?

Jeśli ma to być głównie do raportowania / historii czytelnej dla człowieka, wdrożyliśmy ten schemat w przeszłości...

Utwórz tabelę o nazwie 'AuditTrail' lub coś, co zawiera następujące pola...

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL

Możesz następnie dodać kolumnę' LastUpdatedByUserID ' do wszystkich tabel, która powinna być ustawiana za każdym razem, gdy wykonujesz aktualizację / wstawianie tabeli.

Możesz następnie dodać wyzwalacz do każdej tabeli, aby wychwycić każdą Insert / update i utworzyć wpis w tej tabeli dla każdego pola, które zostało zmienione. Ponieważ tabela jest również dostarczana z "LastUpdateByUserID" dla każdej aktualizacji / wstawki, można uzyskać dostęp do tej wartości w wyzwalaczu i używać jej podczas dodawania do tabeli audytu.

Używamy pola RecordID do przechowywania wartości pola klucza aktualizowanej tabeli. Jeśli jest to klucz zespolony, to po prostu robimy konkatenację ciągu Z " ~ " pomiędzy pola.

Jestem pewien, że ten system może mieć wady - dla mocno zaktualizowanych baz danych wydajność może być hit, ale dla mojej aplikacji internetowej, mamy o wiele więcej odczytów niż zapisów i wydaje się, że działa całkiem dobrze. Napisaliśmy nawet trochę VB.NET narzędzie do automatycznego zapisu wyzwalaczy na podstawie definicji tabeli.

Przydatne mogą być tabele Historii Artykuł w blogu programista baz danych - omawia niektóre z poruszonych tu kwestii i omawia przechowywanie delt.

Edit

W eseju tabele Historii autor ( Kenneth Downs) zaleca utrzymywanie tabeli historii składającej się z co najmniej siedmiu kolumn:

1. znacznik czasu zmiany,
2. Użytkownik, który dokonał zmiany,
3. token do identyfikacji rekordu, który był changed (gdzie historia jest utrzymywana oddzielnie od aktualnego stanu),
Czy zmiana była insert, update, or delete,
4. Stara wartość,
5. nowa wartość,
6. delta (dla zmian wartości liczbowych).

Kolumny, które nigdy się nie zmieniają, lub których historia nie jest wymagana, nie powinny być śledzone w tabeli historii, aby uniknąć wzdęcia. Przechowywanie delty dla wartości numerycznych może ułatwić późniejsze zapytania, nawet jeśli można je wyprowadzać od starych i nowych wartości.

Tabela historii musi być bezpieczna, a użytkownicy spoza systemu nie mogą wstawiać, aktualizować lub usuwać wierszy. Należy obsługiwać tylko okresowe oczyszczanie w celu zmniejszenia całkowitego rozmiaru (i jeśli jest to dozwolone w przypadku użycia).

Wdrożyliśmy rozwiązanie bardzo podobne do rozwiązania, które proponuje Chris Roberts, i to działa całkiem dobrze dla nas.

Jedyną różnicą jest to, że przechowujemy tylko nową wartość. Stara wartość jest jednak zapisana w poprzednim wierszu historii

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[NewValue] [varchar](5000) NULL

Powiedzmy, że masz tabelę z 20 kolumnami. W ten sposób musisz tylko zapisać dokładną kolumnę, która się zmieniła, zamiast przechowywać cały wiersz.

Jeśli musisz przechowywać historię, Utwórz tabelę cieni z tym samym schematem, co tabela, którą śledzisz, oraz kolumną "Data rewizji" i "typ rewizji" (np. "Usuń", "zaktualizuj"). Napisz (lub Wygeneruj - patrz poniżej) zestaw wyzwalaczy do wypełnienia tabeli audytu.

Dość proste jest stworzenie narzędzia, które odczyta systemowy słownik danych dla tabeli i wygeneruje skrypt, który utworzy tabelę cieni i zestaw wyzwalaczy do jej wypełnienia.

Nie próbuj używać XML dla dzięki temu pamięć XML jest o wiele mniej wydajna niż natywna pamięć tabel bazy danych, której używa ten typ wyzwalacza.

Unikaj Design 1; nie jest to zbyt poręczne, gdy będziesz musiał na przykład przywrócić stare wersje rekordów-automatycznie lub "ręcznie"za pomocą konsoli administrators.

Naprawdę nie widzę wad projektu 2. Myślę, że druga tabela historii powinna zawierać wszystkie kolumny obecne w pierwszej tabeli rekordów. Np. w mysql można łatwo utworzyć tabelę o tej samej strukturze co inna tabela (create table X like Y). I, kiedy masz zamiar zmienić strukturę rekordów table w Twojej aktywnej bazie danych musisz używać komend alter table - i nie ma większego wysiłku w uruchamianiu tych komend również dla Twojej tabeli historii.

Uwagi

• tabela rekordów zawiera tylko ostatnią rewizję;
• tabela historii zawiera wszystkie poprzednie wersje rekordów w tabeli rekordów;
• Klucz główny tabeli historii jest kluczem głównym tabeli rekordów z dodaną kolumną RevisionId;
• pomyśl o dodatkowych polach pomocniczych, takich jak ModifiedBy - the użytkownik, który stworzył konkretną wersję. Możesz również chcieć mieć pole DeletedBy do śledzenia, kto usunął daną wersję.
• zastanów się, co DateModified powinno oznaczać - albo oznacza to, gdzie ta konkretna wersja została stworzona, albo będzie oznaczać, kiedy ta konkretna wersja została zastąpiona inną. Pierwsze wymaga, aby pole znajdowało się w tabeli rekordów i wydaje się być bardziej intuicyjne na pierwszy rzut oka; drugie rozwiązanie wydaje się jednak bardziej praktyczne dla usuniętych rekordów (data, gdy ta konkretna zmiana została usunięta). Jeśli wybierzesz pierwsze rozwiązanie, prawdopodobnie będziesz potrzebował drugiego pola DateDeleted (oczywiście tylko wtedy, gdy go potrzebujesz). Zależy od Ciebie i co chcesz nagrać.

Operacje w projekcie 2 są bardzo trywialne:

• skopiuj rekord z tabeli rekordów do tabeli historii, nadaj mu nowy RevisionId( jeśli nie jest jeszcze obecny w tabeli rekordów), obsłuż DateModified (zależy od tego, jak go zinterpretujesz, patrz UWAGI powyżej)
• Kontynuuj normalną aktualizację rekordu w tabeli rekordów

• zrób dokładnie to samo, co w pierwszym kroku operacji Modify. W zależności od wybranej interpretacji należy odpowiednio dostosować datę/datę.

• weź najwyższą (czy jakąś konkretną?) przegląd z tabeli historii i skopiuj ją do tabeli rekordów

• wybierz z tabeli historii i tabeli rekordów
• pomyśl, czego dokładnie oczekujesz od tej operacji; prawdopodobnie określi ona, jakich informacji potrzebujesz z pól DateModified/DateDeleted (patrz UWAGI powyżej)

Jeśli wybierzesz Design 2, wszystkie potrzebne do tego polecenia SQL będą bardzo proste, a także konserwacja! Być może, będzie znacznie łatwiej jeśli użyjesz kolumn pomocniczych (RevisionId, DateModified) również w tabeli rekordów-aby zachować obie tabele mają dokładnie tę samą strukturę (Z wyjątkiem unikalnych klawiszy)! Pozwala to na proste polecenia SQL, które będą tolerowane na każdą zmianę struktury danych:

insert into EmployeeHistory select * from Employe where ID = XX

Nie zapomnij użyć transakcji!

Jeśli chodzi o skalowanie , To rozwiązanie jest bardzo wydajne, ponieważ nie przekształcamy żadnych danych z XML tam iz powrotem, tylko kopiujemy całe wiersze tabeli - bardzo proste zapytania, używając indeksów-bardzo wydajne!

Ramesh, byłem zaangażowany w rozwój systemu opartego na pierwszym podejściu.
Okazało się, że przechowywanie wersji w formacie XML prowadzi do ogromnego wzrostu bazy danych i znacznie spowalnia proces.
Moim podejściem byłoby mieć jedną tabelę na jednostkę:

Employee (Id, Name, ... , IsActive)  

Gdzie jest znakiem najnowszej wersji

Jeśli chcesz powiązać dodatkowe informacje z wersjami, możesz utworzyć osobną tabelę zawierające te informacje i łączą je z tabelami encji za pomocą Relacja PK\FK.

W ten sposób można przechowywać wszystkie wersje pracowników w jednej tabeli. Zalety tego podejścia:

• Prosta struktura bazy danych
• brak konfliktów, ponieważ tabela staje się tylko dołączana
• możesz cofnąć do poprzedniej wersji, zmieniając po prostu flagę IsActive
• nie ma potrzeby łączenia, aby uzyskać historię obiektu

Należy pamiętać, że klucz podstawowy powinien być nie unikalny.

The way that I ' ve seen this done in the past is have

Employees (EmployeeId, DateModified, < Employee Fields > , boolean isCurrent );

Nigdy nie "aktualizujesz" tej tabeli (z wyjątkiem zmiany poprawności isCurrent), wystarczy wstawić nowe wiersze. Dla każdego identyfikatora pracownika, tylko 1 wiersz może mieć isCurrent = = 1.

Złożoność utrzymywania tego może być ukryta przez widoki i wyzwalacze "zamiast" (w oracle zakładam podobne rzeczy inne RDBMS), można nawet przejść do zmaterializowanych widoków, jeśli tabele są zbyt duże i nie mogą być obsługiwane przez indeksy).

To metoda jest ok, ale możesz skończyć z kilkoma złożonymi zapytaniami.

Tworzy również bardzo mało narzutu dla bazy danych i aplikacji, szczególnie podczas wykonywania zapytań odczytu, co jest prawdopodobnie tym, co będziesz robić przez 99% czasu.

Byłoby również dość łatwe automatyczne tworzenie tabele historii i wyzwalacze do utrzymania (zakładając, że zostanie to wykonane za pomocą wyzwalaczy).

Jeśli rzeczywiście ścieżka audytu jest wszystkim, czego potrzebujesz, chciałbym pochylić się w kierunku rozwiązania tabeli audytu (wraz z denormalizowanymi kopiami ważnej kolumny na innych tabelach, np. UserName). Pamiętaj jednak, że gorzkie doświadczenie wskazuje, że pojedyncza tabela audytu będzie ogromnym wąskim gardłem na drodze; prawdopodobnie warto stworzyć indywidualne tabele audytu dla wszystkich skontrolowanych tabel.

Jeśli chcesz śledzić rzeczywiste historyczne (i / lub przyszłe) wersje, to standard rozwiązaniem jest śledzenie tego samego elementu z wieloma wierszami za pomocą kombinacji wartości początku, końca i czasu trwania. Widok umożliwia wygodny dostęp do bieżących wartości. Jeśli jest to podejście, które podejmiesz, możesz napotkać problemy, jeśli Twoje wersjonowane dane odwołują się do mutowalnych, ale niezweryfikowanych danych.

Korekta danych jest aspektem 'valid-time ' koncepcji czasowej bazy danych. Wiele badań w tym zakresie i wiele wzorców i wytycznych powstało. Napisałem obszerną odpowiedź z mnóstwem odniesień do tego pytania dla zainteresowanych.

Jeśli chcesz zrobić pierwszą z nich, możesz użyć XML również w tabeli pracowników. Większość nowszych baz danych pozwala na zapytania do pól XML, więc nie zawsze jest to problem. I może być łatwiej mieć jeden sposób dostępu do danych pracowników, niezależnie od tego, czy jest to najnowsza wersja, czy wcześniejsza wersja.

Spróbowałbym jednak drugiego podejścia. Można to uprościć, mając tylko jedną tabelę pracowników z polem DateModified. Pracownik + DateModified byłby podstawowym klucz i możesz zapisać nową wersję, dodając wiersz. W ten sposób Archiwizacja starszych wersji i przywracanie wersji z archiwum jest również łatwiejsze.

Innym sposobem na to może być model datavault autorstwa Dana Linstedta. Zrobiłem projekt dla holenderskiego Biura Statystycznego, który używał tego modelu i działa całkiem dobrze. Ale nie sądzę, że jest to bezpośrednio przydatne do codziennego korzystania z bazy danych. Może wpadniesz na jakieś pomysły po przeczytaniu jego prac.

Podzielę się z Wami moim projektem i różni się on od obu projektów tym, że wymaga jednej tabeli na każdy typ encji. Znalazłem najlepszy sposób na opisanie każdego projektu bazy danych poprzez ERD, oto mój:

W tym przykładzie mamy podmiot o nazwie employee. user tabela przechowuje rekordy użytkowników, a entity i entity_revision to dwie tabele, które przechowują historię rewizji dla wszystkich typów encji, które będą mieć w swoim organizmie. Oto jak działa ten projekt:

dwa pola entity_id i revision_id

Każda jednostka w Twoim systemie będzie miała unikalny identyfikator jednostki. Twój podmiot może przejść zmiany, ale jego entity_id pozostanie taki sam. Musisz zachować ten identyfikator podmiotu w tabeli pracownika (jako klucz obcy). Należy również zapisać typ jednostki w tabeli entity (np. 'pracownik'). A co do revision_id, jak pokazuje jego nazwa, śledzi zmiany Twojego podmiotu. Najlepszym sposobem na to jest użycie employee_id jako twojego revision_id. Oznacza to, że będziesz miał duplikaty identyfikatorów rewizji dla różnych typów podmiotów, ale to nie jest dla mnie traktowane (nie jestem pewien twojego przypadku). Jedyną ważną uwagą jest to, że kombinacja entity_id i revision_id powinna być unikalna.

Istnieje również pole state w tabeli entity_revision , które wskazywało stan rewizji. Może mieć jeden z trzech stanów: latest, obsolete lub deleted (nie poleganie na dacie zmian pomaga znacznie zwiększyć liczbę zapytań).

Ostatnia uwaga na temat revision_id, nie stworzyłem obcego klucza łączącego employee_id z revision_id, ponieważ nie chcemy zmieniać tabeli entity_revision dla każdego typu encji, które możemy dodać w przyszłości.

Wstawianie

Dla każdego pracownika, który chcesz wstawić do bazy danych, możesz dodaje również rekord do entity i entity_revision . Te dwa ostatnie rekordy pomogą Ci śledzić, przez kogo i kiedy rekord został wstawiony do bazy danych.

UPDATE

Każda aktualizacja dla istniejącego rekordu pracownika będzie zaimplementowana jako dwie wstawki, jedna w tabeli pracowników i jedna w entity_revision. Drugi pomoże Ci dowiedzieć się, przez kogo i kiedy rekord został zaktualizowany.

Delecja

Do usunięcia pracownik, rekord jest wstawiany do entity_revision stwierdzając usunięcie i zrobione.

Jak widać w tym projekcie żadne dane nie są nigdy zmieniane lub usuwane z bazy danych, a co ważniejsze każdy typ encji wymaga tylko jednej tabeli. Osobiście uważam ten projekt za bardzo elastyczny i łatwy w obsłudze. Ale nie jestem pewien co do ciebie, bo Twoje potrzeby mogą być inne.

[UPDATE]

Mając obsługiwane partycje w nowych wersjach MySQL, wierzę, że mój projekt również pochodzi z jednym z najlepszych występów. Można partycjonować entity tabelę używając pola type, natomiast partycję entity_revision używając swojego pola state. Zwiększy to znacznie liczbę zapytań SELECT, zachowując prostotę i czystość projektu.

A może:

• EmployeeID
• DateModified
  • i / lub numer wersji, w zależności od tego, jak chcesz go śledzić
• ModifiedByUSerId
  • plus wszelkie inne informacje, które chcesz śledzić
• pola pracownicze

Tworzysz klucz główny (EmployeeId, DateModified) i aby uzyskać "bieżący" rekord(y), po prostu wybierz MAX(DateModified) dla każdego employeeid. Przechowywanie IsCurrent jest bardzo zły pomysł, ponieważ po pierwsze, można go obliczyć, a po drugie, jest zbyt łatwo, aby dane zostały zsynchronizowane.

Możesz również utworzyć widok, który wyświetla tylko najnowsze rekordy i używać go głównie podczas pracy w aplikacji. Fajną rzeczą w tym podejściu jest to, że nie masz duplikatów danych i nie musisz zbierać danych z dwóch różnych miejsc (bieżących w pracownikach i zarchiwizowanych w historiach pracowniczych), aby uzyskać całą historię lub wycofanie itp.).

Jeśli chcesz polegać na danych historii (ze względów raportowania) powinieneś użyć struktury coś takiego:

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds the Employee revisions in rows.
"EmployeeHistories (HistoryId, EmployeeId, DateModified, OldValue, NewValue, FieldName)"

Lub globalne rozwiązanie dla aplikacji:

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, OldValue, NewValue, FieldName)"

Możesz zapisać swoje wersje również w XML, wtedy masz tylko jeden rekord dla jednej wersji. Będzie to wyglądało następująco:

// Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

// Holds all entities revisions in rows.
"EntityChanges (EntityName, EntityId, DateModified, XMLChanges)"

Mieliśmy podobne wymagania i okazało się, że często użytkownik chce po prostu zobaczyć co zostało zmienione, niekoniecznie cofając wszelkie zmiany.

Nie jestem pewien, jaki jest Twój przypadek użycia, ale to, co zrobiliśmy, to tworzenie i audyt tabeli, która jest automatycznie aktualizowana ze zmianami w jednostce biznesowej, w tym przyjazną nazwą wszelkich odniesień i wyliczeń kluczy zagranicznych.

Ilekroć użytkownik zapisze swoje zmiany, przeładowujemy stary obiekt, Uruchom porównanie, Zapisz zmiany i zapisz encję (wszystko odbywa się w jednej transakcji bazy danych w przypadku wystąpienia jakichkolwiek problemów).

Wydaje się to działać bardzo dobrze dla naszych użytkowników i oszczędza nam ból głowy posiadania całkowicie oddzielnej tabeli audytu z tymi samymi polami, co nasz podmiot biznesowy.

Id 3, "bob", "123 main street", następnie kolejny ID 3, "bob" "234 elm st", i tak dalej, w istocie jest w stanie wyrzygać historię zmian pokazującą każdy adres " bob " był w.

Najlepszym sposobem na to jest posiadanie pola "is current" na każdym rekordzie i (prawdopodobnie) znacznika czasu lub FK do tabeli daty/czasu.

Wstawki muszą następnie ustawić "is current", a także wyłączyć "is current" na poprzedni rekord "jest aktualny". Zapytania muszą określać "jest aktualne", chyba że chcesz całą historię.

Istnieją dalsze poprawki, jeśli jest to bardzo duża tabela lub spodziewana jest duża liczba zmian, ale jest to dość standardowe podejście.