10 марта 2013 в 22:56

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


Есть концепции работы с базой, основанные на ORM, CodeFirst со своими преимуществами и недостатками. Предлагаемая здесь унификация базы основана в первую очередь на подходе Database First.

Схема базы данных приложений со сложной доменной моделью (к которым относятся системы 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 на редактируемый справочник).
Предполагается что тип записи редактируемого справочника «Тэг» контролируется на уровне приложения.
Спасибо за внимание, комментарии приветствуются.



Ссылки:


Григорий Кислин @gkislin
карма
18,0
рейтинг 0,0
Ведущий Java Enterprise тренингов
Самое читаемое Разработка

Комментарии (21)

  • +5
    Я бы не был столь категоричным в
    Общие свойства всех документов размещаем в отдельной таблице

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

    Не хотите пробовать использовать ORM и наследование с разными mappings?

    При этом, там где это действительно необходимо, выбрав ClassTable легко получаем то что вы описали.
    А там где более выгодно (например в плане производительности и удобства партиционирования) разбить на несколько таблиц — ConcreteTable
    • 0
      Простите, не понял- каким образом я могу получить блокировку на всю таблицу (если конечно уровень изоляции не Serializable)?
      Изменение типа документа- это в дизайне?
      • +1
        msdn При блокировании значительного количества строк блокировка может эскалироваться на страницы, и далее на всю таблицу.
        • +1
          Поведение, характерное для блокировочников, или даже отдельных СУБД отдельных поставщиков не должно приниматься как должное ко всем СУБД. Не MsSQL единым живо реляционное хранение данных.
          • 0
            Вообще MS SQL, начиная с 2005, легко может быть честным версионником.
    • 0
      По поводу ORM (не имею ничего против него) — дописал вступление.
  • 0
    Для таких штук EAV решает, на самом деле. Особенно если система позиционируется как легко кастомизируемая.
    • 0
      Ну, наверное здесь есть что-то схожее с EAV. Только аттрибутами являются не примитивные типы, а составные.
    • +1
      А вот тут про EAV SQL антипаттерн: www.slideshare.net/billkarwin/sql-antipatterns-strike-back.
      Данный подход свободен от них, кроме нестрогое ограничение полей.
  • +1
    EAV не решает, потому что дико тормозит при сколь-нибудь серьезном объеме данных.
    • +1
      все дико тормозит при сколь-нибудь серьезном объеме данных, просто для разных технологий он слегка отличается
      многие проблемы EAV можна решить материализацией
      и говорить что EAV не решает, всеравно что говорить что реляционные базы данных не решают
      • 0
        А чем eav + материализация лучше отдельных таблиц? Все равно ведь схему менять надо, на каждое добавление атрибута.
  • 0
    Общие свойства всех документов размещаем в отдельной таблице.

    Вы только что изобрели 1с 7.7. Это не может работать при большом количестве пользователей.
    • 0
      Здесь нужно определиться что такое большие нагрузки. ERP- это корпоративное интранет приложение, максимальная нагрузка не сравнима с приложениями для публичного интенсивного пользования.
      • +1
        Нагрузка для корпоративных приложений измеряется объемами обрабатываемых данных. В типичной учетной системе необходимо быстро и стабильно работать при 1М+ проводках и при 100,000 первичных документов.

        Посчитать легко. Берем цикл жизни системы — 5-9 лет — умножаем на количество первичных документов и проводок в год, умножаем на Пи (оно примерно равно 15%-росту в год на все 9 лет). Получаем требуемые числа.

        • 0
          А как насчет горизонтального секционирования по подсистеме и, при необходимости, по типу документа?
          • 0
            Это зависит от запросов, можно неаккуратно писать и упереться в в одну общую таблицу сущностей, если действовать по модели, предложенной в статье. Разные таблицы для разных типов (3НФ) конечно приближает к идеалу, но её сложно поддерживать.

            Еще обязательно рассмотреть такие моменты:
            1) создание связей между произвольными типами
            2) версионность документов
            3) «корзина» удаленных элементов
            4) разграничение доступа

            Системы валятся от объема данных не потому что подставь_сюда_любой_движок_БД слабоват, а потому что изначально не продумывали аспекты, которые я указал выше, а потом они «внезапно» возникли.
            • 0
              Спасибо за подробные ответы.
            • 0
              Секционирование неудобно тем, что на общие таблицу нельзя делать FK. Поддерживать по подсистемам будет действительно сложно. Для производительности можно секционировать актуальные и исторические данные, работая по умолчанию только с актуальными. Объем данных существенно уменьшится.
      • 0
        Для 1С 7.7, уже 70 одновременно работающих операторов в стандартной торговле большая проблема и без костылей не решается.
        Так что речи о публичных сервисах даже близко нет.
  • 0
    Тем не менее, такой подход может дать системе реактивный старт.

    У нас так и было:
    Все справочники имели универсальную структуру и жили в одном дереве. Можно заводить типизированные параметры, действующие на объекты от какого-то узла и ниже. Значения параметров тоже могут ссылаться на узлы дерева. Там же в дереве и юзеры, и группы. По сути чем-то LISP напоминает (все есть список => всё есть узел дерева).
    Ещё и метаданные документов лежали в БД, объект-документ не содержал поля фиксированного типа, обращение к полю происходило по строковому имени, как к полю dataset. Таким образом, заведение нового документа требовало минимум кодирования.

    Через несколько лет оно стало тормозить и самые популярные документы были вынесены в отдельные таблицы, а что-то и на другой сервер (благо трёхзвенка). Но для документов, создающихся в количестве не более 1E6 за год, можно и эту неэффективную схему использовать.

Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.