Cześć! Nazywam się Andrey Semenov i jestem starszym analitykiem w Sportmaster. W tym poście chcę poruszyć kwestię denormalizacji baz danych systemu ERP. Przyjrzymy się warunkom ogólnym, a także konkretnemu przykładowi - powiedzmy, że byłaby to wspaniała tawerna monopolowa dla piratów i żeglarzy. W którym inaczej trzeba obsługiwać piratów i marynarzy, bo idee piękna i wzorce konsumenckie tych dobrych panów znacząco się różnią.
Jak uszczęśliwić wszystkich? Jak uniknąć szaleństwa projektując i utrzymując taki system? Co zrobić, jeśli do tawerny zaczną przychodzić nie tylko zwykli piraci i marynarze?
Wszystko jest pod cięciem. Ale chodźmy po kolei.
1. Ograniczenia i założenia
Wszystko powyższe dotyczy wyłącznie relacyjnych baz danych. Konsekwencje denormalizacji w postaci anomalii modyfikacji, usuwania i wstawiania, które są dobrze opisane, w tym w Internecie, nie są brane pod uwagę. Poza zakresem tej publikacji istnieją przypadki, w których denormalizacja jest zjawiskiem powszechnym, z klasycznymi przykładami: seria i numer paszportu, data i godzina itp.
W poście zastosowano intuicyjne i praktycznie obowiązujące definicje postaci normalnych, bez odwoływania się do terminów matematycznych. W formie, w jakiej można je zastosować do badania rzeczywistych procesów biznesowych (BP) i projektowania oprogramowania przemysłowego.
Argumentuje się, że projektowanie hurtowni danych, narzędzi raportowania i umów integracyjnych (które wykorzystują tabelaryczne reprezentacje informacji) różni się od projektowania baz danych systemu ERP tym, że łatwość ich wykorzystania i zastosowanie świadomej denormalizacji w celu jej osiągnięcia może mieć pierwszeństwo przed integralnością dane ochrony. Podzielam tę opinię i to, co opisano poniżej, dotyczy wyłącznie modeli danych podstawowych i transakcyjnych systemów ERP.
Wyjaśnienie form normalnych podano na przykładzie zrozumiałym na poziomie codziennym dla większości czytelników. Jednakże, jako ilustrację wizualną, w akapitach 4-5 celowo wykorzystano zadanie „fikcyjne”. Jeśli tego nie zrobisz i weźmiesz jakiś podręcznikowy przykład, na przykład ten sam model przechowywania zamówień z punktu 2, możesz znaleźć się w sytuacji, w której uwaga czytelnika zostanie przesunięta z proponowanej dekompozycji procesu na model, do osobistego doświadczenia i postrzegania tego, w jaki sposób należy budować procesy i modele przechowywania danych w IS. Inaczej mówiąc, weźmy dwóch wykwalifikowanych analityków IT, jeden niech obsługuje logistyków przewożących pasażerów, drugi logistyków przewożących maszyny do produkcji mikroczipów. Poproś ich, bez wcześniejszego omawiania automatycznych BP, o utworzenie modelu danych do przechowywania informacji o podróży koleją.
Istnieje niezerowe prawdopodobieństwo, że w proponowanych modelach znajdziesz nie tylko zauważalnie odmienny zestaw atrybutów, ale także rozbieżne zbiory bytów, gdyż każdy analityk będzie opierał się na znanych mu procesach i zadaniach. I w takiej sytuacji nie da się stwierdzić, który model jest „właściwy”, bo nie ma kryterium oceny.
2. Formy normalne
Pierwsza normalna postać bazy danych wymaga atomowości wszystkich atrybutów.
W szczególności, jeśli obiekt A ma niekluczowe atrybuty aib takie, że c=f(a,b) i w tabeli opisującej obiekt A zapiszesz wartość atrybutu c, to w bazie danych zostanie naruszona pierwsza postać normalna . Przykładowo, jeśli w specyfikacji zamówienia wskazana jest ilość, której jednostki miary zależą od rodzaju produktu: w jednym przypadku mogą to być sztuki, w innym litry, w trzecim opakowania składające się z sztuk (w powyższym modelu Good_count_WR) , wówczas w bazie danych zostaje naruszona atomowość atrybutów. W tym przypadku, aby powiedzieć, jaki powinien być zbiór tabel specyfikacji zamówienia, potrzebny jest ukierunkowany opis procesu pracy w IS, a ponieważ procesy mogą być różne, „poprawnych” wersji może być wiele.
Druga postać normalna bazy danych wymaga zgodności z pierwszym formularzem i własną tabelą dla każdego podmiotu związanego z procesem pracy w IS. Jeśli w jednej tabeli występują zależności c=f1(a) i d=f2(b) i nie ma zależności c=f3(b), to w tabeli naruszona jest druga postać normalna. W powyższym przykładzie nie ma zależności pomiędzy zamówieniem a adresem w tabeli Order. Zmień nazwę ulicy lub miasta, a nie będziesz mieć wpływu na istotne atrybuty zamówienia.
Trzecia baza danych w postaci normalnej wymaga zachowania drugiej postaci normalnej i braku zależności funkcjonalnych pomiędzy atrybutami różnych bytów. Zasadę tę można sformułować w następujący sposób: „wszystko, co można obliczyć, należy obliczyć”. Innymi słowy, jeśli istnieją dwa obiekty A i B. W tablicy przechowującej atrybuty obiektu A manifestuje się atrybut C, a obiekt B ma atrybut b tak, że istnieje c=f4(b), to trzecia postać normalna jest naruszony. W poniższym przykładzie atrybut Ilość sztuk (Total_count_WR) w rekordzie zamówienia wyraźnie wskazuje na naruszenie trzeciej postaci normalnej
3. Moje podejście do stosowania normalizacji
1. Tylko docelowy zautomatyzowany proces biznesowy może zapewnić analitykowi kryteria identyfikacji jednostek i atrybutów podczas tworzenia modelu przechowywania danych. Utworzenie modelu procesu jest warunkiem wstępnym utworzenia normalnego modelu danych.
2. Osiągnięcie trzeciej postaci normalnej sensu stricto może nie być praktyczne w praktyce tworzenia systemów ERP, jeśli zostaną spełnione niektóre lub wszystkie z poniższych warunków:
- zautomatyzowane procesy rzadko ulegają zmianom,
- terminy 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 ani klientów przez klienta oprogramowania)
- itd.
W opisanych warunkach koszty identyfikacji i opisu cyklu życia niektórych obiektów oraz ich atrybutów mogą nie być uzasadnione z punktu widzenia efektywności ekonomicznej.
3. Wszelkie konsekwencje denormalizacji modelu danych w już utworzonym IS można złagodzić poprzez dokładne wstępne przestudiowanie kodu i przetestowanie.
4. Denormalizacja to sposób na przeniesienie kosztów pracy z etapu badania źródeł danych i projektowania procesu biznesowego na etap rozwoju, z okresu wdrożenia na okres rozwoju systemu.
5. Wskazane jest dążenie do trzeciej postaci normalnej bazy danych, jeśli:
- Kierunek zmian w zautomatyzowanych procesach biznesowych jest trudny do przewidzenia
- Istnieje słaby podział pracy w zespole wdrożeniowym i/lub programistycznym
- Systemy zawarte w obwodzie integracyjnym rozwijają się według własnych planów
- Niespójność danych może skutkować 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 i procesu w IS. Jeśli programista projektuje model danych, będzie musiał zanurzyć się w tematyce do tego stopnia, aby w szczególności zrozumieć różnicę pomiędzy wartościami atrybutów – warunek konieczny wyodrębnienia atrybutów atomowych. Tym samym przejmuje niezwykłe funkcje.
4 Problem ilustracyjny
Załóżmy, że masz w porcie małą tawernę z robotami. Twój segment rynku: marynarze i piraci, którzy przybywają do portu i potrzebują odpoczynku. Sprzedajesz marynarzom herbatę z tymiankiem, a piratom rum i kościane grzebienie do czesania brody. W samej tawernie obsługę zapewnia hostessa-robot i barman-robot. Dzięki swojej wysokiej jakości i niskim cenom wypędziliście konkurencję, tak że każdy schodzący ze statku trafia do Waszej tawerny, która jest jedyną w porcie.
Kompleks systemów informatycznych tawerny składa się z następującego oprogramowania:
- System wczesnego ostrzegania o kliencie, który rozpoznaje swoją kategorię na podstawie charakterystycznych cech
- System sterowania dla robotycznych hostess i robotycznych barmanów
- System zarządzania magazynem i dostawami do punktu sprzedaży
- System zarządzania relacjami z dostawcami (SURP)
Proces:
System wczesnego ostrzegania rozpoznaje osoby opuszczające statek. Jeśli ktoś jest gładko ogolony, rozpoznaje go jako marynarza, jeśli okaże się, że ma brodę, rozpoznaje się go jako pirata.
Wchodząc do tawerny, gość słyszy powitanie od gospodyni robota zgodne ze swoją kategorią, np.: „Ho-ho-ho, drogi piracie, idź do stolika nr…”
Gość podchodzi do wskazanego stołu, gdzie robot-barman przygotował już dla niego towar zgodnie z kategorią. Robot-barman przekazuje do systemu magazynowego informację o konieczności zwiększenia kolejnej porcji dostawy, a IS magazynu na podstawie pozostałych sald na magazynie generuje w systemie zarządzającym żądanie zakupu.
Chociaż system wczesnego ostrzegania mógł zostać opracowany przez wewnętrzny dział IT, program zarządzania robotem barowym mógł zostać stworzony przez zewnętrznego wykonawcę specjalnie dla Twojej firmy. A systemy do zarządzania magazynami i relacjami z dostawcami to spersonalizowane, pakietowe rozwiązania z rynku.
5. Przykłady denormalizacji i jej wpływ na rozwój oprogramowania
Projektując proces biznesowy, ankietowani znawcy tematu zgodnie stwierdzili, że na całym świecie piraci piją rum i czeszą brody grzebieniem kostnym, 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ólne dla całego obiegu informacyjnego firmy.
System powiadamiania klienta natychmiast zapisuje wynik przetwarzania obrazu jako identyfikator (ID) rozpoznanego klienta i jego typ: marynarz lub pirat.
Rozpoznany identyfikator obiektu
Kategoria klienta
100500
Pirat
100501
Pirat
100502
Marynarz
Zauważmy to jeszcze raz
1. Nasi marynarze to tak naprawdę ogoleni ludzie
2. Nasi piraci to tak naprawdę brodaci
Jakie problemy w tym przypadku należy wyeliminować, aby nasza konstrukcja dążyła do trzeciej postaci normalnej:
- naruszenie niepodzielności atrybutu — kategoria klienta
- mieszanie analizowanego faktu i wniosków w jednej tabeli
- ustalony związek funkcjonalny pomiędzy atrybutami różnych bytów.
W znormalizowanej formie otrzymalibyśmy dwie tabele:
- wynik rozpoznania w postaci zestawu ustalonych cech,
Rozpoznany identyfikator obiektu
Zarost
100500
Tak
100501
Tak
100502
Nie
- wynik określenia typu klienta jako zastosowanie logiki wbudowanej w IS do interpretacji ustalonych cech
Rozpoznany identyfikator obiektu
Identyfikator identyfikacyjny
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 kompleksu IP? Załóżmy, że nagle zyskujesz nowych klientów. Niech będą to japońscy piraci, którzy może nie mają brody, ale chodzą z papugą na ramieniu, oraz piraci ekologiczni, łatwo ich rozpoznać po niebieskim profilu Grety na lewej piersi.
Piraci ekologiczni oczywiście nie mogą używać grzebieni kostnych i żądają analogu wykonanego z przetworzonego plastiku morskiego.
Należy przerobić algorytmy programu zgodnie z nowymi danymi wejściowymi. Gdyby przestrzegano zasad normalizacji, wystarczyłoby w niektórych systemach uzupełnić dane wejściowe dla niektórych gałęzi procesów i utworzyć nowe gałęzie tylko dla tych przypadków i tych IS, gdzie zarost ma znaczenie. Ponieważ jednak zasady nie były przestrzegane, będziesz musiał przeanalizować cały kod, w całym obwodzie, w którym używane są wartości katalogu typu klienta i jasno ustalić, że w jednym przypadku algorytm powinien uwzględniać profesjonalną działalności klienta oraz w innych cechach fizycznych.
W takiej formie szuka do znormalizowania otrzymalibyśmy dwie tabele z danymi operacyjnymi i dwa katalogi:
- wynik rozpoznania w postaci zestawu ustalonych cech,
Rozpoznany identyfikator obiektu
Greta na lewej piersi
Ptak na ramieniu
Zarost
100510
1
1
1
100511
0
0
1
100512
1
0
- wynik określenia typu klienta (niech będzie to niestandardowy widok, w którym wyświetlane są opisy z katalogów)
Czy wykryta denormalizacja oznacza, że systemów nie można modyfikować tak, aby spełniały nowe warunki? Oczywiście nie. Jeśli wyobrazimy sobie, że wszystkie systemy informatyczne zostały stworzone przez jeden zespół przy zerowej rotacji personelu, zmiany są dobrze udokumentowane, a informacje przekazywane są w obrębie zespołu bez strat, to wymagane zmiany można wprowadzić znikomo małym wysiłkiem. Ale jeśli powrócimy do pierwotnych warunków problemu, 1,5 klawiatury zostanie wymazane tylko w celu drukowania protokołów wspólnych rozmów i kolejne 0,5 w celu przetwarzania postępowań o udzielenie zamówienia.
W powyższym przykładzie naruszone są wszystkie trzy formy normalne, spróbujmy je naruszyć osobno.
Naruszenie pierwszej postaci normalnej:
Załóżmy, że towary są dostarczane do Twojego magazynu z magazynów dostawców poprzez odbiór przy użyciu jednej 1.5-tonowej gazeli należącej do Twojej tawerny. Wielkość Twoich zamówień jest na tyle mała w stosunku do obrotów dostawców, że zawsze realizujemy je jednostkowo, bez czekania na produkcję. Potrzebujesz osobnych tabel z takim procesem biznesowym: pojazdy, typy pojazdów, czy w zamówieniach dla odchodzących dostawców konieczne jest oddzielenie planu i faktów?
Wyobraź sobie, ile „dodatkowych” połączeń będą musieli napisać twoi programiści, jeśli do opracowania programu użyjesz poniższego modelu.
Załóżmy, że uznaliśmy, że proponowana struktura jest niepotrzebnie skomplikowana, w naszym przypadku oddzielenie planu od faktu w zapisie zamówienia jest informacją zbędną, a wygenerowana specyfikacja zamówienia jest przepisana na podstawie wyników przyjęcia dostarczonego towaru, rzadko zdarzają się pomyłki -klasyfikacja i przybycie towarów o nieodpowiedniej jakości rozliczane są poza granicami IS.
A potem pewnego dnia widzisz, jak cała sala tawerny wypełniona jest oburzonymi i zaniedbanymi piratami. Co się stało?
Okazuje się, że wraz z rozwojem Twojej firmy rosła także Twoja konsumpcja. Dawno, dawno temu podjęto decyzję kierowniczą, że w przypadku przeciążenia gazeli pod względem objętości i/lub wagi, co zdarzało się niezwykle rzadko, dostawca priorytetowo traktował ładunek na rzecz napojów.
Niedostarczony towar trafiał do kolejnego zamówienia i odlatywał nowym lotem, obecność minimalnego salda na magazynie w tawernie sprawiała, że nie można było zauważyć brakujących skrzynek.
Ostatni konkurent zamknął się w porcie, a przebity przypadek przeciążenia gazeli, omijany przez ustalanie priorytetów w oparciu o założenie wystarczalności salda minimalnego i okresowe niedociążenie pojazdu, stał się powszechną praktyką. Stworzony system będzie idealnie działał zgodnie z wbudowanymi w niego algorytmami i będzie pozbawiony jakiejkolwiek możliwości śledzenia systematycznego braku realizacji zaplanowanych zamówień. Tylko nadszarpnięta reputacja i niezadowoleni klienci będą w stanie wykryć problem.
Uważny czytelnik mógł zauważyć, że zamawiana ilość w specyfikacji zamówienia (T_ORDER_SPEC) w sekcji 2 i sekcji 5 może, ale nie musi, spełniać wymaganie pierwszej postaci normalnej. Wszystko zależy od tego, czy przy wybranym asortymencie towarów zasadniczo różne jednostki miary mogą należeć do tego samego zakresu.
Naruszenie drugiej postaci normalnej:
W miarę wzrostu Twoich potrzeb dokupujesz jeszcze kilka pojazdów o różnych rozmiarach. W powyższym kontekście utworzenie katalogu pojazdów uznano za zbędne, w efekcie wszystkie algorytmy przetwarzania danych służące potrzebom dostaw i magazynu postrzegają ruch ładunku od dostawcy do magazynu jako przelot wyłącznie 1,5-tonowego pojazdu gazela. Zatem wraz z zakupem nowych pojazdów nadal tworzysz katalog pojazdów, ale po jego finalizacji będziesz musiał przeanalizować cały kod odnoszący się do ruchu ładunku, aby dowiedzieć się, czy w każdym konkretnym miejscu znajdują się odniesienia do cech samego pojazdu, od którego rozpoczęła się działalność.
Naruszenie trzeciej postaci normalnej:
W pewnym momencie zaczynasz tworzyć program lojalnościowy, pojawia się zapis stałego klienta. Po co np. tracić czas na tworzenie widoków materiałowych przechowujących zagregowane dane dotyczące sprzedaży do indywidualnego klienta do wykorzystania w raportowaniu i przekazywaniu do systemów analitycznych, skoro na starcie programu lojalnościowego można umieścić w kartotece klienta wszystko, co interesuje klienta ? I rzeczywiście, na pierwszy rzut oka, nie ma sensu. Ale za każdym razem, gdy Twój biznes łączy np. nowe kanały sprzedaży, wśród Twoich analityków powinien znaleźć się ktoś, kto będzie pamiętał, że istnieje taki atrybut agregacji.
Projektując każdy nowy proces, np. sprzedaż w Internecie, sprzedaż za pośrednictwem dystrybutorów podłączonych do wspólnego systemu lojalnościowego, trzeba pamiętać, że wszystkie nowe procesy muszą zapewniać integralność danych na poziomie kodu. W przypadku przemysłowej bazy danych zawierającej tysiąc tabel wydaje się to zadaniem niemożliwym.
Doświadczony programista oczywiście wie, jak zaradzić wszystkim wymienionym powyżej problemom, jednak moim zdaniem zadaniem doświadczonego analityka nie jest ich ujawnianie.
Chciałbym wyrazić wdzięczność wiodącemu deweloperowi Evgeniyowi Yarukhinowi za jego cenne uwagi podczas przygotowywania publikacji.
literatura
Connolly Thomas, Begg Caroline. Baza danych. Projektowanie, wdrażanie i wsparcie. Teoria i praktyka
Źródło: www.habr.com