Pomoc w projektowaniu, listy w bazach danych

Mam prośbę o sprawdzenie i doradzenie jak najlepiej implementować wszelkie listy w bazie danych, w moim przykładzie poniżej, takimi listami są:

• marszruta technologiczna (ważna kolejność operacji, wymaga możliwości wprowadzania częstych aktualizacji)
• skład materiałowy (częste zmiany, czeste aktualizacje)

W obu listach powyżej najlepiej byłoby aby przy każdej zmianie, zachowywać starą listę w bazie jako archiwalną, nie wiem jak to zrobić, prosiłbym o podpowiedzi.

Ponadto prosiłbym o sprawdzenie czy tak powinno się definiowac numer wersji wyrobu gotowego, wyciągnąłęm tabelę wersji bo wydawalo mi się ze tą samą tabelę (wersja) mogę zastosować i do wyrobu i do dokumentacji i do materialu ale w takiej postaci jak teraz to chyba jest kaszana (nie jestem pewien) prosiłbym o doradzenie ajk to zrobić dobrze.

W tabeli Material jest kolumna "IDListyAlternatyw", tu też za bardzo nie wiem jak zaprojektowac baze aby byla mozliwość w skladzie materialowym, w przypadku braku w magazynie jakiegos materialu, aby system podpowiadal zdefiniowany alternatywny material ktorego ilosc w magazynie jest wieksza od zera.

Ponadto jest jeszcze jeden przypadek ktorego tez nie wiem jak zaprojektowac w bazie, gdzie w niektorych przypadkach dla wyrobu gotowego A, jednym z materialów jest np. wyrób gotowy B.

Materiał w większości aspektów jest tożsamy z wyrobem, szczególnie poprzez swoją rekurencyjność. Można powiedzieć, że materiał jest szczególną, niepodzielną formą wyrobu, wyrób może natomiast być użyty jako składowa część większego wyrobu. Zatem proponuję utworzyć jedna tabelę dla materiałów i wyrobów. Jeżeli potrzebne jest przechowywanie informacji historycznych, najlepiej zaimplementować to w tej samej tabeli z uwagi na tą samą strukturę danych, wystarczy dodać datę zmiany, a klucz główny rozszerzyć o tą właśnie datę:

WYROB

id PK
data PK
nazwa
......

Dopisując rekord (INSERT) powinien zostać stworzony nowy numer id na potrzeby klucza głównego (sekwencja, autoincrement, itd), w pole data należy wpisać bieżącą datę i czas z dokładnością na tyle dużą, aby niemożliwa była modyfikacja rekordu częstsza, niż dokładność tego pola, przypuszczalnie wystarczy 1 milisekunda. Modyfikując rekord (UPDATE) należy (najłatwiej za pomocą triggera) zamienić polecenie UPDATE na INSERT tworząc nowy rekord z tym samym ID, lecz nowym czasem w kolumnie data. W ten sposób tabela będzie miała wszystkie wersje rekordów. Aby ułatwić pobieranie w zapytaniach najnowszej obowiązującej wersji rekordu można wprowadzić do tabeli kolumnę NAJNOWSZA typu logicznego co znacznie ułatwi pisanie zapytań SELECT. Stan kolumny NAJNOWSZA powinien zawsze wynosić TRUE dla najnowszej wersji rekordu i FALSE dla wszystkich starszych. Pole NAJNOWSZA może być stosownie modyfikowane przez wcześniej wspominany trigger. Podobny mechanizm można zastosować dla dowolnej innej kolumny dodając do nie pola DATA i ew. NAJNOWSZA, rozszerzając klucz główny z samego pola ID do pary: (ID, DATA). Co ważne - referencje do tabeli mogą nadal być obsługiwane tylko przez pole ID, o ile chodzi nam o najświeższą obowiązującą wersję rekordu.
Ponieważ wyrób może składać się z innych wyrobów (materiałów), te z kolei z dalszych itd. musimy pokazać relację wiele-wielu za pomocą nowej tabeli:

WYROB2WYROB

parent_wyrob_id PK
wyrob_id PK

Tabela zawiera tylko 2 kolumny stanowiące w parze klucz główny. Z pomocą tej tabeli można pokazać całe drzewo budowy dowolnego wyrobu do dowolnej głębokości. Warto napisać trigger zapobiegający "zapętleniu" (recyrkulacji) się drzewa wyrobów, tak aby potomek nie był jednocześnie rodzicem w swojej gałęzi.
Lista materiałów/wyrobów i ich zamienników może wyglądać tak:

ZAMIENNIK

wyrob_id PK
zamiennik_wyrob_id PK

Tabela zawiera tylko 2 kolumny stanowiące w parze klucz główny chroniący jednocześnie swoją unikalnością możliwość zdublowania zamiennika dla wyrobu.
Skład materiałowy będzie taki:

SKLAD

wyrob_id PK
ilosc

Jeśli chodzi o marszrutę technologiczną trzeba dodać pole powiedzmy KOLEJNOSC (integer) która zawierać będzie numer w żądanej kolejności niezależnej od fizycznych rekordów. Pytając możemy zrobić tak: SELECT * FROM sklad WHERE ......... ORDER BY KOLEJNOSC