Czy źle jest mieć kolumnę „status rekordu” w tabeli bazy danych?

Muszę najpierw wyjaśnić, że kolumna stanu nie ma na celu odzwierciedlać statusu przedmiotu rzeczywistego reprezentowanego przez rekord (wiersz) w tabeli. Ma raczej na celu pokazanie stanu samego rekordu.

Może być tak prosty jak Aktywny / Nieaktywny lub skomplikowany jak Zatwierdzony / Usunięty / Zablokowany / Oczekujący / Odrzucony itp. Status może być przechowywany w kolumnie logicznej / krótkiej liczbie całkowitej lub kolumnie jednoznakowej, z odwzorowaniami jak true/ 1= Aktywny lub A= Zatwierdzony.

Podstawową ideą jest posiadanie w aplikacji obsługi odzyskiwania przypominającego kosz / kosz w aplikacji (i symulowanie jej w bazie danych). Jeśli istnieje interfejs GUI lub inny interfejs, który rzekomo pozwala użytkownikowi „usuwać” rekordy, tak naprawdę nie usuwa rekordu w tabeli, ale po prostu zmienia status rekordu na Nieaktywny lub Usunięty. Gdy interfejs pobiera rekordy, zawsze otrzymuje rekordy, które pasują tylko pod warunkiem, że status jest Aktywny lub Zatwierdzony.

Jeśli użytkownik popełni błąd i konieczne będzie odzyskanie „usuniętego” rekordu (z perspektywy użytkownika), DBA może łatwo załatać ten rekord z powrotem do stanu Aktywny lub Zatwierdzony, co byłoby lepsze niż wyszukiwanie kopii zapasowych i, mam nadzieję, znalezienie oryginalnego rekordu tam. Albo sam interfejs może pozwolić użytkownikowi przeglądać usunięte rekordy w osobnym widoku i przywracać je w razie potrzeby, a nawet trwale je usuwać (usuwając aktualny rekord).

Moje pytania:

• Czy to dobra praktyka, czy zła praktyka?
• Czy wpływa to na normalizację danych?
• Jakie są potencjalne pułapki?
• Czy istnieje alternatywna metoda osiągnięcia tego samego celu? (patrz uwaga)
• W jaki sposób baza danych może wymuszać unikalne ograniczenia danych tylko dla określonego statusu (ale dopuszczać dowolną liczbę duplikatów dla innych statusów)?
• Dlaczego bazy danych nie oferują natywnej funkcji „kosza” lub śledzenia / odzyskiwania tabeli, aby umożliwić interfejsom usuwanie faktycznych rekordów bez obaw?

Uwaga: Czytałem o utrzymywaniu osobnej tabeli historii, ale wydaje się to gorsze pod względem pamięci i konieczności generowania wyzwalaczy i aktualizowania wyzwalaczy zgodnie ze schematem śledzonej tabeli.

Znam to jako „miękkie usuwanie”; oznaczanie rekordu jako „usuniętego”, nawet jeśli tak naprawdę nie jest.

Czy to dobra praktyka, czy zła praktyka?

To zależy.
Jeśli jest to coś, czego Twoi użytkownicy bardzo potrzebują, to prawdopodobnie jest to dobra rzecz. Jednak w zdecydowanej większości przypadków argumentowałbym, że dodaje [dużo] kosztów ogólnych dla niewielkiej korzyści.

Czy wpływa to na normalizację danych?

Nie, ale wpłynie to na indeksowanie tych danych.
Upewnij się, że umieściłeś kolumnę „usuniętą” w swoich indeksach, aby wiersze te zostały wykluczone jak najszybciej w zapytaniach.

Jakie są potencjalne pułapki?

Twoje dane stają się trochę bardziej złożone. Wszystko, co zbliża się do danych w pobliżu, musi „wiedzieć” o tych dodatkowych, „nie-tak” rekordach. Lub musisz utworzyć Widoki w tych tabelach, które wykluczają te wiersze, i użyć tych widoków, powiedzmy, w wybranym narzędziu do raportowania.

Twoja baza danych może wzrosnąć. Jeśli tak naprawdę nie usuwasz tych wierszy, to wciąż tam są, zajmując miejsce. To może, ale nie musi stanowić problemu, zwłaszcza że umieściłeś je w swoich indeksach, więc zajmowane przez nich miejsce jest zwielokrotniane.

