Введение

Как правильно спроектировать схему данных? Какие критерии должен применить разработчик при выборе из большого числа возможных схем? Широко известны «академические» требования к качеству схемы данных – требования нормализации или соблюдения одной из нормальных форм. При этом не афишируется (не написано в учебниках, неочевидно для начинающих проектировщиков схем данных), что и при соблюдении этих требований обычно остается достаточно широкий выбор возможных схем. В данной статье приводится пример выбора между альтернативными схемами данных, когда на первый план выступают уже не требования нормализации, а другие критерии: простота реализации клиентской части, уменьшение объема хранимой информации.

Два варианта схемы данных для табельного учета

Предметная область примера – табельный учет рабочего времени. Основная информация, которую необходимо хранить в программе табельного учета – кто, когда, сколько часов отработал и по какому коду оплаты. Документальным отражением этой информации служит табель учета рабочего времени:

Рис 1. Табель учета рабочего времени (фрагмент)

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

Рис.2 Схема данных – «Модель А»

На схеме:

• Person – справочник сотрудников (ФИО, табельный номер…)
• PaymentType – таблица кодов оплаты
• Tabel – таблица с заголовками документов – табелей. Поле Date – содержит информацию только о годе и месяце табеля.
• TabelFact – таблицы фактов (кто, когда, сколько часов отработал). Поле Date – в какой день месяца произошел факт.

Рис 3. Схема данных – «Модель Б»

Таблица TabelRow содержит строки табелей.

Предварительное сравнение этих схем показывает:

1. Они различаются представлением данных фактов отработанного времени. В первом случае – хранятся факты в чистом виде, во втором – они хранятся в развернутом виде, как строки табелей.
2. В схеме в модели фактов можно создать pivot-представление, аналогичное таблице TabelRow в модели строк.

   SELECT idTabel, idPaymentType, idPerson,
    SUM(CASE DAY(Date) WHEN 1 THEN Hours ELSE 0 END) AS h1,
    SUM(CASE DAY(Date) WHEN 2 THEN Hours ELSE 0 END) AS h2,
    ...
    SUM(CASE DAY(Date) WHEN 31 THEN Hours ELSE 0 END) AS h31
FROM tblTabelFact
GROUP BY idTabel, idPaymentType, idPerson

   или, для MS SQL-Server 2005:

   SELECT idTabel, idPaymentType, idPerson,
    [1] AS h1, [2] AS h2, [3] AS h3, [4] AS h4, [5] AS h5,
    [6] AS h6, [7] AS h7, [8] AS h8, [9] AS h9, [10] AS h10,
    [11] AS h11, [12] AS h12, [13] AS h13, [14] AS h14, [15] AS h15,
    [16] AS h16, [17] AS h17, [18] AS h18, [19] AS h19, [20] AS h20,
    [21] AS h21, [22] AS h22, [23] AS h23, [24] AS h24, [25] AS h25,
    [26] AS h26, [27] AS h27, [28] AS h28, [29] AS h29, [30] AS h30, [31] AS h31
FROM
    (SELECT idTabel, idPaymentType, idPerson, Hours, DAY(Date) as Day 
     FROM tblTabelFact) AS t1
           PIVOT (SUM(Hours) FOR [Day] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
                          [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])) AS t2

3. В модели строк можно создать unpivot-представление, аналогичное таблице TabelFact в модели А:

   SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
    TabelRow.idTabel, TabelRow.h1 AS Hours
FROM Tabel
    INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
    INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
           AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 1
UNION
SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
    TabelRow.idTabel, TabelRow.h2 AS Hours
FROM Tabel
    INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
    INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
           AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 2
UNION
...
UNION
SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
    TabelRow.idTabel, TabelRow.h31 AS Hours
FROM Tabel
    INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
    INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
           AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 31

   для MS SQL-Server 2005:

   SELECT t.idTabel, t.idPaymentType, idPerson, Hours, Calendar.Date
FROM tblTabelRow
           UNPIVOT (Hours For Day IN (h1, h2, h3, h4, h5, h6, h7, h8, h9, h10,
                                 h11, h12, h13, h14, h15, h16, h17, h18, h19, h20,
                                 h21, h22, h23, h24, h25, h26, h27, h28, h29, h30, h31)) AS t
    INNER JOIN tblTabel ON t.idTabel = tblTabel.idTabel
    INNER JOIN Calendar ON YEAR(tblTabel.Date) = Calendar.YEAR
AND MONTH(tblTabel.Date) = Calendar.Month AND t.Day = 'd' +  CAST(Calendar.Day AS VARCHAR)

   где таблица Calendar содержит список всех календарных дат (в разумном диапазоне) и имеет следующую структуру:

   CREATE TABLE Calendar(
    [Date] [datetime],
    [Year] [int],
    [Month] [int],
    [Day] [int])

4. Возможности, продемонстрированные в пп. 2 и 3 показывают, что схемы действительно являются альтернативными и можно говорить о выборе между ними. (На самом деле схемы являются до конца эквивалентными лишь с учетом следующих ограничений: в модели фактов у одного человека в табеле в один день по конкретному виду оплаты труда не может быть двух записей, в модели строк – проще: на одного человека в табеле не может быть двух строк по одному виду оплаты. Если это соблюдается – приведенные выше операции pivot и unpivot будут обратными.)

   
   Рис. 4 Pivot и Unpivot преобразования

