🥇Денормализация на бази данни за ERP-системи и нейното влияние върху развитието на софтуера: отваряме таверна на Тортуга

Здравейте! Аз съм Андрей Семенов, старши аналитик в Спортмастер. В този пост бих искал да повдигна въпроса за денормализацията на базите данни в ERP системите. Ще разгледаме общите условия, както и конкретен пример - да кажем, че това ще бъде прекрасна монополна таверна за пирати и моряци. В която пиратите и моряците трябва да бъдат обслужвани различно, тъй като представите им за красота и потребителските им навици значително се различават.

Как да постигнем всичките да бъдат доволни? Как да не полудея, докато проектирам и поддържам такава система? Какво да правя, ако в таверната започнат да идват не само познатите пирати и моряци?

Всичко е под кат. Но нека започнем по ред.

1. Ограничения и допускания

Всичко изложено се отнася само за релационни бази данни. Добре описаните, включително в интернет, последици от денормализацията под формата на аномалии при модификация, изтриване и вмъкване не се разглеждат. Извън рамките на публикацията остават случаи, когато денормализацията е общоизвестна, с класически примери: серия и номер на паспорт, дата и час и т.н.

В поста се използват интуитивно разбираеми и практически приложими определения на нормалните форми, без позовавания на математически термини. В такъв вид, в който те могат да бъдат приложени към проучвания на реални бизнес-процеси и проектиране на индустриален софтуер.

Съществува мнение, че проектирането на хранилища за данни, инструменти за генериране на отчети и интеграционни споразумения (в които се използва таблично представяне на информация) се различава от проектирането на бази данни на ERP системи, като удобството на потребление и прилагането на осъзнатата денормализация може да има приоритет пред защитата на целостта на данните. Аз споделям това мнение, и написаното по-долу се отнася единствено до моделите на мастер-данни и транзакционни данни на ERP системите.

Обяснението на нормалните форми е представено с пример, разбираем за повечето читатели на битово ниво. Въпреки това, за наочна илюстрация, в пунктовете 4-5 умишлено е използвана подчертано „измислена“ задача. Ако не се направи това и се вземе някой хрестоматийния пример, например същия модел на съхранение на поръчки от т. 2, може да се окаже, че вниманието на читателя е отклонено от предложеното разложение на процеса в модел към личния опит и възприятие за това как трябва да бъдат изградени процесите и моделите за съхранение на данни в ИС. С други думи, вземете двама квалифицирани ИТ аналитика, един да обслужва логисти, превозващи пътници, а друг — логисти, превозващи машини за производство на микрочипове. Помолете ги, без да обсъждат предварително автоматизируемите БП, да съставят модел на данни за съхранение на информация за жп-рейса.

Съществува ненулева вероятност, че в предложените модели ще намерите не само значително различен набор от атрибути, но и несъвпадащи набори от същности, тъй като всеки анализатор ще се опре на познатите си процеси и задачи. И в такава ситуация е невъзможно да се каже коя модель е „правилна“, тъй като няма критерий за оценка.

2. Нормални форми

Първа нормална форма на БД изисква атомарност на всичките атрибути.
По-специално, ако обект A има неключови атрибути a и b, така че c=f(a,b) и в таблицата, описваща обект A, съхранявате стойността на атрибута c, то в БД е нарушена първата нормална форма. Например, ако в спецификацията на поръчката се посочва количество, единиците за измерване на което зависят от вида на продукта: в един случай това могат да бъдат бройки, в друг литри, в трети опаковки, състоящи се от бройки (в модела по-горе Good_count_WR), то в БД е нарушена атомарността на атрибутите. В такъв случай, за да се определи каква трябва да бъде структурата на таблиците в спецификацията на поръчката, е нужно целевото описание на процеса на работа в ИС, а тъй като процесите могат да бъдат различни, то и „правилните“ версии могат да бъдат много.

Втора нормална форма на БД изисква спазването на първата форма и собствена таблица за всяка сущност, свързана с процеса на работа в ИС. Ако в една таблица съществуват зависимости с=f1(a) и d=f2(b) и не съществува зависимост с=f3(b), то в таблицата е нарушена втората нормална форма. В примера по-горе в таблицата "Поръчка" не съществува зависимост между поръчката и адреса. Променете името на улицата или града, и няма да получите никакво влияние върху съществени атрибути на поръчката.

