Cześć! Nazywam się Andrey Semenov i jestem starszym analitykiem w Sportmaster. W tym poście chcę poruszyć kwestię denormalizacji baz danych systemów ERP. Przyjrzymy się ogólnym warunkom, a także konkretnemu przykładowi – powiedzmy, że będzie to wspaniała tawerna Monopoly dla piratów i żeglarzy. W którym to przypadku piratom i żeglarzom należy służyć odmiennie, ponieważ idee piękna i wzorce konsumpcyjne tych dobrych dżentelmenów różnią się znacząco.
Jak zadowolić wszystkich? Jak zachować zdrowy rozsądek projektując i utrzymując taki system? Co zrobić, gdy do tawerny zaczną przychodzić nie tylko zwykli piraci i marynarze?
Wszystko jest pod nacięciem. Ale zróbmy to po kolei.
1. Ograniczenia i założenia
Wszystkie powyższe informacje dotyczą wyłącznie relacyjnych baz danych. Nie uwzględniono skutków denormalizacji w postaci anomalii modyfikacji, usuwania i wstawiania, które są dobrze opisane, także w Internecie. Poza zakresem tej publikacji znajdują się przypadki, w których denormalizacja jest powszechna. Klasycznymi przykładami są: seria i numer paszportu, data i godzina itp.
W poście wykorzystano intuicyjne i praktycznie stosowane definicje postaci normalnych, nie odwołując się do pojęć matematycznych. W formie, w jakiej można je zastosować do badania rzeczywistych procesów biznesowych (BP) i projektowania oprogramowania przemysłowego.
Twierdzono, że konstrukcja magazynów danych, narzędzi do raportowania i umów integracyjnych (które wykorzystują tabelaryczną reprezentację informacji) różni się od konstrukcji baz danych ERP, ponieważ łatwość użytkowania i wykorzystanie celowej denormalizacji w tym celu mogą mieć pierwszeństwo przed ochroną integralności danych. Podzielam tę opinię i to, co opiszę poniżej, dotyczy wyłącznie modeli danych podstawowych i danych transakcyjnych systemów ERP.
Wyjaśnienie form normalnych przedstawiono na przykładzie zrozumiałym dla większości czytelników na co dzień. Jednakże w celach ilustracyjnych w punktach 4-5 celowo użyto zadania wyraźnie „fikcyjnego”. Jeśli tego nie zrobimy i weźmiemy jakiś przykład z podręcznika, np. ten sam model przechowywania zamówień z punktu 2, możemy znaleźć się w sytuacji, w której uwaga czytelnika przesunie się z proponowanego rozkładu procesu na model, na osobiste doświadczenie i percepcję tego, w jaki sposób procesy i modele przechowywania danych powinny być budowane w systemie informacyjnym. Innymi słowy, weźmy dwóch wykwalifikowanych analityków IT, niech jeden będzie świadczył usługi logistykom zajmującym się transportem pasażerów, a drugi logistykom zajmującym się transportem maszyn do produkcji mikroczipów. Poproś ich, nie omawiając wcześniej kwestii zautomatyzowanych punktów odniesienia, o stworzenie modelu danych do przechowywania informacji o podróży pociągiem.
Istnieje niezerowe prawdopodobieństwo, że w proponowanych modelach znajdziemy nie tylko znacząco różniący się zestaw atrybutów, ale także niedopasowane zestawy bytów, ponieważ każdy analityk będzie opierał się na procesach i zadaniach, które są mu znane. A w takiej sytuacji nie sposób powiedzieć, który model jest „słuszny”, bo nie ma kryterium oceny.
2. Formy normalne
Pierwsza normalna forma bazy danych wymaga atomowości wszystkich atrybutów.
W szczególności, jeśli obiekt A ma atrybuty niekluczowe a i b takie, że c=f(a,b), a w tabeli opisującej obiekt A przechowujesz wartość atrybutu c, to pierwsza postać normalna w bazie danych jest naruszona. Na przykład, jeśli specyfikacja zamówienia określa ilość, której jednostki miary zależą od rodzaju produktu: w jednym przypadku mogą to być sztuki, w innym litry, w trzecim opakowanie składające się z sztuk (w powyższym modelu Good_count_WR), wówczas naruszona zostaje atomowość atrybutów w bazie danych. W tym przypadku, aby powiedzieć, jaka powinna być tabela specyfikacji zamówienia, potrzebny jest opis docelowy procesu pracy w systemie informatycznym, a ponieważ procesy mogą być różne, może być wiele „poprawnych” wersji.
Druga normalna forma bazy danych wymaga spełnienia pierwszego formularza i własnej tabeli dla każdego podmiotu powiązanego z procesem pracy w SI. Jeżeli w jednej tabeli występują zależności c=f1(a) i d=f2(b), a nie ma zależności c=f3(b), to w tabeli naruszona jest druga postać normalna. W powyższym przykładzie nie ma żadnego związku pomiędzy zamówieniem i adresem w tabeli Zamówienia. Zmiana nazwy ulicy lub miasta nie będzie miała wpływu na istotne atrybuty zamówienia.
Trzecia normalna forma bazy danych wymaga zgodności z drugą postacią normalną i braku zależności funkcyjnych pomiędzy atrybutami różnych bytów. Zasadę tę można sformułować następująco: „wszystko, co da się obliczyć, musi zostać obliczone”. Innymi słowy, jeśli istnieją dwa obiekty A i B. W tabeli przechowującej atrybuty obiektu A, atrybut C jest zamanifestowany, a obiekt B ma atrybut b taki, że istnieje c=f4(b), to trzecia postać normalna jest naruszona. W poniższym przykładzie atrybut Total_count_WR w rekordzie zamówienia wyraźnie narusza trzecią postać normalną.
3. Moje podejście do stosowania normalizacji
1. Tylko ukierunkowany zautomatyzowany proces biznesowy może dostarczyć analitykowi kryteriów identyfikacji jednostek i atrybutów podczas tworzenia modelu przechowywania danych. Utworzenie modelu procesu jest warunkiem koniecznym do utworzenia normalnego modelu danych.
2. Osiągnięcie trzeciej postaci normalnej w ścisłym tego słowa znaczeniu może nie być praktyczne w praktyce tworzenia systemów ERP, jeżeli spełnione są niektóre lub wszystkie z następujących warunków:
- zautomatyzowane procesy rzadko podlegają zmianom,
- terminy realizacji prac badawczo-rozwojowych są napięte,
- wymagania dotyczące integralności danych są stosunkowo niskie (potencjalne błędy w oprogramowaniu przemysłowym nie prowadzą do utraty pieniędzy lub klientów przez odbiorcę oprogramowania)
- itd.
W opisanych warunkach koszty identyfikacji, opisu cyklu życia niektórych obiektów i ich atrybutów mogą nie być uzasadnione z punktu widzenia efektywności ekonomicznej.
3. Wszelkie konsekwencje denormalizacji modelu danych w już utworzonym systemie informacyjnym można złagodzić poprzez staranne wstępne przestudiowanie kodu i przeprowadzenie testów.
4. Denormalizacja jest sposobem na przeniesienie kosztów pracy z etapu badania źródeł danych i projektowania procesu biznesowego na etap rozwoju, z okresu wdrażania do okresu rozwoju systemu.
5. Zaleca się dążenie do trzeciej postaci normalnej bazy danych, jeżeli:
- Trudno przewidzieć kierunek zmian w automatyzacji procesów biznesowych
- W zespole wdrażającym i/lub rozwojowym występuje słabo przenikalny podział pracy
- Układy wchodzące w skład układu scalonego rozwijają się według własnych planów
- Niespójność danych może spowodować utratę klientów lub pieniędzy przez firmę.
6. Projekt modelu danych powinien być wykonywany przez analityka wyłącznie w powiązaniu z modelami docelowego procesu biznesowego oraz procesu w systemie informatycznym. Jeśli programista projektuje model danych, będzie musiał zagłębić się w dany temat do tego stopnia, aby w szczególności zrozumieć różnicę pomiędzy wartościami atrybutów, co jest warunkiem koniecznym do identyfikacji atrybutów atomowych. Przejmując w ten sposób funkcje, które nie są dla nas typowe.
4 Problem do zilustrowania
Załóżmy, że masz małą, robotyczną tawernę w porcie. Twój segment rynku: żeglarze i piraci, którzy przypływają do portu i potrzebują odpoczynku. Sprzedajesz herbatę tymiankową żeglarzom, a piratom rum i kostne plastry. Obsługę w samej tawernie zapewniają roboty - hostessa i barman - robot. Dzięki wysokiej jakości i niskim cenom wyparłeś całą konkurencję, tak że każdy, kto opuszcza statek, przychodzi do twojej tawerny, która jest jedyną w porcie.
Kompleks systemów informatycznych tawerny składa się z następującego oprogramowania:
- System wczesnego ostrzegania dla klienta rozpoznającego swoją kategorię po cechach charakterystycznych
- System sterowania dla robotów-hostess i robotów-barmanów
- System zarządzania dostawami do magazynu i punktu sprzedaży
- System zarządzania relacjami z dostawcami (SRMS)
Proces:
System wczesnego ostrzegania rozpoznaje osoby opuszczające statek. Jeśli ktoś jest ogolony, nazywa się go żeglarzem, a jeśli ma brodę, nazywa się go piratem.
Po wejściu do tawerny gość słyszy powitanie od robota-gospodyni, zgodnie ze swoją kategorią, np.: „Ho-ho-ho, drogi piracie, podejdź do stolika nr.…”
Gość udaje się do wyznaczonego stolika, gdzie robot-barman przygotował już dla niego towary zgodne z kategorią. Robot-barman przesyła do systemu magazynowego informację o konieczności zwiększenia kolejnej porcji dostawy, a system informatyczny magazynu, na podstawie stanu zapasów pozostałych w magazynie, generuje zamówienie zakupu w systemie SUOP.
Podczas gdy system wczesnego ostrzegania może zostać opracowany przez wewnętrzny dział IT, oprogramowanie do zarządzania robotem prętowym może zostać stworzone przez zewnętrznego wykonawcę specjalnie na potrzeby Twojej firmy. Systemy do zarządzania magazynem i relacjami z dostawcami to gotowe rozwiązania dostępne na rynku.
5. Przykłady denormalizacji i jej wpływ na rozwój oprogramowania
Podczas projektowania procesu biznesowego eksperci w tej dziedzinie jednogłośnie stwierdzili, że na całym świecie piraci piją rum i czeszą brody grzebieniami z kości, a marynarze piją herbatę z tymiankiem i zawsze są gładko ogoleni.
Pojawia się katalog typów klientów z dwiema wartościami: 1 - piraci, 2 - marynarze, wspólnymi dla całego obiegu informacyjnego firmy.
System powiadamiania klientów natychmiast zapisuje wynik przetwarzania obrazu w postaci identyfikatora (ID) rozpoznanego klienta i jego typu: marynarz lub pirat.
Identyfikator rozpoznanego obiektu
Kategoria klienta
100500
Pirat
100501
Pirat
100502
Marynarz
Zauważmy jeszcze raz, że
1. Nasi marynarze to tak naprawdę ogoleni ludzie
2. Nasi piraci są tak naprawdę ludźmi z brodą.
Jakie problemy należy rozwiązać w tym przypadku, aby nasza struktura zmierzała do trzeciej postaci normalnej:
- naruszenie atomowości atrybutu - Kategoria klienta
- mieszanie analizowanego faktu i wniosku w jednej tabeli
- ustalona zależność funkcjonalna między atrybutami różnych jednostek.
W znormalizowanej formie otrzymalibyśmy dwie tabele:
- wynik rozpoznania w postaci zespołu ustalonych cech,
Identyfikator rozpoznanego obiektu
Włosy na twarzy
100500
Tak
100501
Tak
100502
Nie
- wynik określenia typu klienta jako zastosowania logiki osadzonej w IS do interpretacji ustalonych cech
Identyfikator rozpoznanego obiektu
Identyfikacja ID
Kategoria klienta
100500
100001
Pirat
100501
100002
Pirat
100502
100003
Marynarz
W jaki sposób znormalizowana organizacja przechowywania danych może ułatwić rozwój złożonego systemu informatycznego? Załóżmy, że nagle zyskujesz nowych klientów. Niech to będą japońscy piraci, którzy może nie mają brody, ale chodzą z papugą na ramieniu, a piratów-ekologów łatwo rozpoznać po niebieskim profilu Grety na lewej piersi.
Piraci ekologiczni oczywiście nie mogą używać grzebieni z kości i domagają się odpowiednika wykonanego z przetworzonego plastiku morskiego.
Należy przerobić algorytmy programu zgodnie z nowymi danymi wejściowymi. Gdyby przestrzegano reguł normalizacji, wówczas wystarczyłoby uzupełnić dane wejściowe tylko dla niektórych gałęzi procesu w niektórych systemach i utworzyć nowe gałęzie tylko w tych przypadkach i w tych systemach informatycznych, w których zarost ma znaczenie. Ponieważ jednak nie przestrzegano tych reguł, trzeba będzie przeanalizować cały kod, w całym obwodzie, gdzie używane są wartości katalogu typu klienta i jasno ustalić, że w jednym przypadku algorytm powinien uwzględniać aktywność zawodową klienta, a w drugim jego cechy fizyczne.
W takiej formie szuka po znormalizowaniu otrzymalibyśmy dwie tabele z danymi operacyjnymi i dwie książki referencyjne:
- wynik rozpoznania w postaci zespołu ustalonych cech,
Identyfikator rozpoznanego obiektu
Greta na lewej piersi
Ptak na ramieniu
Włosy na twarzy
100510
1
1
1
100511
0
0
1
100512
1
0
- wynik określenia typu klienta (niech będzie to widok niestandardowy, w którym wyświetlane są opisy z książek referencyjnych)
Czy wykryta denormalizacja oznacza, że systemów nie da się modyfikować tak, aby odpowiadały nowym warunkom? Oczywiście, że nie. Jeżeli wyobrazimy sobie, że wszystkie systemy informatyczne zostały stworzone przez jeden zespół, przy zerowej rotacji personelu, zmiany były dobrze udokumentowane, a informacje były przekazywane w obrębie zespołu bez strat, to wymagane zmiany można było wprowadzić przy minimalnym wysiłku. Jeśli jednak wrócimy do pierwotnego stanu problemu, samo wydrukowanie protokołów ze wspólnych dyskusji zajmie 1,5 klawiatury, a kolejne 0,5 zajmie przetwarzanie procedur zamówień publicznych.
W powyższym przykładzie wszystkie trzy postacie normalne są naruszone, spróbujmy je teraz naruszyć pojedynczo.
Naruszenie pierwszej postaci normalnej:
Załóżmy, że towary są dostarczane do Twojego magazynu z magazynów dostawców za pośrednictwem odbioru osobistego, przy użyciu 1.5-tonowej ciężarówki Gazelle należącej do Twojej tawerny. Wielkość Twoich zamówień jest tak mała w porównaniu do obrotów dostawców, że zawsze są one realizowane w takiej formie, w jakiej zostały zrealizowane, bez czekania na produkcję. Czy dla takiego BP potrzebne są osobne tabele: pojazdy, rodzaje pojazdów, czy w zamówieniach od dostawców wychodzących konieczne jest rozdzielenie planu i faktu?
Wyobraź sobie, ile „dodatkowych” połączeń będą musieli napisać Twoi programiści, jeśli do napisania programu użyjesz poniższego modelu.
Załóżmy, że uznaliśmy proponowaną strukturę za zbyt skomplikowaną, gdyż rozdzielenie planu i faktu w rekordzie zamówienia jest informacją zbędną, a wygenerowana specyfikacja zamówienia przepisywana jest na podstawie wyników akceptacji przybyłych towarów, rzadkie błędne sortowanie oraz rozliczanie przybycia towarów o nieodpowiedniej jakości poza IS.
Aż pewnego dnia widzisz, jak cała sala tawerny wypełnia się oburzonymi i zaniedbanymi piratami. Co się stało?
Okazało się, że wraz ze wzrostem firmy rosła również konsumpcja. W pewnym momencie zarząd podjął decyzję, że jeśli Gazelle zostanie przeciążona pod względem objętości i/lub wagi, co zdarzało się niezwykle rzadko, dostawca będzie priorytetowo traktował załadunek napojów.
Niedostarczone towary zostały uwzględnione w kolejnym zamówieniu i wysłane w nową trasę; obecność minimalnego salda w magazynie w karczmie powodowała, że nie zauważano przypadków wyłudzeń.
Ostatni konkurent w porcie został zamknięty, a wyeliminowany przypadek przeciążenia Gazeli, pominięty dzięki priorytetyzacji opartej na założeniu wystarczalności minimalnego salda i okresowemu niedociążaniu pojazdu, stał się powszechną praktyką. Stworzony system będzie działał idealnie zgodnie z wbudowanymi w niego algorytmami i nie będzie miał możliwości śledzenia systematycznych niewypełnień zaplanowanych zamówień. Tylko nadszarpnięta reputacja i niezadowoleni klienci ujawnią problem.
Uważny czytelnik zauważy zapewne, że zamówiona ilość w specyfikacji zamówienia (T_ORDER_SPEC) w rozdziale 2 i 5 może spełniać lub nie spełniać pierwszego wymogu postaci normalnej. Wszystko zależy od tego, czy przy wybranym asortymencie produktów możliwe jest uwzględnienie w tym samym polu zasadniczo różnych jednostek miary.
Naruszenie drugiej postaci normalnej:
W miarę wzrostu Twoich potrzeb możesz zakupić kilka pojazdów o różnych rozmiarach. W powyższym kontekście uznano, że tworzenie katalogu pojazdów jest zbędne, w efekcie czego wszystkie algorytmy przetwarzania danych obsługujące potrzeby dostaw i magazynowania postrzegają przemieszczanie się ładunku od dostawcy do magazynu jako przejazd wyłącznie 1,5-tonową Gazelą. Kiedy więc kupujesz nowe pojazdy, tworzysz katalog pojazdów, ale kiedy go udoskonalasz, musisz przeanalizować cały kod odnoszący się do przewozu ładunków, aby sprawdzić, czy każda konkretna lokalizacja implikuje odniesienia do charakterystyki konkretnego pojazdu, od którego rozpoczęła się działalność.
Naruszenie trzeciej postaci normalnej:
W pewnym momencie zaczynasz tworzyć program lojalnościowy i pojawia się rejestr stałych klientów. Po co na przykład poświęcać czas na tworzenie reprezentacji materiałowych, które przechowują zagregowane dane dotyczące sprzedaży do indywidualnego klienta na potrzeby raportowania i przesyłania do systemów analitycznych, skoro na etapie uruchamiania programu lojalnościowego wszystko, co interesuje klienta, można umieścić w jego własnych danych? I rzeczywiście, na pierwszy rzut oka nie ma to sensu. Ale za każdym razem, gdy Twoja firma będzie włączać na przykład nowe kanały sprzedaży, wśród Twoich analityków powinna znaleźć się osoba, która przypomni sobie o istnieniu takiego atrybutu agregacji.
Projektując każdy nowy proces, na przykład sprzedaż przez Internet, sprzedaż za pośrednictwem dystrybutorów podłączonych do wspólnego systemu lojalnościowego, ktoś musi pamiętać, że wszystkie nowe procesy muszą zapewniać integralność danych na poziomie kodu. W przypadku przemysłowej bazy danych zawierającej tysiące tabel wydaje się to zadaniem niemożliwym.
Doświadczony programista oczywiście wie, jak złagodzić wszystkie problemy wymienione powyżej, lecz moim zdaniem zadaniem doświadczonego analityka jest niedopuszczenie do tego punktu.
Chciałbym wyrazić swoją wdzięczność głównemu programiście Jewgienijowi Jaruchinowi za cenne uwagi, jakie przekazał mi podczas przygotowywania tej publikacji.
literatura
Connolly Thomas, błagaj Karolinę. Bazy danych. Projektowanie, wdrażanie i wsparcie. Teoria i praktyka
Źródło: www.habr.com
