Projektowanie bazy danych: jak poradzić sobie z problemem „archiwizacji”?

Jestem pewien, że wiele aplikacji, aplikacji krytycznych, banków itd. Robi to codziennie.

Ideą tego wszystkiego jest:

• wszystkie wiersze muszą mieć historię
• wszystkie linki muszą pozostać spójne
• składanie wniosków o uzyskanie „bieżących” kolumn powinno być łatwe
• klienci, którzy kupili przestarzałe rzeczy, powinni nadal zobaczyć, co kupili, nawet jeśli ten produkt nie jest już częścią katalogu

i tak dalej.

Oto, co chcę zrobić, a ja wyjaśnię problemy, przed którymi stoję.

Wszystkie moje tabele będą miały następujące kolumny:

• id
• id_origin
• date of creation
• start date of validity
• start end of validity

A oto pomysły na operacje CRUD:

• create = wstaw nowy wiersz za pomocą id_origin= id, date of creation= teraz, start date of validity= teraz, end date of validity= null (= oznacza, że ​​jest to bieżący aktywny rekord)
• aktualizacja =
  • read = czytaj wszystkie rekordy z end date of validity== null
  • zaktualizuj „bieżący” rekord end date of validity= null za pomocą end date of validity= teraz
  • utwórz nowy z nowymi wartościami, a end date of validity= null (= oznacza, że ​​jest to bieżący aktywny rekord)
• delete = zaktualizuj „bieżący” rekord end date of validity= null za pomocą end date of validity= teraz

Oto mój problem: z wieloma do wielu skojarzeniami. Weźmy przykład z wartościami:

• Tabela A (id = 1, id_origin = 1, start = teraz, end = null)
• Tabela A_B (start = teraz, koniec = null, id_A = 1, id_B = 48)
• Tabela B (id = 48, id_origin = 48, start = teraz, end = null)

Teraz chcę zaktualizować tabelę A, rekord id = 1

• Oznaczam rekord id = 1 za pomocą end = now

• Wstawiam nową wartość do tabeli A i ... cholera, straciłem relację A_B, chyba że też zduplikuję relację ... skończyłoby się to tabelą:

• Tabela A (id = 1, id_origin = 1, początek = teraz, koniec = teraz + 8 mln)

• Tabela A (id = 2, id_origin = 1, start = teraz + 8 min, end = null)
• Tabela A_B (start = teraz, koniec = null, id_A = 1, id_B = 48)
• Tabela A_B (start = teraz, koniec = null, id_A = 2, id_B = 48)
• Tabela B (id = 48, id_origin = 48, start = teraz, end = null)

I ... no cóż, mam inny problem: relacja A_B: czy mam oznaczyć (id_A = 1, id_B = 48) jako przestarzałe czy nie (A - id = 1 jest przestarzałe, ale nie B - 48)?

Jak sobie z tym poradzić?

Muszę to zaprojektować na dużą skalę: produkty, partnerzy i tak dalej.

Jakie jest twoje doświadczenie w tej sprawie? Jak byś zrobił (jak to zrobiłeś)?

-- Edytować

Znalazłem ten bardzo interesujący artykuł , ale nie radzi sobie właściwie z „kaskadowaniem przestarzałości” (= o co właściwie pytam)

Nie jest dla mnie jasne, czy te wymagania są do celów audytu, czy po prostu zwykłych danych historycznych, takich jak CRM i koszyki na zakupy.

Tak czy inaczej, rozważ tabelę main i main_archive dla każdego głównego obszaru, w którym jest to wymagane. „Main” będzie mieć tylko bieżące / aktywne wpisy, podczas gdy „main_archive” będzie zawierał kopię wszystkiego, co kiedykolwiek przechodzi do main. Wstaw / aktualizuj do main_archive może być wyzwalaczem z wstawiania / aktualizacji do main. Usunięcia w stosunku do main_archive mogą być uruchamiane przez dłuższy okres, jeśli w ogóle.