5. Проверка таблиц TabelFact и TabelRow на соответствие нормальным формам:
   1. Ни одно из полей обеих таблиц не содержит более одного значения и ни одно из ключевых полей не пусто – выполняется требование 1-й нормальной формы
   2. В TabelRow есть составной первичный ключ {idTabel, idPaymentType, idPerson} (в одном табеле не могут быть две строки с одним видом оплаты для одного человека), который связан полной функциональной зависимостью с любым полем, не входящим в первичный ключ. Аналогично – таблица TabelFact с составным ключом {idTabel, idPaymentType, idPerson, Date} (в один день не может быть зафиксировано два факта работы с одинаковым видом оплаты для одного и того же человека). В этой таблице остается лишь одно поле, не входящее в составной ключ – Hours оно не является функционально зависимым от части составного ключа – соответственно связано полной функциональной зависимостью с составным первичным ключом. – Для обеих таблиц выполняется требование 2-й нормальной формы.
   3. Неключевые поля обеих таблиц не зависят функционально от других неключевых полей: в TabelFact – одно неключевое поле (Hours), в TabelRow – неключевые поля h1-h31 на языке предметной области требование означает, что количество часов, отработанное человеком в какой-либо день месяца не должно зависеть функционально от количества часов, отработанного в другие дни – так оно и есть на самом деле. Таким образом, таблицы соответствуют 3-й нормальной форме.
   4. Таблицы соответствуют нормальной форме Бойса-Кодда, т.к. имеют всего по одному ключу – уже рассмотренному в пункте b.
   5. В таблицах нет многозначных зависимостей (многозначная зависимость не является функциональной, она существует в том случае, когда из факта, что в таблице содержится некоторая строка X, следует, что в таблице обязательно существует некоторая определённая строка Y). Соответственно – выполняются требования четвертой и пятой нормальных форм (они формулируются, как ограничения на существующие многозначные зависимости).

   Таким образом, выбор между моделями А и Б возможен и, принимая решение, необходимо руководствоваться иными критериями, нежели нормализация данных.

Критерий простоты программирования учетной системы.

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

Запросы для получения данных в эту форму:

• Модель А:

  SELECT idTabel, idPaymentType, idPerson,
  SUM(CASE DAY(Date) WHEN 1 THEN Hours ELSE 0 END) AS h1,
  SUM(CASE DAY(Date) WHEN 2 THEN Hours ELSE 0 END) AS h2,
  ...
  SUM(CASE DAY(Date) WHEN 31 THEN Hours ELSE 0 END) AS h31
FROM tblTabelFact
GROUP BY idTabel, idPaymentType, idPerson
WHERE idTabel = @idTabel

  - всего 35 строк. Можно конечно принять решение о разворачивании данных не в SQL-запросе, а в программном коде, но вряд ли это будет проще.

• Модель Б:

  SELECT idTabel, idTabelRow, idPerson, idPaymentType, h1, h2, h3, h4, h5, h6, h7, h8, h9, h10,
h11, h12, h13, h14, h15, h16, h17, h18, h19, h20,
h21, h22, h23, h24, h25, h26, h27, h28, h29, h30, h31
FROM TabelRow
WHERE idTabel = @idTabel

Разница очевидна: запрос в модели А значительно сложнее. Это еще сильнее проявляется для запросов модифицирующих данные: если для модели строк подходят стандартные механизмы сохранения данных таблицы (грида), то для модели фактов придется изобретать свои методы – задача конечно же решаемая, но все же нетиповая. Причем именно усложнение программирования модификации данных здесь позволяют сделать выбор в пользу модели Б, ведь, как уже говорилось выше, модели эквивалентны –можно создать представление (view) и пользоваться в модели А коротким запросом как в модели Б.

Критерий объема хранимой информации.

В современных условиях дисковое пространство - не самый дорогой ресурс. Тем не менее, бывают ситуации, когда желательно минимизировать объем хранимой информации. Расчет размера памяти, необходимого для хранения одной строки табеля (для 30-дневного месяца)

• Модель А:

30*(sizeof(idTabel) + sizeof(idPaymentType) + sizeof(idPerson) + sizeof(Hours) + sizeof(Date));

• Модель Б:

sizeof(idTabel) + sizeof(idPaymentType) + sizeof(idPerson) + 30*sizeof(Hours).

При любых размерах полей таблицы выигрыш будет за моделью Б. Если предположить, что все поля одного размера, то речь идет о соотношении 150:33). При этом, прошу заметить, что из расчета исключены искусственные ключи таблицы (принимая во внимание существования спора «суррогатные vs. естественные ключи»). Если их включить в расчет, то соотношение еще больше будет в пользу модели Б. Некоторую поправку может внести соображение, что не все ячейке табеля заполнены. Например, человек вышел работать сверхурочно только один раз в месяц. Для таблицы TabelFact модели А это означает просто отсутствие строк (следовательно – экономию памяти). Однако практика показывает, что пустых ячеек в табелях не более половины.