Трета нормална форма на БД изисква спазването на втората нормална форма и отсъствие на функционални зависимости между атрибутите на различни сущности. Това правило може да бъде формулирано така: "всичко, което може да бъде изчислено, трябва да бъде изчислено". С други думи, ако съществуват два обекта A и B. В таблицата, съ храняща атрибутите на обекта A, е проявен атрибут С, при обекта B съществува атрибут b, такъв, че съществува c=f4(b), то нарушена е третата нормална форма. В приведеното по-долу примера атрибутът "Количество парчета" (Total_count_WR) в записа за поръчка очевидно претендира за нарушение на третата нормална форма.

3. Моят подход към прилагането на нормализация

1. Само конкретният автоматизируем бизнес процес може да осигури на аналитика критерии за идентификация на сущности и атрибути при създаването на модел за съхранение на данни. Създаването на модел на процеса е задължително условие за формирането на нормален модел на данни.

2. Постигането на третата нормална форма в строг смисъл може да не е целесъобразно в реалната практика за създаване на ERP системи при изпълнението на част от или всички следните условия:

автоматизируемите процеси рядко са подложени на промени,
сроковете за изследване и разработка са стиснати,
изискванията към целостта на данните условно не са високи (потенциални грешки в промишлен софтуер не водят до загуба на пари или клиенти от страна на поръчителя на софтуера)
и т.н.

При описаните условия разходите за идентификация, описание на жизнения цикъл на определени обекти и техните атрибути могат да бъдат неуместни от гледна точка на икономическата ефективност.

3. Всякакви последствия от денормализацията на модела на данни в вече създадена ИС могат да бъдат прекратени чрез внимателно предварително изследване на кода и тестване.

4. Денормализация — начин да се преместят трудозатратите от етапа на проучване на източниците на данни и проектиране на бизнес процеса към етапа на разработка, от периода на внедряване към периода на развитие на системата.

5. Целесъобразно е да се стремим към третата нормална форма на БД, ако:

Посоката на промяна на автоматизираните бизнес процеси трудно може да се предвиди.
В рамките на екипа по внедряване и/или развитие има слабо проникващо разделение на труда.
Системите, включени в интеграционния контур, се развиват по собствени планове.
Несъответствието на данните може да доведе до загуба на клиенти или пари за компанията.

6. Проектирането на модела на данни трябва да се извършва от аналитик само във връзка с моделите на целевия бизнес процес и процеса в ИС. Ако проектирането на модела на данни се извършва от разработчик, той ще трябва да се потопи в предметната област до такава степен, че, в частност, да разбере разликата между стойностите на атрибутите — необходимо условие за извличане на атомарни атрибути. Така той поема функции, които не му принадлежат.

4 Задача за илюстрация

Да предположим, че имате малка роботизирана таверна в пристанището. Вашият пазарен сегмент: моряци и пирати, които влизат в порта и имат нужда от почивка. На моряците предлагате чай с чабрец, а на пиратите — ром и костяни гребени за разресване на брадите. Обслужването в самата таверна се състои от робот-хостес и робот-барман. Благодарение на високото качество и ниските цени сте изгонили всички конкуренти, така че всеки, който слиза от кораба, идва в вашата таверна, която е единствената в пристанището.

Комплексът от информационни системи на таверната се състои от следния софтуер:

Система за ранно предупреждение за клиента, разпознаваща неговата категория по характерни признаци.
Система за управление на роботите-хостес и роботите-бармани.
Система за управление на склада и доставката до точката на продажба.
Система за управление на отношенията с доставчиците (СУОП).

Процес:

Системата за ранно предупреждение разпознава хората, слизащи от кораба. Ако човекът е гладко избръснат, тя го определя като моряк, а ако има брада, се определя като пират.

Като влезе в таверната, гостът чува от робот-хостес приветствие в зависимост от своята категория, например: «Хо-хо-хо, уважаеми пират, моля, преминете към маса №…»

