Эта тема частично является ответом на вопросы поставленные в
http://www.rsdn.ru/forum/message.asp?mid=28895&only

Автор: Willi
Дата: 14.02.02

и в
http://www.rsdn.ru/forum/message.asp?mid=29453&only

Автор: Tiger
Дата: 18.02.02

Но зи-за того, что она включает ответы сразу на несколько вопросов, и одновременно является предложением ко всем участникам, я решил вынести ее в отдельную ветку.

Для начала обсудим постулаты бухучета, вокруг которых разгарелся спор...

Итак. "Каждая хозоперация подлежит отражению в одной и той же сумме одновременно по дебету одного счета и кредиту другого.". Уберем из этого определения плохо детерминированное понятие хозоперация и введем понятие проводка. Так всем будет понятнее. Получится: "Каждая проводка подлежит отражению в одной и той же сумме одновременно по дебету одного счета и кредиту другого.".
Зададимся вопросом – что же означает слово отражению? В данном случае — это означает, что проводка должна изменять остаток на обоих счетах. Точнее она должна уменьшать остаток на одном счете и увеличивать на другом. Если мыслить примитивно, то нам понадобится ввести сущность (объект, если кому так больше нравится) "счет" и физически хранить для нее остаток. Но тут возникает вопрос, а на какую дату нужно хранить остаток? Или это должен быть остаток вообще (за весь период жизни предприятия)? Ответ – нет, так как и бухгалтеру и управленцу нужно получать остатки на определенную дату. Более того им нужны еще и обороты. Правильно? Думаю – да. И тут нужно обратить внимание на брошенное мною, походу дела, слово: получать!
Это слово было сказано не спроста. Дело в том, что хранить остатки и обороты совершенно не нужно. Их и так можно посчитать в любой момент времени. Правильно?
Слышу тихий ропот: при некоторых объемах эти расчеты просто несерьезно делать каждый раз пересчитывая все с самого начала. Нужны срезы... Правильно, но это всего лишь отдельный вопрос производительности. И решать его нужно отдельно от вопроса структуры хранения информации. Индексированные/материализованные view прекрасно решают проблему производительности сервера (да и программиста). Если вы еще не перешли на SQLServer 2000 или Oracle, то это ваши личные проблемы. Да и на других серверах можно создать грамотные таблицы содержащие агрегированную информацию. Главное, что это не повод для нереляционного хранения данных в реляционной СУБД. И не повод для заявлений делаемых George_Seryakov. Ну, да ладно... продолжим разбор полетов. :)
Ответим на вопрос alexm1202 (Является ли нормализованной таблица описанная им и мной?). Ответим также и на другой вопрос. Можно ли в этой таблице хранить проводки отвечающие принципам двойной записи?
Каждая запись в таблице отражает информацию только об одной сущности – проводке. Данная таблица не будет иметь ни одной повторяющейся записи, из таблицы нельзя вынести данные в другие (ссылочные) таблицы. Вроде все говорит – о том, что таблица соответствует самым жестким требованиям нормализации. Значит на первый вопрос отвечаем положительно (Если есть возражения, просьба возражать по существу, а не уходить от темы).

Теперь ответим на второй вопрос...

Каждая проводка содержит информацию о дебетовом и кредитовом счете/аналитическом признаке (в виде ссылки). Причем в ней отражен дебетуемый и кредитуемый счета. С эти вроде спорить бесполезно? Или как?
Проводка содержит дату ее совершения и сумму. Общая совокупность даты проводки, суммы и направление перемещения денег дает нам возможность записывать, таким образом, нормальные бухгалтерские проводки удовлетворяя всем требованиям двойной записи. Просто, вместо учета изменения счетов мы начали учитывать изменения этих самых счетов во времени. Никто не запрещает записывать проводку как два отдельных изменения счетов (дебетового и кредитового), но тогда и появляется та самая проблема с "нереляционностью" и "нерормализованностью".

Чтобы не быть голословным я создал маленькую тестовую базу состоящую из двух таблиц (я буду пользоваться синтаксисом SQL Server 2000, но он довольно близок к SQL 9x). Первая хранит некие аналитические признаки. Упростим задачу и предположим, что это просто бухгалтерские счета. Вот ее описание на SQL:

CREATE TABLE Z
(
    idZ int NOT NULL PRIMARY KEY,
    ZName varchar(100) NOT NULL 
)

Вторая таблица – это таблица содержащая проводки:

CREATE TABLE Oper
(
    idOper int IDENTITY (0, 1) NOT NULL PRIMARY KEY,
    Db int NOT NULL FOREIGN KEY REFERENCES Z,
    Cr int NOT NULL FOREIGN KEY REFERENCES Z,
    OperDate datetime NOT NULL,
    OperSum float NOT NULL 
)

