Projekt bazy danych do rejestrowania audytu

Za każdym razem, gdy potrzebuję zaprojektować nową bazę danych, spędzam trochę czasu zastanawiając się, jak skonfigurować schemat bazy danych, aby prowadzić dziennik audytu zmian.

Zadano już tutaj kilka pytań na ten temat, ale nie zgadzam się, że istnieje jedno najlepsze podejście do wszystkich scenariuszy:

• Projekt bazy danych dla zmian
• Najlepszy projekt tabeli bazy danych inspekcji dziennika zmian
• Pomysły na projekt bazy danych do przechwytywania ścieżek audytu

Natknąłem się również na ten interesujący artykuł o utrzymywaniu dziennika zmian w bazie danych, w którym próbuję wymienić zalety i wady każdego podejścia. Jest bardzo dobrze napisany i zawiera ciekawe informacje, ale jeszcze bardziej utrudnia mi to podjęcie decyzji.

Moje pytanie brzmi: czy istnieje odniesienie, którego mogę użyć, może książka lub coś w rodzaju drzewa decyzyjnego, do którego mogę się odwołać, aby zdecydować, w którą stronę powinienem pójść, na podstawie pewnych zmiennych wejściowych, takich jak:

• Dojrzałość schematu bazy danych
• Sposób odpytywania dzienników
• Prawdopodobieństwo, że trzeba będzie odtworzyć rekordy
• Co ważniejsze: wydajność zapisu lub odczytu
• Charakter rejestrowanych wartości (ciąg, liczby, obiekty blob)
• Dostępna przestrzeń do przechowywania

Znane mi podejścia to:

1. Dodaj kolumny dla daty utworzenia i modyfikacji oraz użytkownika

Przykład tabeli:

• ID
• wartość_1
• wartość_2
• wartość_3
• stworz Date
• zmieniona data
• stworzone przez
• modyfikowane przez

Główne wady: tracimy historię modyfikacji. Nie można wycofać zmian po zatwierdzeniu.

2. Wstaw tylko tabele

Przykład tabeli :

• ID
• wartość_1
• wartość_2
• wartość_3
• z
• do
• usunięte (Boolean)
• użytkownik

Główne wady: Jak aktualizować klucze obce? Potrzeba ogromnej przestrzeni

3. Utwórz oddzielną tabelę historii dla każdej tabeli

Przykład tabeli historii:

• ID
• wartość_1
• wartość_2
• wartość_3
• wartość_4
• użytkownik
• usunięte (Boolean)
• znak czasu

Główne wady: Musi powielić wszystkie kontrolowane tabele. Jeśli schemat ulegnie zmianie, konieczna będzie również migracja wszystkich dzienników.

4. Utwórz skonsolidowaną tabelę historii dla wszystkich tabel

Przykład tabeli historii:

• Nazwa tabeli
• pole
• użytkownik
• Nowa wartość
• usunięte (Boolean)
• znak czasu

Główne wady: Czy będę w stanie łatwo odtworzyć rekordy (wycofać) w razie potrzeby? Kolumna nowa_wartość musi być ogromnym ciągiem, aby mogła obsługiwać wszystkie różne typy kolumn.

Jedną z metod używanych na kilku platformach wiki jest oddzielenie danych identyfikacyjnych od kontrolowanej zawartości. Dodaje to złożoności, ale w końcu otrzymujesz ścieżkę audytu pełnych rekordów, a nie tylko list edytowanych pól, które następnie musisz zmiksować, aby dać użytkownikowi wyobrażenie o tym, jak wyglądał stary rekord.

Na przykład, gdybyś miał tabelę o nazwie Szanse sprzedaży do śledzenia umów sprzedaży, w rzeczywistości utworzyłbyś dwie oddzielne tabele:

Opportunities
Opportunities_Content (lub coś w tym rodzaju)

Szanse stół miałby informacje chcesz użyć, aby jednoznacznie identyfikować rekord i byłoby domu klucz podstawowy chcesz odniesienia dla swoich zagranicznych kluczowych relacji. Opportunities_Content tabela będzie posiadać wszystkie pola użytkownicy mogą zmieniać i dla którego chcesz zachować ślad rewizyjny. Każdy rekord w tabeli zawartości zawierałby własną PK oraz dane zmodyfikowane przez i data modyfikacji. Szanse tabela będzie zawierać odniesienie do obecnej wersji, jak również informacje o tym, kiedy główny rekord został pierwotnie stworzony i przez kogo.

Oto prosty przykład:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

A zawartość:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

Prawdopodobnie uczyniłbym PK tabeli zawartości kluczem wielokolumnowym z identyfikatora strony i wersji, pod warunkiem, że wersja byłaby typem tożsamości. Użyjesz kolumny Revision jako FK. Następnie wyciągasz skonsolidowany rekord, łącząc się w ten sposób:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

Tam mogą być jakieś błędy ... to jest z głowy. Powinien jednak dać wyobrażenie o alternatywnym wzorze.

Jeśli używasz SQL Server 2008, prawdopodobnie powinieneś rozważyć zmianę przechwytywania danych. Jest to nowość na rok 2008 i może zaoszczędzić znaczną ilość pracy.

Nie znam żadnego odniesienia, ale jestem pewien, że ktoś coś napisał.

Jeśli jednak celem jest po prostu rejestracja tego, co się wydarzyło - najbardziej typowe użycie dziennika audytu - dlaczego nie po prostu zachować wszystkiego:

timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue

Przypuszczalnie jest to utrzymywane przez spust.

Stworzymy małą przykładową bazę danych dla aplikacji do blogowania. Wymagane są dwie tabele:

blog: przechowuje unikalny identyfikator posta, tytuł, treść i usuniętą flagę. audit: przechowuje podstawowy zestaw zmian historycznych z identyfikatorem rekordu, identyfikatorem wpisu na blogu, typem zmiany (NOWA, EDYCJA lub USUŃ) oraz datą / godziną tej zmiany. Poniższy kod SQL tworzy blogi indeksuje usuniętą kolumnę:

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `title` text,
    `content` text,
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

Poniższy kod SQL tworzy audittabelę. Wszystkie kolumny są indeksowane, a klucz obcy jest definiowany dla audit.blog_id, który odwołuje się do blog.id. Dlatego też, gdy fizycznie USUWAMY wpis na blogu, usuwana jest również jego pełna historia kontroli.

CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `blog_id` mediumint(8) unsigned NOT NULL,
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
    `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_blog_id` (`blog_id`),
    KEY `ix_changetype` (`changetype`),
    KEY `ix_changetime` (`changetime`),
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Myślę, że nie ma to jak drzewo decyzyjne. Ponieważ niektóre zalety i wady (lub wymagania) nie są tak naprawdę policzalne. Jak na przykład mierzy się dojrzałość?

Po prostu wyrównaj wymagania biznesowe dotyczące rejestrowania audytu. Spróbuj przewidzieć, jak te wymagania mogą się zmienić w przyszłości i wygeneruj swoje wymagania techniczne. Teraz możesz porównać to z zaletami i wadami i wybrać odpowiednią / najlepszą opcję.

Zapewniamy, że nie ma znaczenia, jak zdecydujesz, zawsze znajdzie się ktoś, kto myśli, że podjąłeś złą decyzję. Jednak odrobiłeś pracę domową i uzasadniłeś swoją decyzję.