Критерий простоты составления запросов для отчетных (аналитических) систем.

Невозможно предусмотреть все возможные запросы, которые могут понадобиться для составления отчетов по данным табельного учета. Тем не менее, логично предположить, что большинство запросов будут содержать фильтр данных по датам. Как это будет выглядеть для альтернативных моделей:

• Модель А:

  SELECT *
FROM TabelFact
WHERE Date>=@BeginDate AND Date<=@EndDate

• Модель Б:

  SELECT *
FROM (
    SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
           TabelRow.idTabel, TabelRow.h1 AS Hours
    FROM Tabel
           INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
           INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
                  AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 1
    UNION
   
    SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
           TabelRow.idTabel, TabelRow.h2 AS Hours
    FROM Tabel
           INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
           INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
                  AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 2
    UNION
    ...
    UNION
    SELECT     TabelRow.idPaymentType, Calendar.Date, TabelRow.idPerson,
           TabelRow.idTabel, TabelRow.h31 AS Hours
    FROM Tabel
           INNER JOIN TabelRow ON Tabel.idTabel = TabelRow.idTabel
           INNER JOIN Calendar ON YEAR(Calendar.Date) = YEAR(Tabel.Date)
                  AND MONTH(Calendar.Date) = MONTH(Tabel.Date) AND Calendar.Day = 31 ) AS t
WHERE Date>=@BeginDate AND Date<=@EndDate

Видно, что с точностью до наоборот повторяется ситуация с SELECT-запросами строк табеля. Теперь запрос в модели фактов выглядит намного лаконичнее. Опять же ситуацию можно поправить, используя представление (view) в случае выбора модели Б.

С точки зрения аналитических (OLAP) систем, известно, что для построения OLAP-куба необходима схема типа «снежинка», центром которой является таблица фактов, содержащая значения мер куба. Модель А идеально соответствует этому требованию, а в модели Б понадобиться построение unpivot-view для таблицы TabelRow.

Таким образом, по данному критерию привлекательнее выглядит модель А, однако в случае выбора модели Б проблема решается построение всего одного представления.

Критерий производительности.

Исследование вариантов схемы данных будет неполным без рассмотрения вопросов производительности. На момент принятия решения у меня не было данных для тестирования производительности – пришлось их генерировать, в настоящий момент – программа работает три года, накопилось достаточно информации, поэтому приведу факты по реальной базе данных:

Показатель	Модель А, таблица TabelFact	Модель Б, таблица TabelRow
Число строк в таблице	5 млн.	300 тыс.
Время выполнения запроса на получение деталей одного табеля	30ms	5ms
Время на изменение значения одного факта	12ms	2ms
Время расчета OLAP-куба	1мин.	4мин.

Таблица 1. Характеристики базы данных табельного учета

Таблица TabelFact создана и рассчитана специально для настоящего исследования, в реальной базе ее нет. При развороте должно было получиться примерно 300*30 –9 млн. строк, но как уже говорилось – примерно половина из них пустые (нулевые). Заполнение таблицы на сервере заняло 5 минут. Резервная копия базы возросла при этом с 300 до 500 Мб.

Разница в скорости выполнения запросов учетной системы вполне предсказуема и определяется в основном количеством записей в таблицах.

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

Осталось выяснить, насколько эти показатели соответствуют характеру требований производительности:

1. Для учетной системы требования полностью удовлетворены как при выборе модели фактов, так и при выборе модели строк. 6-кратное различие в скорости запросов на выбор данных одного табеля и изменение одного факта – некритично, если принимать во внимание, что в самом худшем варианте задержка составляет 30ms (0,03 секунды).
2. С точки зрения отчетных систем, надо принять во внимание такую особенность предметной области, как периодический характер составления отчетности. Табели заполняются раз в месяц, компактно по времени (после 25-го числа, должны быть поданы до конца месяца), в период подачи документов сводная информации по данным текущего месяца не требуется. Как следствие, достаточно раз в месяц рассчитывать OLAP-куб (4 минуты – не проблема при такой периодичности). Если волнуют проблемы производительности построения отчетов на основании unpivot-представления – можно раз в месяц рассчитывать таблицу TabelFact и в отчетах использовать уже ее.

Заключение

Результаты проведенного исследования, конечно же, говорят в пользу модели Б – «модели строк»: программировать учетную систему проще, запросы для нее будут работать быстрее, данные займут меньше места, проблему падения производительности в отчетной системе можно легко решить при помощи предварительного ежемесячного расчета таблицы фактов.

Можно ли применить результаты данного исследования при проектировании других баз? В самой предметной области есть одна особенность, без которой модель строк вообще бы не рассматривалась: в любом месяце года от 28-и до 31-го дня. Соответственно, если в предметной области факты группируются на основании признака, возможные значения которого ограничены и заранее известны, то в этом случае возможно рассмотрение альтернативных схем данных, переход между которыми осуществляется на основе pivot-unpivot преобразований. Общий характер и последовательность исследования по приведенным критериям, применимы для любой схемы данных.