Архитектура базы данных: унификация (на примере ERP River)

Схема базы данных приложений со сложной доменной моделью (к которым относятся системы ERP) обычно состоит из нескольких сотен таблиц. Поэтому на начальном этапе проектирования базы для избежания многократных дублирований и разбухания схемы важно определиться с несколькими базовыми таблицами для хранения общих свойств базовых сущностей приложения и все остальные таблицы уже проектировать как вспомогательные или дополнения основных таблиц.

Пример проектирования документов:

• Общие свойства всех документов размещаем в отдельной таблице.
• На каждый тип документов с собственными, специфичными полями создается отдельная таблица, которая приджойнивается к общей таблице. Для сокращения количества полей-индексов FK к общей таблице делаем PK. При показе списка документов мы отображаем только общие поля из базовой таблицы, а при показе конкретного документа уже используем join, поэтому производительность не страдает.
• Функционально однотипные поля документов (особенно если они различаются для разных типов документов) выносим в отдельные общие таблицы. Это
  
  • ссылки на контрагентов (например суд, заявитель жалобы, заинтересованное лицо, третье лицо для документа «Отзыв на жалобу»).
  • ссылки на людей, играющих в документе определенные роли (автор, получатель, исполнитель, согласующий, ответственный, делопроизводитель, руководитель).
  • ссылки на другие документы (основание, командировочный документ, ссылка на договор, счет, протокол разногласий, контракт).

  Дополняем эти таблицы полем — тип ссылки (для PostgreSQL — подходит enum). При этом запрос к определенному документу обрастает джойнами, но выигрыш в унификации обращения с данными огромен: проверка при удалении документа, сохранение документа, копирование документа для всех полей общих таблиц- будет делаться не специально для каждого из сотни типов документов, а один раз. Плюс у нас появляется возможность множественных ссылок (несколько получателей, котрактов, третьих лиц) для одного документа.

• Далее- каждая подсистема ERP (бюджетирование, логистика, СЭД, склады, CRM, ..) имеет свои документы с теми же самыми общими свойствами. Необходимо иметь возможность вывести список всех документов для одной подсистемы и список всех постоянных атрибутов (состояний, типов, папок) документов для одной подсистемы. Создаем enum module, характеризующий подсистему

              CREATE TYPE ref.module AS ENUM             ( 'bdg', 'crm', 'ecm', 'wms', 'scm', ... );             

  и добавляем поле типа module в эти таблицы. В резутьтате мы имеем общий PK для всех документов приложения, общий код для обработки CRUD, возможность ссылки из любого документа на документ других подсистем, общую систему прав на действия с документом и пр.

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

базовые сущности приложения:

константы (типы и статусы документов, аттрибуты контрагентов, типы связей документов, режимы доступа, типы отправки) и редактируемые справочники (тэги, роли, ..). Создаем две таблицы const и ref и два enum, характеризующий типы записей этих таблиц. И еще две общие таблицы приложения doc.folder и ref.folder для поддержания древовидной структуры документов и редактируемых справочников. Один из недостатков такой унификации — нестрогое ограничение полей на уровне базы (т.е поле «ссылка на тэг документа» будет иметь FK на редактируемый справочник). Предполагается что тип записи редактируемого справочника «Тэг» контролируется на уровне приложения.
Спасибо за внимание, комментарии приветствуются.

---

Ссылки:

• ERP River