Гостът преминава до посочената маса, където робот-барманът вече е подготвил стоки за него в съответствие с категорията. Робот-барманът предава информация на складовата система, че следващата партида доставка трябва да бъде увеличена, а складовата ИС на базата на остатъците в хранилището създава заявка за закупуване в СУОП.

Нека системата за ранно предупреждение да е разработена от вашия вътрешен ИТ, а програмата за управление на барменските роботи да е създадена от външен изпълнител специално за вашия бизнес. А системите за управление на склада и отношенията с доставчиците са персонализирани пакетни решения от пазара.

5. Примери за денормализация и нейното влияние върху развитието на софтуера

При проектирането на бизнес процеса, анкетирани експерти от предметната област единодушно заявиха, че по целия свят пиратите пият ром и разчесват брадите си с костени гребени, а моряците пият чай с чабер и винаги са гладко избръснати.

Появява се справочник за типовете клиенти с две стойности: 1 - пирати, 2 - моряци, общ за целия информационен контур на компанията.

Системата за уведомяване за клиента веднага запазва резултата от обработката на изображението като идентификатор (ИД) на разпознатия клиент и неговия тип: моряк или пират.

ИД на разпознатия обект
Категория на клиента

100500
Пират

100501
Пират

100502
Моряк

Още веднъж да обърнем внимание, че

1. Нашите моряци всъщност са избръснати хора
2. Нашите пирати всъщност са брадати хора

Какви проблеми трябва да бъдат решени в този случай, за да се стремим нашата структура към третата нормална форма:

нарушение на атомарността на атрибута — Категория на клиента
смесване на анализирания факт и извод в една таблица
фиксирана функционална зависимост между атрибутите на различни субекти.

В нормализиран вид бихме получили две таблици:

резултат от разпознаването под формата на набор от установени признаци,

ИД на разпознатия обект
Космена покривка на лицето

100500
Да

100501
Да

100502
Не

резултат от определянето на типа клиент като приложение на вградената в ИС логика за интерпретация на установените признаци

ИД на разпознатия обект
ИД на идентификация
Категория на клиента

100500
100001
Пират

100501
100002
Пират

100502
100003
Моряк

Как нормализирана организация за съхранение на данни може да улесни развитието на комплекса от ИС? Да предположим, че изведнъж имате нови клиенти. Нека да кажем, че това са японски пирати, които нямат брада, но носят папагай на рамото, както и пират-еколог, лесно ще ги разпознаете по синьото изображение на Грета на лявата си гърда.

Пиратите-еколози, разбира се, не могат да ползват костени гребени и изискват аналог от рециклирана морска пластмаса.

Необходимо е да преработите алгоритмите на програмите в съответствие с новите насоки. Ако правилата за нормализация бяха спазени, то щяхте да трябва само да добавите входове за някои процеси в част от системите и да създадете нови клони само за тези случаи и в тези ИС, където е важно окосмяването на лицето. Но, тъй като правилата не бяха спазени, ще трябва да анализирате целия код, в целия контур, където се използват стойностите от справочника за типове клиенти и да определите недвусмислено, че в единия случай алгоритмът трябва да вземе предвид професионалната дейност на клиента, а в другия физическите особености.

В формата, в която се стреми към нормализирания, получаваме две таблици с оперативни данни и два справочника:

резултат от разпознаването под формата на набор от установени признаци,

ИД на разпознатия обект
Грета на лявата гърда
Птица на рамото
Космена покривка на лицето

100510
1
1
1

100511
0
0
1

100512

1
0

резултат от определянето на типа клиент (нека да бъде потребителско представяне, в което са изведени описанията от справочниците)

Означава ли откритата денормализация, че системите не могат да бъдат адаптирани към новите условия? Разбира се, че не. Ако предположим, че всички ИС са създавани от един екип с нулева текучка на кадри, документацията е добре написана и информацията в екипа се предава без загуби, то необходимите промени могат да бъдат направени с пренебрежимо малки усилия. Но ако се върнем към началните условия на задачата, само за отпечатването на протоколите от съвместните обсъждания ще се износят 1.5 клавиатури и още 0.5 за оформлението на процедурите за поръчка.

В горния пример са нарушени всички три нормални форми, да опитаме да ги нарушим по отделно.

Нарушение на първата нормална форма:

