Przyzwyczaiłem się do wyświetlania wierszy tabeli z kolumnami takimi jak „Usunięte” i nie lubię ich. Samo pojęcie „skreślony” polega na tym, że wpis nie powinien był zostać dokonany w pierwszej kolejności. Praktycznie nie można ich usunąć z bazy danych, ale nie chcę ich z moimi gorącymi danymi. Logicznie usunięte wiersze są z definicji zimnymi danymi, chyba że ktoś konkretnie chce zobaczyć usunięte dane.
Ponadto każde napisane zapytanie musi je konkretnie wykluczać, a indeksy również je uwzględniają.
Chciałbym zobaczyć zmianę na poziomie architektury bazy danych i na poziomie aplikacji: utwórz schemat o nazwie „usunięty”. Każda tabela zdefiniowana przez użytkownika ma identyczny odpowiednik w schemacie „usuniętym” z dodatkowym polem przechowującym metadane - użytkownika, który ją usunął i kiedy. Konieczne jest utworzenie kluczy obcych.
Następnie usuwa staje się usuwanie-wstawianie. Najpierw wiersz do usunięcia jest wstawiany do jego „usuniętego” odpowiednika schematu. Wiersz w głównej tabeli można następnie usunąć. Należy jednak dodać dodatkową logikę gdzieś wzdłuż linii. Naruszenie klucza obcego można rozwiązać.
Klucze obce muszą być odpowiednio obsługiwane. Złą praktyką jest logiczne usuwanie wiersza, ale którego podstawowy / niepowtarzalny ma kolumny w innych tabelach, które go dotyczą. To i tak nie powinno się zdarzyć. Zwykłe zadanie może usuwać wiersze wdów (wiersze, których klucze podstawowe nie mają odniesień w innych tabelach pomimo obecności klucza obcego. Jest to jednak logika biznesowa.
Ogólną korzyścią jest zmniejszenie metadanych w tabeli i poprawa wydajności. Kolumna „deleteDate” mówi, że ten wiersz nie powinien być tutaj, ale dla wygody zostawiamy go tam i pozwalamy zapytaniu SQL go obsłużyć. Jeśli kopia usuniętego wiersza jest przechowywana w schemacie „usuniętym”, wówczas główna tabela z gorącymi danymi ma wyższy procent gorących danych (zakładając, że jest archiwizowany w odpowiednim czasie) i mniej niepotrzebnych kolumn metadanych. Indeksy i zapytania nie muszą już uwzględniać tego pola. Im mniejszy rozmiar wiersza, tym więcej wierszy można dopasować do strony, tym szybciej działa SQL Server.
Główną wadą jest rozmiar operacji. Istnieją teraz dwie operacje zamiast jednej, a także dodatkowa logika i obsługa błędów. Może to prowadzić do większego blokowania niż aktualizacja pojedynczej kolumny, w przeciwnym razie zajęłoby to. Transakcja utrzymuje blokady na stole dłużej i są zaangażowane dwie tabele. Usuwanie danych produkcyjnych, przynajmniej z mojego doświadczenia, jest rzadkością. Mimo to w jednej z głównych tabel 7,5% z prawie 100 milionów wpisów ma wpis w kolumnie „Usunięte”.
W odpowiedzi na pytanie aplikacja musiałaby mieć świadomość „cofnięcia usunięcia”. Musiałby po prostu zrobić to samo w odwrotnej kolejności: wstawić wiersz ze schematu „usunięty” do głównej tabeli, a następnie usunąć wiersz ze „usuniętego schematu”. Znowu potrzebna jest dodatkowa logika i obsługa błędów, aby uniknąć błędów, problemów z kluczami obcymi i tym podobnych.