Інформаційна система і реляційна СУБД

Отже. "Кожна госпоперацій підлягає відображенню в одній і тій же сумі одночасно за дебетом одного рахунку і кредиту іншого". Приберемо з цього визначення погано детерміноване поняття "госпоперацій" і введемо поняття "проводка". Так всім буде зрозуміліше. Вийде: "Кожна проводка підлягає відображенню в одній і тій же сумі одночасно за дебетом одного рахунку і кредиту іншого".


Задамося питанням – що ж означає слово "відображенню"? У даному випадку це означає, що проводка повинна змінювати залишок на обох рахунках. Точніше, вона повинна зменшувати залишок на одному рахунку і збільшувати на іншому. Якщо мислити примітивно, то нам знадобиться ввести сутність (об'єкт, якщо комусь так більше подобається) "рахунок" і фізично зберігати для неї залишок. Але тут виникає питання, а на яку дату треба зберігати залишок? Або це має бути залишок взагалі (за весь період життя підприємства)? Відповідь – ні, так як і бухгалтеру і керівнику потрібно отримувати залишки на певну дату. Більше того, їм потрібні ще й звороти. Правильно? Думаю – так. І тут треба звернути увагу на кинуте мною по ходу справи слово: отримувати!


Це слово було сказано не спроста. Справа в тому, що зберігати залишки і обороти абсолютно не потрібно. Їх і так можна порахувати в будь-який момент часу. Правильно?


Чую тихий гомін: "при деяких обсягах ці розрахунки просто несерйозно робити кожен раз перераховуючи все з самого початку. Потрібні зрізи …" Правильно, але це всього лише окреме питання продуктивності. І вирішувати його потрібно окремо від питання структури зберігання інформації. Індексовані / матеріалізовані view чудово вирішують проблему продуктивності сервера (та й програміста). Якщо ви ще не перейшли на SQLServer 2000 або Oracle8i (9i), то саме час зробити це. Та й на інших серверах можна грамотно створити таблиці, що містять агреговану інформацію. Головне, що це не привід для нереляційні зберігання даних в реляційної СУБД.


Існує думка, що подвійний запис погано описується засобами реляційних БД і при спробі реалізації призводить до денормализация з обмеженням цілісності. Такої думки дотримується призводить до створення систем, складаються з величезної кількості слабо пов'язаних таблиць, наприклад, окремих таблиць для накладних приходу товару на склад (витрати зі складу) та обліку касових ордерів. Це призводить до суттєвого ускладнення узагальненого аналізу даних, що зберігаються в такому конгломераті таблиць. У принципі, будь-які облікові дані поліморфні за своєю природою. У них завжди можна виділити загальну складову, яка дозволяє узагальнено розглядати дані з різних точок зору. На жаль, при вищеописаному підході цього дуже важко добитися. Так, у наведеному прикладі складські накладні та касові ордери швидше за все будуть містити абсолютно різні поля і, швидше за все, не буде враховано, що і касовий ордер, і накладна в кінцевому рахунку є відображенням переказу грошей чи товару.


Таке "потаблічное" проектування дуже спрощує життя проектувальника. Однак це та простота, яка згодом вийде боком, так як не описує життєвих процесів підприємства, а займається простий реєстрацією документів. Системи, побудовані за таким або подібним принципом можна назвати документо-орієнтованими, або системами, що будуються від документа. Зазвичай такі системи або взагалі не містять бухгалтерії, або містять її у вигляді окремого блоку. Дані з БД потрапляють до бухгалтерії з таблиць документів, зазвичай пакетно, за звітний період. Тільки після цього бухгалтер починає працювати з інформацією, перебуваючи поза зв'язку з реальним життям, тобто реальної облікової ситуацією.


Чи є нормалізованої таблиця, описана в лістингу 2?


Кожен запис в таблиці відображає інформацію тільки про одну сутності – проводці. Ця таблиця не буде мати ні одного повторюється записи, з таблиці не можна винести дані в інші (посилальні) таблиці. Начебто, все говорить про те, що таблиця відповідає найжорсткішим вимогам нормалізації. Значить, на перше запитання відповідаємо позитивно.


Чи можна в цій таблиці зберігати проводки, що відповідають принципам подвійного запису?


Кожна проводка містить інформацію про дебетовом і кредитовому рахунку (у вигляді посилання), причому в ній відображені і дебетуемого, і кредитується рахунку.


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


Щоб не бути голослівним, я створив маленьку тестову базу (див. лістинг 1), що складається з двох таблиць (я буду користуватися синтаксисом SQL Server 2000, але він досить близький до SQL 9x). Перша зберігає якісь аналітичні ознаки. Спростимо завдання і припустимо, що це просто бухгалтерські рахунки. Ось опис цих таблиць у вигляді ER-діаграми:


При цьому, правда, з'являється необхідність синхронізувати суму операції (проводки) з сумою за відповідним деталей операції. Це можна зробити в тригерах або в middleware.


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

Назва гілки # гілки в БД
Товари на складі 1
Продукти харчування 2
Вобла 3
Пиво "Lowenbrau" 4
Одяг 5
Джинси 6
Майки 7
Взуття 8
Кросівки 9
Ласти на підборах 10

можна розгорнути в наступну тимчасову таблицю:









































1


NULL


NULL


1


2


NULL


1


2


3


1


2


4


1


5


NULL


1


5


6


1


5


7


1


8


9


1


8


10


Для агрегації з цієї таблиці потрібно виділити всі значення останньої колонки не містять NULL. При формуванні остаточного звіту потрібно об'єднати (з допомогу OUTER JOIN) результат агрегації з цією таблицею (За останнім полю) і підрахувати проміжні результати. Цей спосіб не вимагає зміни структури БД для зміни рівнів ієрархії. Більш того, можна створювати віртуальні ієрархії (тільки на час побудови запиту).


Але питання може бути пов'язаний не обов'язково з кількісно-сумовим обліком. Ресурс, і тим більше, z-об'єкт (адже атрибути застосовні і до них) може мати різні структуру і кількість атрибутів (аналітичних ознак). Як бути в цій ситуації? Я бачу два виходи – створити універсальну структуру для зберігання необмеженої кількості аналітичних ознак, або створювати (найкраще динамічно) окремі таблиці для зберігання аналітичної інформації для окремих типів z-об'єктів. Чесно кажучи, до цих пір я не можу віддати перевагу одному з варіантів. Динамічні таблиці складніше в реалізації, зате дають можливість зберігати атрибути не тільки посилального типу, але і звичайні атрибути (текстові, числові, булеві, дати). З іншого боку, кілька ускладнюються поліморфні запити, які повертають інформацію відразу по декількох типів z-об'єктів або різним типам ресурсів. У будь-якому випадку, найбільш часто використовувані атрибути є сенс зберігати або безпосередньо в таблиці, що зберігає z-об'екти/ресурси, або в таблиці, яка описує їх типи (наприклад, безпосередньо в z-об'єкті можна зберігати бухгалтерський рахунок або тип z-об'єкта, а в описі типу – вже номер бухгалтерського рахунку). При цьому для z-об'екта/ресурса буде матися поліморфна запис в універсальній таблиці, що зберігає z-об'єкти (деталі операції для ресурсів), і в спеціалізованій таблиці для конкретного типу z-об'ектов/ресурса. Зв'язок між цими таблицями повинна бути один до одного. Власне, це дуже схоже на те, що пропонує документо-орієнтований підхід (зберігання інформації про дебеті і кредиті кожного рахунку в окремій таблиці) тільки в цьому випадку є єднальна таблиця зберігає інформацію про проводці. По суті – це те ж саме, але наведене в нормальну (нормалізовану і несуперечливу) реляційну форму.


Другий варіант теж має на увазі наявність поліморфної таблиці z-об'ектов/ресурсов, але замість динамічного створення таблиць для кожного їх типу використовує одну універсальну таблицю. Схема, що дозволяє задавати необмежену кількість атрибутів для будь-якого z-об'єкта (на малюнку єднальна таблиця має назву Z2Ref), показана на малюнку 1.


Ref – це спрощена довідкова таблиця. У реальному житті її місце повинні зайняти таблиці, що реалізують універсальну (необхідної складності) модель довідників (куди можна динамічно додавати довідники і / або рівні ієрархії). По суті між таблицею Ref і Z з'являється зв'язок "багато до багатьох", тобто з будь-яким z-об'єктом можна асоціювати будь-який елемент довідника і навпаки. Це дозволяє домогтися максимальної гнучкості і при цьому не створювати зайві таблиці (як у попередньому випадку).

Малюнок 1


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


Для початку нам потрібно заповнити таблиці Ref і Z2Ref початковими значеннями. Так як до реального життя цього всього далеко, я заповнив ці таблиці тим, що прийшло до голову:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create table “Ref”
(
“idRef” INT IDENTITY NOT NULL PRIMARY KEY,
“RefName” varchar(100) NOT NULL
)
create table “Z2Ref”
(
“idRef” INT NOT NULL,
“idZ” INT NOT NULL
)
alter table “Z2Ref”
add constraint “Z2Ref_PK”
primary key clustered (“idZ”, “idRef”)

Insert into Ref (RefName) Values ("Васьок")
Insert into Ref (RefName) Values ("Склад 1")
Insert into Ref (RefName) Values ("Петек")
Insert into Ref (RefName) Values ("Склад 2")
Insert into Ref (RefName) Values ("Каса 1")
Insert into Ref (RefName) Values ("Каса 1")
Insert into Ref (RefName) Values ("Нюрка")

Insert into Z2Ref(idRef, idZ) Values(3, 10)
Insert into Z2Ref(idRef, idZ) Values(3, 41)
Insert into Z2Ref(idRef, idZ) Values(1, 50)
Insert into Z2Ref(idRef, idZ) Values(3, 60)
Insert into Z2Ref(idRef, idZ) Values(7, 60)
Insert into Z2Ref(idRef, idZ) Values(1, 71)
Insert into Z2Ref(idRef, idZ) Values(3, 71)
Insert into Z2Ref(idRef, idZ) Values(1, 75)


Припустимо тепер, що нам потрібно отримати обороти для Васька і Петько, тобто для записів 1 і 3 з таблиці Ref (у реальному житті варто було б додати тип z-об'єкта, так як не варто змішувати неоднорідні дані, але тут цього не робиться – через брак такого в спрощеною схемою). Щоб створити такий запит, досить дещо модернізувати попередні запити:

SELECT Agr.Z, Ref.RefName AS Name, Agr.Db, Agr.Cr
FROM (SELECT IsNull(DbS.idRef, CrS.idRef) AS Z,
IsNull(CrS.CrSum, 0) AS Db,
IsNull(DbS.DbSum, 0) AS Cr
FROM (SELECT Z2Ref.idRef,
SUM(Oper.OperSum) AS DbSum
FROM Oper INNER JOIN
Z ON Oper.Db = Z.idZ
INNER JOIN
Z2Ref ON Z.idZ = Z2Ref.idZ
WHERE (Z2Ref.idRef IN (1, 3))
GROUP BY Z2Ref.idRef

) DbS
FULL OUTER JOIN
(SELECT Z2Ref.idRef,
SUM(Oper.OperSum) AS CrSum
FROM Oper INNER JOIN
Z ON Oper.Cr = Z.idZ
INNER JOIN
Z2Ref ON Z.idZ = Z2Ref.idZ
WHERE (Z2Ref.idRef IN (1, 3))
GROUP BY Z2Ref.idRef

) CrS
ON DbS.idRef = CrS.idRef
) Agr
INNER JOIN Ref ON Agr.Z = Ref.idRef

Їх основу, як і раніше, складають два агрегують вкладені запити, що обчислюють обороти по дебету і кредиту вибраних аналітичних ознак. Завдання ознак в цьому прикладі зроблено через оператор in (), в реальних запитах може бути і результатом підзапитах.

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


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

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

Ваш отзыв

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

*

*