А вот описание этих таблиц в виде ER-диаграммы:

Как видно из ее определения поля Db, Cr являются ссылками на аналитическую таблицу. В принципе каждая запись аналитической таблицы может содержать неограниченное количество дополнительных аналитических признаков, но суть от этого не изменится. Главное, что мы совершаем перевод денег с одной единицы аналитики на другую (равнозначную по глубине/уровню). Посчитать остатки/обороты для такого вида таблиц довольно просто. Для начала заполним наши таблицы данными:

Insert into Z(idZ, ZName) Values(1, 'OC')
Insert into Z(idZ, ZName) Values(2, 'Амортизация ОС')
Insert into Z(idZ, ZName) Values(10, 'Склад материалов')
Insert into Z(idZ, ZName) Values(41, 'Товоры')
Insert into Z(idZ, ZName) Values(46, 'Реализация')
Insert into Z(idZ, ZName) Values(50, 'Касса')
Insert into Z(idZ, ZName) Values(51, 'Расчетный счет')
Insert into Z(idZ, ZName) Values(60, 'Поставщики')
Insert into Z(idZ, ZName) Values(71, 'Овансовые отчеты')
Insert into Z(idZ, ZName) Values(75, 'Учт.кап.')

SET DATEFORMAT dmy
Insert into Oper(Db, Cr, OperDate, OperSum) Values(51, 75, '01.01.2002', 1000)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(50, 51, '02.01.2002', 300)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(71, 50, '03.01.2002', 100)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(10, 71, '04.01.2002', 55)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(50, 71, '04.01.2002', 40)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(60, 50, '05.01.2002', 200)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(41, 60, '06.01.2002', 180)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(51, 60, '06.01.2002', 20)
Insert into Oper(Db, Cr, OperDate, OperSum) Values(50, 71, '07.01.2002', 5)

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

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

Общий алгоритм совсем прост нужно выбрать необходимые записи (за некоторый период) из таблицы проводок и посчитать их сумму для дебета и кредита каждого из счетов. К сожалению записать это одним запросом (без подзапросов) невозможно (если кто извернется, то покажите как). Я знаю два обхода данной проблемы. Первый известный мне способ – мы можем создать основной запрос который будет возвращать список аналитических признаков, а обороты и остатки подсчитывать в простых подзапросах. Второй – можно произвести агрегацию отдельно для дебетовых и кредитовых признаков и соединить полученные запросы с помощью FULL OUTER JOIN. Я продемонстрирую второй подход:

SELECT IsNull(DbS.Db, CrS.Cr) AS Z, 
       IsNull(CrS.CrSum, 0) AS Db, 
       IsNull(DbS.DbSum, 0) AS Cr
   FROM
      (SELECT  Cr, SUM(OperSum) AS CrSum
         FROM  Oper
         GROUP BY Cr) as CrS
     FULL OUTER JOIN
      (SELECT  Db, SUM(OperSum) AS DbSum
         FROM  Oper
         GROUP BY Db) as DbS
    ON CrS.Cr = DbS.Db

Данный запрос содержит два подзапроса (выделенных жирным) которые рассчитывают обороты по дебету и кредиту аналитических признаков.
Если нужно вычислять оборот и остаток за определенный период, нужно добавить в подзапросы фильтрацию необходимого периода. Приведенный выше запрос должен будет сам стать подзапросом. Дело в том что для полноценной оборотки нужно рассчитать остаток на начало периода и оборот за период. Техника тут будет примерно такая же, так что вряд ли ее стоит касаться. Тот же FULL OUTER JOIN по аналитическим признакам... Остаток на конец рассчитывается путем сложения остатка на начало периода и оборота за период.
Если нужно добавить название аналитического признака, то можно сделать еще одну вложенность и JOIN со справочной таблицей. Вот вариант для нашего примера:

SELECT  sb.Z, sb.Db, sb.Cr, Z.ZName
   FROM Z INNER JOIN
      (SELECT IsNull(DbS.Db, CrS.Cr) AS Z, 
              IsNull(CrS.CrSum, 0) AS Db, 
              IsNull(DbS.DbSum, 0) AS Cr
          FROM   
             (SELECT Cr, SUM(OperSum) AS CrSum
                FROM Oper
                GROUP BY Cr) as CrS FULL 
            OUTER JOIN 
             (SELECT Db, SUM(OperSum) AS DbSum
                FROM Oper
                GROUP BY Db) as DbS
                ON CrS.Cr = DbS.Db
      ) as sb ON Z.idZ = sb.Z