Czy istnieje alternatywna metoda osiągnięcia tego samego celu? (patrz uwaga)

Nie, naprawdę nie.

W jaki sposób baza danych może wymuszać unikalne ograniczenia danych tylko dla określonego statusu (ale dopuszczać dowolną liczbę duplikatów dla innych statusów)?

Nie łatwo. Deklaratywna integralność referencyjna (klauzule dotyczące klucza obcego) jest najczystszym sposobem na wdrożenie tego i jest łatwa w narzędziach raportujących, aby wybrać te reguły w celu ustalenia relacji między tabelami. Takie zasady mają zastosowanie do wszystkich rekordów, niezależnie od „statusu” (i nie ma na to sposobu).

Alternatywą jest użycie wyzwalaczy, fragmentów kodu proceduralnego, które egzekwują referencyjną integralność między tabelami i wykonują wszystkie sprytne, warunkowe czynności, których potrzebujesz. To jest dobre w twoim konkretnym przypadku, ale większość korzyści z deklaratywnego RI wychodzi z okna - nie ma wykrywalnych [zewnętrznie] związków między twoimi tabelami; to wszystko jest „ukryte” w wyzwalaczach.

Dlaczego bazy danych nie oferują natywnej funkcji „kosza” lub śledzenia / odzyskiwania tabeli, aby umożliwić interfejsom usuwanie faktycznych rekordów bez obaw?

Dlaczego mieliby ?

Są to w końcu bazy danych, a nie systemy plików ani arkusze kalkulacyjne.

To, co robią, może [zrobić] bardzo dobrze.

Na to, czego nie robią, prawdopodobnie nie było dużego zapotrzebowania.

To jest praktyka. To, czy jest to dobre, czy złe, zależy w dużej mierze od twojej aplikacji i tego, jak często będziesz potrzebować / chcesz zrobić „cofnięcie usunięcia”. Byłbym dość wątpliwy w związku z planem umieszczenia tego rodzaju kolumn w każdej tabeli w systemie - wydaje się wysoce nieprawdopodobne, abyś naprawdę zawracał sobie głowę wdrażaniem cofania usuwania na każdym stole w systemie. I wymaga implementacji - w zdecydowanej większości przypadków nie usuwasz pojedynczego wiersza z pojedynczej tabeli, musisz przejść przez tabele podrzędne, usuwając wiersze i aktualizując powiązane tabele.

W przypadku większości pozostałych pytań w dużym stopniu zależy to od implementacji. Na przykład Oracle udostępnia różne metody śledzenia wszystkich zmian w tabeli - Archiwum danych Flashback (FDA znane również jako Total Recall) jest najnowszym podejściem do utrzymywania pełnej historii każdej wersji wiersza i archiwizacji w bazie danych w celu wdrożenia miękki wzór usuwania. Inne bazy danych mogą zapewniać inne sposoby implementacji wzorca. W zależności od bazy danych i tego, jak zaimplementujesz miękkie usuwanie, będzie to miało różny wpływ na wydajność, to, czy i jak można egzekwować ograniczenia itp. Jeśli mówimy o Oracle, możesz wiele zrobić na przykład z indeksami opartymi na funkcjach , w SQL Server często można używać filtrowanych indeksów do podobnych celów.

W systemach MRP / ERP bardzo często stosuje się pole „oflagowane do usunięcia”.

Na przykład można chcieć oznaczyć rekord części lub zapasów, który nie jest już sprzedawany jako nieaktywny, ale nadal istnieją z nim nierozliczone zamówienia. Wykonanie rzeczywistego usunięcia rekordu może wpłynąć na zamówienia, które nie zostały jeszcze wysłane, wpisy księgi, które jeszcze nie zostały opublikowane, tabele historii, które nie zostaną zbudowane do końca miesiąca itp. Wiele systemów nie zezwoli na usunięcie rekordu, chyba że przejdzie szereg walidacji w stosunku do innych tabel. Jeśli kasujesz usuwanie przez relacje, prawdziwe usuwanie może być jeszcze bardziej destrukcyjne.

Zamiast tego, oznaczając go do usunięcia, umieszczasz wyraźny znacznik zamiaru w rekordzie, a później zaplanowane zadanie może usunąć rekord, jeśli sprawdzi, że wszystkie powiązane tabele już go nie odwołują.