W przypadku problemów referencyjnych, takich jak Cust X kupił Produkt Y, najłatwiejszym sposobem rozwiązania problemu referencyjnego cust_archive -> archive_product jest nigdy nie usuwać wpisów z archive_product. Ogólnie rzecz biorąc, wskaźnik odejść powinien być znacznie niższy w tej tabeli, więc rozmiar nie powinien stanowić problemu.

HTH.

To w pewnym stopniu pokrywa się z programowaniem funkcjonalnym; w szczególności pojęcie niezmienności.

Masz jedną tabelę o nazwie, PRODUCTa drugą o nazwie PRODUCTVERSIONlub podobną. Kiedy zmieniasz produkt, nie robisz aktualizacji, po prostu wstawiasz nowy PRODUCTVERSIONwiersz. Aby uzyskać najnowsze, możesz zindeksować tabelę według numeru wersji (desc), znacznika czasu (desc) lub możesz mieć flagę ( LatestVersion).

Teraz, jeśli masz coś, co odnosi się do produktu, możesz zdecydować, do której tabeli on wskazuje. Czy wskazuje na PRODUCTencję (zawsze odnosi się do tego produktu) czy PRODUCTVERSIONencję (odnosi się tylko do tej wersji produktu)?

Komplikuje się. Co jeśli masz zdjęcia produktu? Muszą wskazać tabelę wersji, ponieważ można je zmienić, ale w wielu przypadkach nie będą i nie trzeba niepotrzebnie powielać danych. Oznacza to, że potrzebujesz PICTUREstołu i PRODUCTVERSIONPICTURErelacji wiele do wielu.

Zaimplementowałem wszystkie rzeczy stąd z 4 polami na wszystkich moich stołach:

• ID
• tworzenie daty
• date_validity_start
• date_validity_end

Za każdym razem, gdy rekord musi zostać zmodyfikowany, powielam go, zaznaczam zduplikowany rekord jako „stary” =, date_validity_end=NOW()a bieżący jako dobry date_validity_start=NOW()i date_validity_end=NULL.

Sztuczka polega na relacji wiele do wielu i jeden do wielu: działa bez dotykania ich! Chodzi o zapytania, które są bardziej złożone: aby zapytać o rekord w konkretnej dacie (= nie teraz), mam dla każdego sprzężenia i dla głównej tabeli, aby dodać te ograniczenia:

WHERE (
  (date_validity_start<=:dateparam AND date_validity_end IS NULL)
  OR
  (date_validity_start<=:dateparam AND date_validity_start>=:dateparam)
)

Tak więc w przypadku produktów i atrybutów (relacja wiele do wielu):

SELECT p.*,a.*

FROM products p

JOIN products_attributes pa
ON pa.id_product = p.id
AND (
  (pa.date_validity_start<=:dateparam AND pa.date_validity_end IS NULL)
  OR
  (pa.date_validity_start<=:dateparam AND pa.date_validity_start>=:dateparam)
)

JOIN attributes a
ON a.id = pa.id_attribute
AND (
  (a.date_validity_start<=:dateparam AND a.date_validity_end IS NULL)
  OR
  (a.date_validity_start<=:dateparam AND a.date_validity_start>=:dateparam)
)

WHERE (
  (p.date_validity_start<=:dateparam AND p.date_validity_end IS NULL)
  OR
  (p.date_validity_start<=:dateparam AND p.date_validity_start>=:dateparam)
)

Co powiesz na to? Wydaje się to proste i dość skuteczne w stosunku do tego, co robiłem w przeszłości. W tabeli „historii” użyj innej PK. Zatem pole „CustomerID” to PK w tabeli Customer, ale w tabeli „history” PK to „NewCustomerID”. „CustomerID” staje się kolejnym polem tylko do odczytu. Dzięki temu „CustomerID” pozostaje niezmieniony w historii, a wszystkie relacje pozostają nienaruszone.