Да предположим, че стоките на вашия склад се доставят от складовете на доставчиците с товарен автомобил, собственост на вашата таверна, с капацитет 1.5 тона. Размерът на вашите заявки е толкова малък в сравнение с оборота на доставчиците, че те се изпълняват винаги еднакво, без да се чака производство. Нужни ли са при такъв бизнес процес отделни таблици: превозни средства, видове превозни средства, нужно ли е да се разделят планът и фактът в вашите поръчки, изпратени на доставчиците?

Представете си колко "излишни" връзки ще трябва да напишат вашите програмисти, ако за разработката на програмата бъде използвана следната структура.

Да предположим, че взехме решение, че предложената структура е прекалено сложна; за нашия случай разделянето на плана и факта в записа на поръчката е излишна информация и съставената спецификация на поръчката се презаписва на база резултатите от приемането на доставените стоки; рядката прекалена спецификация и получаването на стоки с несъответстващо качество се решават извън информационната система.
И една ден виждате как целият зал на таверната е пълен с възмутени и нечесани пирати. Какво се е случило?

Оказа се, че заедно с нарастващия бизнес нараства и потреблението. Някога е било взето управленско решение, че ако газелата е натоварена извън допустимите норми по обем и/или тегло, което се случва много рядко, доставчикът приоризира натоварването в полза на напитките.

Недоставените стоки попадаха в следващата поръчка и бяха изпратени в нова доставка; наличието на минимален остатък в склада на таверната позволяваше да не се забелязват изключенията.

В пристанището затвори последният конкурент, а случайът с претоварването на газелата, избягнат при приоритизацията, основана на предположението за достатъчност на минималния остатък и периодичното недонасяне на превозното средство, стана обичайна практика. Създадената система ще работи идеално в съответствие с вградените в нея алгоритми и няма да има възможност да се установи систематичното неизпълнение на планираните поръчки. Само развалената репутация и недоволните клиенти ще могат да разкрият проблема.

Внимателният читател вероятно е забелязал, че поръчаният брой в спецификацията на поръчката (T_ORDER_SPEC) в раздел 2 и в раздел 5 може да отговаря или не на изискването за първа нормална форма. Всичко зависи от това, дали при избрания асортимент стоки в едно и също поле могат да попаднат различни по същност единици за измерване.

Нарушение на втората нормална форма:

С нарастващите ви нужди въвеждате още няколко транспортни средства с различни размери. В изложеното по-горе контекст, създаването на справочник за транспортни средства е признато за излишно, в резултат на което всички алгоритми за работа с данни, обслужващи нуждите на доставките и склада, възприемат преместването на стоките от доставчика до склада като курс, извършван изключително от 1,5-тонната газела. И така, заедно с покупката на нови транспортни средства вие все пак създавате справочник за транспортни средства, но при доработката ще трябва да анализирате целия код, който се отнася до преместването на стоките, за да установите дали в конкретното място не се имат предвид характеристиките на автомобила, с който е започнал бизнесът.

Нарушение на третата нормална форма:

В един момент започвате да създавате програма за лоялност, появява се запис на постоянен клиент. Защо, например, да губите време в създаването на материални представяния, съхраняващи агрегирани данни за продажбите на отделен клиент за използване в отчети и предаване на аналитични системи, когато на етапа на стартиране на програмата за лоялност всичко, което интересува клиента, може да бъде поставено в записа на самия клиент? И, наистина, на пръв поглед, няма смисъл. Но всеки път, когато вашият бизнес включва, например, нови канали за продажба, сред вашите анализатори трябва да има някой, който да си спомни, че съществува такъв агрегиращ атрибут.

Проектиране на всеки нов процес, да речем, продажби в интернет, продажби чрез дистрибутори, свързани с общата система за лоялност, някой трябва да има предвид, че всички нови процеси трябва да осигуряват целостта на данните на ниво код. За промишлена база данни с хиляда таблици, това изглежда малко осъществимо.

Опитен разработчик, разбира се, знае как да предотврати всички споменати по-горе проблеми, но, според мен, задачата на опитния анализатор е да не ги допуска.

Искам да изразя благодарност за ценната обратна връзка при подготовката на публикацията на водещия разработчик Евгений Ярухин.