Денормализация баз данных ERP-систем и ее влияние на развитие ПО: открываем таверну на Тортуге

Привет! Меня зовут Андрей Семенов, я старший аналитик в Спортмастер. В этом посте я хочу поднять вопрос денормализации баз данных 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
Моряк

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

Пираты-экологи, естественно, не могут пользоваться костяными гребнями и требуют аналог из переработанного морского пластика.

Вам необходимо переработать алгоритмы работы программ в соответствии с новыми вводными. Если бы правила нормализации были выполнены, то вам пришлось бы лишь в части систем дополнить входы для некоторых веток процессов и создать новые ветки только для тех случаев и в тех ИС, где имеет значение волосяной покров на лице. Но, так как правила не были выполнены, вам придется проанализировать весь код, во всем контуре, где используются значения справочника типов клиентов и однозначно установить, что в одном случае алгоритм должен принимать во внимание профессиональную деятельность клиента, а в другом физические особенности.

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

• результат распознавания в виде набора установленных признаков,

ИД распознанного объекта
Грета на левой груди
Птица на плече
Волосяной покров на лице

100510
1
1
1

100511
0
0
1

100512

1
0

• результат определения типа клиента (пусть это будет пользовательское представление, в котором выведены описания из справочников)

Означает ли обнаруженная денормализация, что системы нельзя будет доработать под новые условия? Конечно, нет. Если представить, что все ИС создавались одной командой с нулевой текучкой кадров, разработки хорошо документированы и информация в команде передается без потерь, то требуемые изменения могут быть произведения с пренебрежительно малыми затратами усилий. Но если мы вернемся к исходным условиям задачи, только на печать протоколов совместных обсуждений сотрется 1,5 клавиатуры и еще 0,5 на оформление закупочных процедур.

В приведенном выше примере, нарушены все три нормальные формы, давайте попробуем понарушать их по отдельности.

Нарушение первой нормальной формы:

Допустим, товары на ваш склад доставляются со складов поставщиков самовывозом с использованием одной 1.5-тонной газели которая принадлежит вашей таверне. Размер ваших заказов настолько небольшой относительно оборотов поставщиков, что они выполняются всегда один в один без ожидания изготовления. Нужны ли при таком БП отдельные таблицы: транспортные средства, типы транспортных средств, нужно ли разделять план и факт в ваших заказах ушедших поставщикам?

Только представьте, сколько «лишних» соединений придется написать вашим программистам, если для разработки программы использовать модель ниже.

Допустим, мы приняли решение, что предложенная структура излишне сложна, для нашего случая разделять план и факт в записи заказа — это избыточная информация, и сформированная спецификация заказа перезаписывается по результатам приемки прибывшего товара, редкий пересорт и приход товара ненадлежащего качества улаживаются вне ИС.
И вот однажды вы видите, как весь зал таверны заполнен возмущенными и нечесанными пиратами. Что же произошло?

Оказалось, что вместе с ростом вашего бизнеса росло и потребление. Когда-то было принято управленческое решение, что если газель оказывалась перегруженной по объему и/или весу, что бывало крайне редко, поставщик приоритизировал загрузку в пользу напитков.

Недовезенный товар попадал в следующий заказ и уходил в новом рейсе, наличие неснижаемого остатка на складе при таверне позволяло не замечать выколотых случаев.

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

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

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

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

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

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

Проектируя каждый новый процесс, допустим, продажи в сети Интернет, продажи через дистрибьюторов подключенных к общей системе лояльности, кто-то должен держать в голове, что все новые процессы должны обеспечивать на уровне кода целостность данных. Для промышленной БД с тысячей таблиц это кажется малоосуществимой задачей.

Опытный разработчик, конечно же, знает, как купировать все означенные выше проблемы, но, на мой взгляд, задача опытного аналитика — до них не доводить.

Хочу выразить благодарность за ценную обратную связь при подготовке публикации ведущему разработчику Евгению Ярухину.

Литература

https://habr.com/en/post/254773/
Коннолли Томас, Бегг Каролин. Базы данных. Проектирование, реализация и сопровождение. Теория и практика

Источник: habr.com