Вот результат выполнения запроса на приведенных данных:

В данном случае большая вложенность запросов не будет приводить к понижению производительности, так как только самые глубокие запросы занимаются действительно серьезной обработкой данных. Остальные уже работают с относительно небольшим количеством агрегированных данных.
Стоит так же обратить внимание на то, что при соединении результата с записями из таблицы аналитики применяется INNER JOIN. INNER JOIN более эффективен, да и если применить OUTER JOIN, то в запрос попадут "лишние записи" (аналитики для которой не было оборотов).

Willi задал вопрос:
W>Все это конечно очень здорово (без всякой иронии).
W>Но вот простой пример, я хочу получить баланс, причем не просто по счетам, но и с W>группировкой по аналитикам. Что-то вроде:

Товары на складе                            1000
    Продукты питания                         200
        Вобла                                 50
        Пиво "Lowenbrau"                     150
    Одежда                                   400
        Джинсы                               250
        Майки                                150  
    Обувь                                    400
        Кроссовки                            200
        Ласты на каблуках                    200

W>Здесь
W>"Товары на складе" — это счет,
W>"Продукты питания", "Одежда", "Обувь" — аналитики типа "Вид товара"
W>все остальное — аналитики типа "Товар"
W>
W>Если данне хранятся в виде

tblEntries
----------
entry_id
entry_volume
account_id
analytic_0_id
analytic_1_id
...
analytic_N_id

W>то написав

SELECT 
  SUM( entry_volume )
...
GROUP BY 
  account_id, 
  analytic_0_id
  analytic_1_id
  ...
  analytic_N_id

W>мы тут же получим нужный нам результат.
W>А как быть в случае с более универсальным хранением аналитических атрибутов?

Первое что хочется здесь заметить – это то что пример не очень удачен. Дело в том, что в данном примере определяются не атрибуты проводки, а атрибуты номенклатуры. Количественный учет – в некотором смысле учет в другом измерении. Не в денежном, а в количественном. Точнее в количественно-денежном. Стало быть мы получаем не аналитические признаки проводки (по дебету или кредиту), а атрибуты единицы измерения. Тип товара это аналитика именно еденицы измерения. И пожалуй не следовало бы причислять ее к дебету или кредиту. Более того я бы даже посоветовал не хранить ее в проводке. В проводке нужно хранить только сумму в деньгах. Количественный учет лучше вынести в отдельную таблицу содержащую ссылку на проводку. При этом можно будет добавлять несколько количественно-суммовых записей для одной проводки, а проводка будет содержать общую суму по всем этим записям. Таким образом проводка будет похожа на простую накладную, что очень хорошо отражает реальную жизнь. Назовем таблицу в которой будут лежать детали проводки OperDet (детали операции). Номенклатура в этом случае учитывается как ссылка (поле в таблице количественно-суммового учета) на таблицу(ы)-справочник номенклатур. Вот ее описание и описание сопутствующих таблиц:

create table "Product" ( 
   "idProd" INT IDENTITY NOT NULL PRIMARY KEY,
   "ProdName" varchar(100) NOT NULL,
   "ProdGroup" varchar(20) NOT NULL)  
go
create table "OperDet" ( 
   "idOper" INT NOT NULL,
   "idProd" Int Not Null,
   "DetCount" FLOAT NOT NULL,
   "DetPrice" FLOAT NOT NULL)
go
alter table "OperDet"
   add constraint "OperDet_PK" 
      primary key ("idOper", "idProd")
alter table "OperDet"
   add constraint "Oper_OperDet_FK1" 
      foreign key("idOper")
        references "Oper" ("idOper")

А вот ER-диаграмма:

При этом, правда, появляется необходимость синхронизировать сумму операции (проводки) с суммой по соответствующим деталям операции, но это можно сделать в триггерах или в middleware. При этом вопреки заблуждению Willi-а замечательное структурирование (продемонстрированное им) не породило множество аналитических признаков. По сути признак один – номенклатура. А деревце нарисованное им всего лишь структурирование самих признаков. Можно хранить такой справочник как дерево (в виде специальной реляционной структуры) и перед агрегацией выбирать необходимые листья этого дерева (которые и являются номенклатурой), а затем делать соединение с таблицей деталей проводки и с самой таблицей проводки.