Podobny przypadek można zastosować w przypadku tej funkcji w tabeli klienta i innych tabelach „długoterminowych”. Ma to nawet sens w przypadku bardziej niestabilnych tabel, takich jak zamówienia, chociaż nazwa flagi może przybrać formę „wysłane” lub „anulowane”. Pełni tę samą funkcję: nie usuwaj go w tej chwili, ale użyj go jako flagi dla programu czyszczącego, aby spróbował sprawdzić poprawność usunięcia rekordu w przyszłości.

Jako alternatywne rozwiązanie, stosowanie pozyskiwania zdarzeń pozwala na podobne cele bez komplikowania struktury tabeli, chociaż sprawia, że ​​kod do modyfikowania danych jest nieco bardziej złożony, ponieważ musisz zapisać modyfikację w zdarzeniu, które można utrwalić w historii zdarzeń . Pozwala to na odtworzenie bazy danych w dowolnym momencie, co może być bardzo przydatną funkcją.

(Nie wierzę, że to właśnie rozumiałeś przez „tabelę historii”, co myślę, że miałeś na myśli po prostu skopiowanie zmodyfikowanych lub usuniętych rekordów do innej tabeli przed ich zmianą)

Często widzę i używam tego wzorca dla tych przypadków użycia:

• metadane, w których chcesz wyświetlać tylko wartości obowiązujące dzisiaj. Na przykład, aby wybrać z listy producentów samochodów z rozwijanej listy, gdzie włączone = 1, wartości tabel dla ID, VALUE, ENABLED to 1, „Ford”, 1 i 2, „Edsel”, 0, 3, „Toyota” , 1 daje tylko wybór Forda i Toyoty
• w przypadku systemu zarządzania sprawami, w którym paradygmatem jest to, że sprawa może znajdować się tylko w jednym stanie na raz. W tym przypadku kolumna przełączająca została nazwana CURRENT z wartościami 0 lub 1 wymuszonymi przez ograniczenia sprawdzające. Gdy przypadek przechodzi z jednego stanu do drugiego, aplikacja aktualizuje flagę CURRENT starego stanu na 0, a nowego na 1

Problem polega na wymuszeniu integralności danych, jeśli więcej niż jedna aplikacja lub usługa sieciowa zapisuje do tabel. Jak zapewnić, że w przypadku istnieje tylko jeden bieżący stan? Jak podkreśla Justin Cave, można to zrobić w Oracle, tworząc wirtualny indeks oparty na funkcji, ale to dodatkowe obciążenie dla czegoś, co pierwotnie wydawało się prostą koncepcją.

Jest to dobra praktyka, jeśli planujesz wykorzystywać swoje dane do raportowania (każda wystarczająco duża aplikacja musiałaby mieć raporty).

Aby przyspieszyć działanie aplikacji, naprawdę nie powinieneś pozwalać, aby narzędzia raportujące działały w Twojej bazie danych. Dlatego musisz wykonać kopię / synchronizację z inną bazą danych.

Używam recordStatustylko dwóch stanów ACTIVElub CANCELLEDw połączeniu ze lastUpdatedOnznacznikiem czasu. Używam recordStatusraczej niż, statusco zwykle ma znaczenie biznesowe.

Kiedy synchronizuję bazę danych raportowania z aplikacją, filtruję, lastUpdatedOnaby wiedzieć, które z nich mam zamiar zastąpić po stronie raportowania.

Po stronie raportowania nie będę mieć pól recordStatuslub, lastUpdatedOnponieważ generalnie nie będą zgłaszane. Jako taki, gdy widzę CANCELLEDstatus, usunąłbym rekord ze strony raportującej w ten sposób, że ma on tylko aktywne rekordy.

Można to rozszerzyć na inne typy sklepów, takie jak archiwa lub kopie zapasowe, w których wymagana jest prawie pełna synchronizacja. Jednak raportowanie jest bardziej powszechnym celem.

Uwaga swój przykład Approved, New, Pendingnie jest to dobry pomysł, aby umieścić jako wspólnego pola jako że ma firmę co oznacza, że powinien iść tylko tam, gdzie to ma sens działalności mądry.

Jeśli chodzi o zablokowane, użyj, versionNoco zapewnia optymistyczną blokadę dla twojego rekordu.

Inną opcją zamiast recordStatusjest recordActiveprzechowywanie go jako booleanzajmującego mniej miejsca i mniej indeksowania, ale martwiłbym się przyszłymi potrzebami, których możesz nie przewidzieć.