SQLite: бухгалтерский баланс

Автор: Artix (user.su)

Задача: настроить учёт денежных транзакций в соответствии с теорией бух.учета.


Комментарий:

Прежде, конечно, нужно немного упомянуть об основах бух.учета, а именно:
1. В бух.учете есть план счетов с субсчетами
2. Счета делятся на активные и пассивные (и активно-пассивные)
3. На счетах могут находиться остатки (у активных - по дебету, у пассивных - по кредиту)
4 Существуют 4 вида операций с активными/пассивными счетами:
 а.+А -А = ДтА КтА
 б.+П -П = ДтП КтП
 в.+А +П = ДтА КтП
 г.-П -А = ДтП КтА
5. Правило использования активных счетов:  +Д -К (активный растёт по дебету)
6. Правило использования пассивных счетов: -Д +К (пассивный растёт по кредиту)
7. Баланс = Актив + Пассив, где
Актив = Активы+Затраты+Расходы === Пассив = Капитал+Доходы+Обязательства 
Таковы азы теории. Но нас здесь будет интересовать только реализация операций (4).

Решение:

Есть только три базовых способа для учёта проводок в SQL. Разница между ними следующая:

Способ 1: делаются две таблицы - приход и уход.

CREATE TABLE debit (
   account INTEGER,
   money DECIMAL
);

CREATE TABLE credit (
   account INTEGER,
   money DECIMAL
);

Дальнейшая работа с этими таблицами ведётся ТОЛЬКО В ТРАНЗАКЦИЯХ или с помощью хранимых.

Например, делаем проводку 500 руб между счетами 70-44:

START TRANSACTION;
INSERT INTO debit (44, 500);
INSERT INTO credit (70, 500);
COMMIT;

Способ 2: делается одна таблица, но запись ведётся двумя строками:

CREATE TABLE transactions (
   account INTEGER,
   money DECIMAL,
   operation TEXT
);

Работа с этой таблицей также происходит ТОЛЬКО В ТРАНЗАКЦИЯХ и всегда парой запросов (иначе рискуем потерять данные)

START TRANSACTION;
INSERT INTO transactions (44, 500, 'debit');
INSERT INTO transactions (70, 500, 'credit');
COMMIT;

Способ 3: делается одна таблица, но запись ведётся в два столбца:

CREATE TABLE transactions (
    debit INTEGER,
    credit INTEGER,
    money DECIMAL
);

Работать с этой таблицей можно уже без транзакций, поскольку в одной строке меняются сразу и дебет и кредит.

INSERT INTO transactions (44, 70, 500);

Получение для этой таблицы баланса выглядит так (SQL-CTE):

WITH deb AS (
   SELECT debit as account, SUM(money) as D, 0 as C FROM transactions GROUP BY 1
), cre AS (
   SELECT credit as account, 0 as D, SUM(money) as C FROM transactions GROUP BY 1
), diff AS (
   SELECT account, SUM(D) as D, SUM(C) as C, SUM(D)-SUM(C) as X FROM (
      SELECT account, D, C FROM deb
      UNION ALL 
      SELECT account, D, C FROM cre
   ) T GROUP BY 1
)
SELECT SUM(D-C) as fin_balance FROM diff;

Совершенно не сложно обойтись и без CTE с помощью пары вложенных запросов.
CTE в данном случае для наглядности: deb-это сумма по дебиту, cre-сумма по кредиту, diff-обороты (X).
В результате получаем проверочный ноль в качестве баланса.
Точно также легко сделать похожие запросы для Способа 1 и Способа 2.

А выводы здесь такие:

А) Способ 2 - точно плохой. У него "слабые" индексы, лишнее поле (флаг операции),
   перегрузка по количеству строк, и, очевидно, самая маленькая производительность.

Б) Способ 3 - хорош только своей компактностью и простотой. Хорош, например, для таблиц
   типа MySQL-ISAM, где нет поддержки транзакций. Но в перспективе плох как раз отсутствием
   транзакций, поскольку в операциях, где корреспондируют три и более счетов, - транзакций
   все равно не избежать.

В) Способ 1 - наиболее эффективный (И ТОЛЬКО С ТРАНЗАКЦИЯМИ!). Он разделяет поступления от списаний
   (уже легче выполнить половину стандартных запросов), таблицы компактны (и в два раза меньше по
   количеству строк, чем в Способе 3), индексы эффективны и производительны.

Когда система занимается обработкой миллионов микроплатежей - то лучше выбрать Способ 1.

Конечно, перечисленные выше способы максимально упрощены и не достаточны для практики.
На практике для гигантского числа транзакций нужна более продвинутая таблица, например такая
(расширяем самый простой Способ 3):

CREATE TABLE transactons (
   transaction_id PRIMARY KEY,
   transaction_date DATETIME (индексируемое),
   partition_param INTEGER,
   operation_number INTEGER (индексируемое),
   debit INTEGER (индексируемое),
   credit INTEGER (индексируемое),
   money DECIMAL,
   debit_nm INTEGER,
   credit_nm INTEGER,
   activation_time DATETIME,
   owner_link INTEGER (индексируемое),
   document_link INTEGER,
   archive_level INTEGER,
   comment VARCHAR(100) (никогда нельзя TEXT или BLOB!)
);

Здесь добавлены:

transaction_id   - уникальный и никогда не повторяющийся идентификатор транзакции
transaction_date - дата+время транзакции (с учётом UTC конечно)
operation_number - номер или идентификатор финансовой операции,
                   в рамках которой могут быть сделаны несколько транзакций
partition_number - параметр, необходимый для партицирования (например год)
activation_time  - дата-время "активации" проводки (поскольку фин. операция 
                   может быть запланирована но не проведена)
debin_nm, credit_nm - связь с номенлатурой, характеризующей аналитический учёт
owner_link       - связь с человеком-оператором базы, делающим фин.операцию
document_link    - связь с документом - основанием для фин. операции
archive_level    - чтобы избежать группировок миллионов строк, можно сделать такой фокус:
                   1) увечить arhive_level с 0 до operation_number+1 для "миллиона строк".
                   2) группировать этот "миллион" и занести его как новую финансовую
                   операцию с номером operation_number+1
                   3) в дальнейшем суммировать только проводки с archive_level=0

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

И еще один важный совет: нужно всегда опираться на те фактические данные, которые есть в учете.
Если, к примеру, на одно списание приходится миллион приходов, то Способ 1 - не повысит эффективность
учёта и необходимо искать какие-то другие параметры для декомпозиции. Цель с технической точки
зрения одна и та же: разложить "миллионы строк" на такие индексируемые блоки, которые будут
удобны для использования и одновременно позволят избежать больших переборов данных.


#