Здравейте! Казвам се Андрей Семенов, аз съм старши анализатор в Спортмастер. В този пост искам да повдигна въпроса за денормализацията на бази данни в 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, се проявява атрибут C, а при обект 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-тонна газела. Така че, заедно с покупката на нови превозни средства, все пак създавате справочник на превозните средства, но при доработка ще трябва да анализирате целия код, който се отнася до преместването на товара, за да разберете дали на всяко конкретно място се имат предвид характеристиките на същото това превозно средство, с което започна бизнесът.
Нарушение на третата нормална форма:
В някакъв момент започвате да създавате програма за лоялност, появява се запис на постоянен клиент. Защо, например, да се губи време в създаването на материални представяния, съ хранящи агрегирани данни по продажбите на отделен клиент за използване в отчети и предаване в аналитични системи, ако на етапа на стартиране на програмата за лоялност всичко, което интересува клиента, може да се разположи в записа на самия клиент? И наистина, на пръв поглед, няма смисъл. Но всеки път, когато вашият бизнес свързва, например, нови канали за продажба, между вашите анализатори трябва да има някой, който да си спомни, че съществува такъв агрегиращ атрибут.
Проектиране на всеки нов процес, да речем, продажби в интернет, продажби чрез дистрибутори, свързани с общата система за лоялност, някой трябва да има предвид, че всички нови процеси трябва да осигуряват целостта на данните на ниво код. За промишлена база данни с хиляда таблици това изглежда като трудна задача.
Опитен разработчик, разбира се, знае как да отстрани всички споменати по-горе проблеми, но, според мен, задачата на опитния анализатор е да не допусне те да се появят.
Искам да изразя благодарност за ценната обратна връзка при подготовката на публикацията на водещия разработчик Евгений Яруулев.
Литература
Конноли Томас, Бегг Каролин. Бази данни. Проектиране, реализиране и поддържане. Теория и практика.
Източник: habr.com
