Вітання! Мене звуть Андрій Семенов, я старший аналітик у Спортмайстер. У цьому посту я хочу порушити питання деноралізації баз даних ERP-систем. Ми розглянемо загальні умови, а також конкретний приклад — скажімо, це буде чудова таверна-монополіст для піратів та моряків. У якій піратів і моряків треба обслуговувати по-різному, бо уявлення про прекрасне та споживчі патерни у цих добрих панів суттєво відрізняються.
Як зробити так, щоб усі були задоволені? Як не збожеволіти, проектуючи і підтримуючи таку систему? Що робити, якщо в таверну починають приходити не лише звичні пірати та моряки?
Все під катом. Але ходімо по порядку.
1. Обмеження та припущення
Все викладене стосується лише реляційних баз даних. Добре освітлені, у тому числі в мережі Інтернет, наслідки денормалізації у вигляді аномалій модифікації, видалення та вставки не розглядаються. За рамками публікації залишаються випадки, коли денормалізація є спільним місцем, із класичними прикладами: серія та номер паспорта, дата та час та інше.
У пості використовуються інтуїтивно зрозумілі та практично застосовні визначення нормальних форм, без посилань до математичних термінів. Як вони можуть бути застосовані до обстеження реальних бізнес-процесів (БП) і проектування промислового ПЗ.
Існує думка, що проектування сховищ даних, інструментів для створення звітності та інтеграційних угод (у яких використовується табличне подання інформації) відрізняється від проектування баз даних ERP-систем тим, що зручність споживання та застосування для його досягнення усвідомленої денормалізації може мати пріоритет над захистом цілісності. даних. Я поділяю цю думку, і описане нижче відноситься виключно до моделей майстер-даних та транзакційних даних ERP-систем.
Пояснення нормальних форм наведено з прикладу зрозумілому на побутовому рівні більшості читачів. Однак як наочна ілюстрація в пунктах 4-5 усвідомлено використано підкреслено «вигадане» завдання. Якщо цього не зробити і взяти якийсь хрестоматійний приклад, наприклад ту саму модель зберігання замовлення з п. 2, можна опинитися в ситуації, коли фокус уваги читача буде зміщений із запропонованого розкладання процесу в модель, на особистий досвід і сприйняття того, як повинні будуватися процеси та моделі зберігання даних в ІС. Іншими словами, візьміть двох кваліфікованих ІТ-аналітиків, нехай один надає сервіс логістам, що перевозять пасажирів, інший - логістам, що перевозять верстати для мікрочіпів. Попросіть їх, не обговорюючи заздалегідь автоматизовані БП, скласти модель даних для зберігання інформації про залізничний рейс.
Існує ненульова ймовірність, що в запропонованих моделях ви знайдете не тільки помітно різний набір атрибутів, але й набори сутностей, що не збігаються, тому що кожен аналітик спиратиметься на звичні йому процеси і завдання. І сказати у такій ситуації, яка модель «правильна», неможливо, бо немає критерію оцінки.
2. Нормальні форми
Перша нормальна форма БД потребує атомарності всіх атрибутів.
Зокрема, якщо у об'єкта A існують неключові атрибути a і b, такі що c=f(a,b) і в таблиці описує об'єкт A ви зберігаєте значення атрибуту с, то БД порушена перша нормальна форма. Наприклад, якщо у специфікації замовлення вказується кількість, одиниці виміру якого залежать від типу товару: в одному випадку це можуть бути штуки, в іншому літри, у третьому упаковки, що складаються з штук (у моделі вище 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-тонної газелі. Отже, разом із покупкою нових транспортних засобів ви все-таки створюєте довідник транспортних засобів, але при доопрацюванні вам доведеться проаналізувати весь код, що посилається на переміщення вантажу, щоб з'ясувати, чи не маються на увазі в кожному конкретному місці посилання на характеристики того самого автомобіля, з якого розпочинався бізнес.
Порушення третьої нормальної форми:
Якогось моменту ви починаєте створювати програму лояльності, з'являється запис постійного клієнта. Навіщо, наприклад, витрачати час на створення матеріальних уявлень, що зберігають агреговані дані з продажу окремому клієнту для використання у звітності та передачі до аналітичних систем, якщо на етапі старту програми лояльності все, що цікавить замовника, можна розмістити на записі самого клієнта? І справді сенсу, на перший погляд, немає. Але щоразу, коли ваш бізнес підключатиме, наприклад, нові канали продажів, серед ваших аналітиків має бути хтось, хто згадає, що існує такий агрегаційний атрибут.
Проектуючи кожен новий процес, припустимо, продажу в мережі Інтернет, продаж через дистриб'юторів підключених до загальної системи лояльності, хтось повинен пам'ятати, що всі нові процеси повинні забезпечувати на рівні коду цілісність даних. Для промислової БД із тисячею таблиць це видається малоздійсненним завданням.
Досвідчений розробник, звичайно ж, знає, як усунути всі зазначені вище проблеми, але, на мій погляд, завдання досвідченого аналітика — до них не доводити.
Хочу висловити подяку за цінний зворотний зв'язок під час підготовки публікації провідному розробнику Євгену Ярухіну.
література
Конноллі Томас, Бегг Каролін. Бази даних. Проектування, реалізація та супровід. Теорія та практика
Джерело: habr.com