Про можливість введення додаткових критеріїв якості схем баз даних, Комерція, Різне, статті

Введення


Як правильно спроектувати схему даних? Які критерії повинен застосувати розробник при виборі з великого числа можливих схем? Широко відомі «академічні» вимоги до якості схеми даних – вимоги нормалізації або дотримання однієї з нормальних форм. При цьому не афішується (не написано в підручниках, неочевидно для початківців проектувальників схем даних), що і при дотриманні цих вимог звичайно залишається досить широкий вибір можливих схем. У даній статті наводиться приклад вибору між альтернативними схемами даних, коли на перший план виступають вже не вимоги нормалізації, а інші критерії: простота реалізації клієнтської частини, зменшення об’єму інформації.

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


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


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

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


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

На схемі:


Рис 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 H2,
        [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, H2, 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.).

Запити для отримання даних в цю форму:


Різниця очевидна: запит в моделі А значно складніше. Це ще сильніше виявляється для запитів модифікуючих дані: якщо для моделі рядків підходять стандартні механізми збереження даних таблиці (гріду), то для моделі фактів доведеться винаходити свої методи – завдання звичайно ж можна вирішити, але все ж нетипова. Причому саме ускладнення програмування модифікації даних тут дозволяють зробити вибір на користь моделі Б, адже, як уже говорилося вище, моделі еквівалентні-можна створити уявлення (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-запитів рядків табеля. Тепер запит до моделі фактів виглядає набагато лаконічніше. Знову ж ситуацію можна поправити, використовуючи подання (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 перетворень. Загальний характер і послідовність дослідження за наведеними критеріями, застосовні для будь схеми даних.


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*