Najlepszy projekt dla tabeli bazy danych dziennika zmian / inspekcji? [Zamknięte]

Muszę utworzyć tabelę bazy danych, aby przechowywać inny dziennik zmian / inspekcję (gdy coś zostało dodane, usunięte, zmodyfikowane itp.). Nie muszę przechowywać szczególnie szczegółowych informacji, więc pomyślałem coś w stylu:

• id (dla zdarzenia)
• użytkownik, który go uruchomił
• Nazwa wydarzenia
• opis wydarzenia
• sygnatura czasowa wydarzenia

Czy coś mi umyka? Oczywiście mogę nadal ulepszać projekt, chociaż nie planuję go komplikować (tworzenie innych tabel dla typów zdarzeń lub podobnych rzeczy nie wchodzi w rachubę, ponieważ jest to komplikacja dla moich potrzeb).

W projekcie, nad którym pracuję, dziennik audytu również zaczynał się od bardzo minimalistycznego projektu, takiego jak ten, który opisałeś:

event ID
event date/time
event type
user ID
description

Pomysł był ten sam: aby wszystko było proste.

Szybko jednak stało się oczywiste, że ten minimalistyczny projekt nie wystarczy. Typowy audyt sprowadzał się do takich pytań:

Who the heck created/updated/deleted a record 
with ID=X in the table Foo and when?

Aby więc móc szybko odpowiedzieć na takie pytania (za pomocą SQL), otrzymaliśmy dwie dodatkowe kolumny w tabeli audytu

object type (or table name)
object ID

Wtedy projekt naszego dziennika audytu naprawdę się ustabilizował (od kilku lat).

Oczywiście ostatnie „ulepszenie” działałoby tylko dla tabel, które miały klucze zastępcze. Ale zgadnij co? Wszystkie nasze stoliki, które warto sprawdzić, mają taki klucz!

Rejestrujemy również stare i nowe wartości oraz kolumnę, z której pochodzą, a także klucz główny tabeli poddawanej inspekcji w tabeli szczegółów kontroli. Pomyśl, do czego potrzebujesz tabeli audytu? Nie tylko chcesz wiedzieć, kto i kiedy dokonał zmiany, ale także gdy nastąpi zła zmiana, potrzebujesz szybkiego sposobu na przywrócenie danych.

Podczas projektowania powinieneś napisać kod, aby odzyskać dane. Kiedy musisz dojść do siebie, zwykle się spieszysz, najlepiej już być przygotowanym.

Istnieje kilka innych rzeczy, które możesz chcieć poddać audytowi, takie jak nazwy tabel / kolumn, komputer / aplikacja, z której dokonano aktualizacji i nie tylko.

Teraz zależy to od tego, jak szczegółowego audytu naprawdę potrzebujesz i na jakim poziomie.

Zaczęliśmy budować własne rozwiązanie audytowe oparte na wyzwalaczach i chcieliśmy wszystko skontrolować, a także mieć pod ręką opcję odzyskiwania. Okazało się to zbyt skomplikowane, więc zakończyliśmy inżynierię wsteczną opartego na wyzwalaczu narzędzia innej firmy ApexSQL Audit, aby stworzyć własne niestandardowe rozwiązanie.

Porady:

• Uwzględnij wartości przed / po

• Uwzględnij 3-4 kolumny do przechowywania klucza podstawowego (w przypadku, gdy jest to klucz złożony)

• Przechowuj dane poza główną bazą danych, zgodnie z sugestią Roberta

• Poświęć przyzwoitą ilość czasu na przygotowanie raportów - zwłaszcza tych, których możesz potrzebować do odzyskania

• Zaplanuj przechowywanie nazwy hosta / aplikacji - może to być bardzo przydatne do śledzenia podejrzanych działań

Tutaj i w podobnych pytaniach jest wiele ciekawych odpowiedzi. Jedyne rzeczy, które mogę dodać z własnego doświadczenia, to:

1. Umieść swoją tabelę audytu w innej bazie danych. Idealnie byłoby, gdybyś chciał oddzielić dane od oryginalnych danych. Jeśli chcesz przywrócić bazę danych, tak naprawdę nie chcesz przywracać ścieżki audytu.

2. Denormalizuj tak bardzo, jak to tylko możliwe. Chcesz, aby tabela miała jak najmniej zależności od oryginalnych danych. Tabela audytu powinna być prosta i błyskawiczna do pobierania danych. Żadnych wymyślnych łączeń ani wyszukiwań w innych tabelach w celu uzyskania danych.

Co mamy w naszej tabeli: -

Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name

Identyfikator ogólny wskazuje wiersz w zaktualizowanej tabeli, a nazwa tabeli to nazwa tej tabeli w postaci ciągu. Niezbyt dobry projekt DB, ale bardzo użyteczny. Wszystkie nasze tabele mają jedną zastępczą kolumnę klucza, więc działa to dobrze.

Ogólnie audyt niestandardowy (tworzenie różnych tabel) jest złą opcją. Wyzwalacze bazy danych / tabeli można wyłączyć, aby pominąć niektóre działania dziennika. Niestandardowe tabele audytu mogą być modyfikowane. Mogą wystąpić wyjątki, które spowodują obniżenie poziomu aplikacji. Nie wspominając o trudnościach w zaprojektowaniu solidnego rozwiązania. Jak dotąd w tej dyskusji widzę bardzo proste przypadki. Potrzebujesz całkowitego oddzielenia od aktualnej bazy danych i od wszystkich uprzywilejowanych użytkowników (DBA, Developers). Każdy główny nurt RDBMS zapewnia funkcje audytu, których nawet DBA nie jest w stanie wyłączyć, manipulują w tajemnicy. Dlatego udostępniona przez dostawcę RDBMS możliwość audytu musi być pierwszą opcją. Inną opcją byłby czytnik dziennika transakcji innej firmy lub niestandardowy czytnik dziennika, który wypycha zdekomponowane informacje do systemu przesyłania wiadomości, który kończy się w niektórych formach hurtowni danych audytu lub modułu obsługi zdarzeń w czasie rzeczywistym. W podsumowaniu: Architekt rozwiązań / "Hands on Data Architect" musi się zaangażować w przeznaczenie takiego systemu na podstawie wymagań. Zwykle jest to zbyt poważna sprawa, aby przekazać ją programistom do rozwiązania.

Istnieje wiele sposobów, aby to zrobić. Mój ulubiony sposób to:

1. Dodaj mod_userpole do tabeli źródłowej (tej, którą chcesz zarejestrować).

2. Utwórz tabelę dziennika zawierającą pola, które chcesz rejestrować, log_datetimeoraz seq_numpola i . seq_numjest kluczem podstawowym.

3. Zbuduj wyzwalacz w tabeli źródłowej, który wstawia bieżący rekord do tabeli dziennika po każdej zmianie dowolnego monitorowanego pola.

Teraz masz zapis każdej zmiany i tego, kto ją wprowadził.

Zgodnie z zasadą separacji:

1. Tabele danych inspekcji muszą być oddzielone od głównej bazy danych. Ponieważ bazy danych inspekcji mogą zawierać wiele danych historycznych, z punktu widzenia wykorzystania pamięci sensowne jest ich oddzielenie.

2. Nie używaj wyzwalaczy do audytu całej bazy danych, ponieważ skończysz z bałaganem różnych baz danych do obsługi. Będziesz musiał napisać jeden dla DB2, SQLServer, Mysql itp.