Но вопрос может быть связан не обязательно с количественно-суммовым учетом. Каждый тип z-объекта может иметь различную структуру и количество аналитических признаков. Как быть в этой ситуации? Я вижу два выхода создать универсальную структуру для хранения неограниченного количества аналитических признаков и создавать (лучше всего динамически) отдельные таблицы для хранения аналитической информации для отдельных типов z-объектов. Честно говоря до сих пор я не могу отдать предпочтение одному из вариантов. Динамические таблицы сложнее в реализации, за то дают возможность хранить атрибуты не только ссылочного типа, но и обычные атрибуты (текстовые, числовые, булевы, даты). С другой стороны несколько усложняется полиморфные запросы (запросы возвращающие информацию сразу по нескольким типам z-объектов. В любом случае наиболее часто используемые атрибуты есть смысл хранить или непосредственно в таблице хранящей z-объекты или в таблице описывающей их типы (например, в ней можно хранить бухгалтерский счет). При этом для z-объекта будет иметься полиморфная запись в универсальной таблице хранящей z-объекты и в специализированной таблице для конкретного типа z-объектов. Связь между этими таблицами должна быть один к одному. Собственно это очень похоже на то, что предлагал George_Seryakov только в этом случае имеется связующая таблица хранящая информацию о проводке. По сути – это тоже самое, но приведенное в нормальную реляционную форму.

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

Ref – это справочная таблица. В реальной жизни ее место должны занять таблицы реализующие универсальную (необходимой навороченности) модель справочников (в которой можно динамически добавлять справочники). По сути между таблицей Ref и Z появляется связь многие ко многим, т.е. с любым z-объектом можно ассоциировать любой элемент справочника и наоборот. Это позволяет добиться максимальной гибкости и при этом не создавать лишние таблицы (как в предыдущем случае).

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

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

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. Чтобы создать такой запрос достаточно несколько модернизировать предыдущие запросы:

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(), в реальных запросах может быть и результатом подзапроса.

Конечно в реальной жизни к этому и так немаленькому запросу прибавятся еще не мало дополнительных прибамбасов. Это может привести к тому, что запросы станут плохо читаемы. Поэтому для их создания следует применять шаблоны или даже некий псевдоязык который будет скрывать сложность реальных запросов. Можно так же попросту разбить запрос на несколько отдельных запросов, а связывание этих запросов производить средствами TSQL (PLSQL для Oracle) или в middleware. Главное учитывать, что дебетовый подзапрос есть "зеркальное" отражение кредитового, а первый уровень всего лишь добавляет текстовые описания к аналитическим признакам.

Заключение

Все проблемы с формированием базы и запросов разрешимы. Я больше задумываюсь над тем, что к универсальной структуре базы нужны универсальные механизмы работы. В далеком 96-м мы решили создать набор компонентов который стал бы оберткой над этой универсальной структурой и смог бы быть дополнительным уровнем абстракции. Первую попытку мы сделали в 97-ом. В качестве объектной модели мы выбрали COM и ActiveX. В качестве первого средства реализации использовалась Delphi. Мы сделали набор визуальных компонентов для работы со справочниками, тестовую версию которых можно скачать с нашего сайта http://www.optim.ru/Software/rus/invoice/download.asp. Опыт разработки таких компонентов показал, что для их создания нужно применять middleware-слой (первые компоненты были сделаны в двухуровневой технологии клиент-сервер) и нужна независящая от языка и имеющая ряд особенностей технология доступа к данным. Одной из особенностей такой технологии является возможность передачи и динамического изменения метаданных для каждого элемента курсора. Второй вывод сделанный нами – нужна инфраструктура позволяющая динамически создавать пользовательский интерфейс. Именно тогда мы приняли решение создать ascDB и ascContainer. В виду серьезных ограничений накладываемых (особенно в те времена) Delphi (вернее VCL) мы приняли решение использовать для создания этих компонентов VC и ATL (тогда еще толи 1 толи 2). Мы назвали эти компоненты нижнем слоем ядра конструктора учетных систем, а сам подход проблемно ориентированными компонентами. Собственно сначала даже не планировалось делать эти библиотеки коммерческими (они предназначались только для внутреннего использования).

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

Так вот, ... к чему я виду? Не хочет ли кто присоединится к этому проекту. Минимум, что нужно это более детально сформулировать идеи (на бумаге) и обсудить эти технологии. Так же было бы интересно заполучить алфа- и бета-тестеров которые помогали бы нам в создании этого конструктора. Ну, и естественно мы не отказались бы от сотрудничества с заинтересованными фирмами (особенно с богатыми) и частными лицами. В зависимости от доли участия первая версия этого продукта вам будет передана или вообще бесплатно или с большой скидкой, с теми кто готов но болшее сотрудничество участие и выгоду можно обговорить отдельно. Ну, и естественно вы получите возможность получать ответы на свои вопросы и использовать беты для создания пилотных версий ваших продуктов.