Блог → Коротко о принципах написания бухгалтерской системы

Как вы уже, наверняка, догадались, речь в этой заметке пойдет о разработке бухгалтерского "софта". Было дело, несколько лет назад мне довелось сопровождать парочку бухгалтерских систем, поэтому я рискну высказать пару рекомендаций на тему "как обустроить бухгалтерию". Хочу сразу сказать, что речь пойдёт о реализациях на платформе реляционных баз данных, а мои советы адресованы в первую очередь тем заказчикам и разработчикам, которые стремятся объединить транзакционную и аналитическую базы данных.

Конечно, вы можете сразу задать вопрос - а почему возникает необходимость написания системы "с нуля"? Почему бы не построить систему, например, на базе "1С-предприятия" или какого-либо из его аналогов? Оказывается, не всё так просто, как кажется. Дело в том, что покупая "закрытую" систему, вы неизбежно принимаете все действующие в этой системе ограничения, и нередко оказывается, что нужного эффекта можно достичь только, как говорится, "танцем с бубнами". Кроме того, система может быть недостаточно надёжной и плохо работать на больших объёмах данных. Если вспомнить любимую в России 1С, то я не уверен, что она "выживет" на 10 млн. проводок.

В отличие от "закрытых" систем, разработки на платформе реляционных СУБД типа Oracle, Sybase, Informix позволяют, с одной стороны, относительно легко решать оптимизационные проблемы, а с другой стороны - использовать всю мощь ОС Unix. Кроме того, не придётся мучиться с непродуманными, ограниченными (если не сказать, убогими) средствами разработки интерфейса, которыми часто отличаются "закрытые" системы. Чтобы не быть голословным, я рассмотрю некую воображаемую систему, похожую на реально сопровождаемую мной программу.

Эту гипотетическую систему я буду использовать для моделирования процесса взаиморасчетов (клиринга) между фирмами, непосредственно предоставляющими туристические услуги и агентами по продаже туристических путёвок. Все организации, участвующие в клиринге, можно поделить на четыре категории: кемпинги (пансионаты), агентства по продаже путёвок, страховые компании различного профиля и клиринговая палата (КП), координирующая взаиморасчёты.

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

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

Это означает, что одному отчёту может соответствовать несколько документов. В принципе, возможны ситуации, когда несколько отчётов оформляется в виде одного финансового документа. Несложно видеть, что отношение между сущностями "Документ" и "Отчет о продаже путёвок" - типа "многие ко многим". Это отношение реализуется с помощью связующей таблицы "Продажи путевок".

Ещё раз повторюсь, что основой всей этой схемы является тройка таблиц: документы, счета и проводки, поэтому в первую очередь - нужно отладить процедуры проведения (в том числе задним числом) и отмены документов. Не забывайте, что при добавлении каждой новой проводки, программа должна списывать сумму проводки с одного счёта и переводить её на другой. Это должно работать при любой погоде и вне зависимости от настроения начальника! Лучше всего реализовать этот механизм с помощью триггеров. Но будьте бдительны: практически невозможно отследить план выполнения запроса внутри триггера, а изменение порядка обработки соединения таблиц в SQL-запросе может существенно увеличить время выполнения этого запроса. Забегая вперед, скажу, что лучше использовать подсказки оптимизатору.

В таблице документов, не считая поля "id документа", всего два атрибута - "дата" и "тип". Эти два атрибута являются самыми важными в таблице документов. Почему? Ответ прост: индексирование по дате и типу документу - краеугольный камень эффективного формирования отчётов. В нашей модели вся финансовая деятельность жёстко привязана к периодам. В конце отчётного месяца каждому агенту и кемпингу нужно посчитать суммы выручки и комиссии. Для этого требуется анализ документов, выполненных в течение месяца. В простейшем случае - это суммирование всех проводок, поступивших на определенный счет. Например, сумма прихода на счёт кемпинга "выручка" бы быть получена на языке SQL с помощью такого запроса:

SELECT SUM(cyMMa проводки)
FROM документ D,проводка Е
WHERE D.дата документа between "начало месяца" AND "конец месяца" AND D.id документа = Е. id документа AND Е.счёт по приходу = "выручка"


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

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

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

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

Раз уж речь зашла об оптимизации, нельзя умолчать о самих оптимизаторах СУБД. Работая с СУБД Sybase (далеко не худший продукт на рынке), я пришел к неутешительному выводу: не давайте оптимизатору не единого шанса! Как это ни печально, оптимизатор склонен "заваливаться" на элементарном соединении по двум таблицам - если, например, сисадмин забыл обновить статистику таблиц. Поэтому, чтобы избежать неожиданного замедления работы системы, используйте подсказки оптимизатору. Оптимизатор по определению не знаком с предметной областью, и не всегда может знать параметры запроса. Не надейтесь, что он построит план запроса лучше вас! Если СУБД не позволяет явно указать индекс или порядок выполнения соединения, такой продукт просто нельзя использовать - если, конечно, вы не собираетесь программировать на API низкого уровня.

Платёжное поручение (далее - ПП) является, по сути, переводом денег с некоторого расчетного счёта (Р/СЧ) на счёт получателя платежа в другой организации. Каждая "платёжка" обладает своим номером, на единичку превосходящим номер последнего ПП, выполненного с того же расчетного счёта. Программа должна осуществлять нумерацию автоматически, заметьте, что для этого требуется найти ПП, выполненное с того же Р/СЧ, с наибольшим номером. Если вы "забудете" построить соответствующий индекс, то система просто "умрёт", выполняя одну-единственную операцию несколько секунд или даже минут!

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

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

И в заключение - несколько слов о сопровождении клиент-серверных приложений. Поскольку в России нормативные акты, касающиеся финансовой деятельности в целом и бухучета в частности, сыплются как из рога изобилия - программы приходится менять очень часто. И процессу тестирования, как водится, много времени не уделяется. Значит - очень важно, чтобы изменение одного куска кода не приводило к неработоспособности всей программы. Лучше до минимума сократить зависимости между модулями и таблицами, клиентским и серверным ПО, а также уметь такие зависимости отслеживать.

Что касается вопроса отслеживания зависимостей, то увы, пока что единственным более-менее приемлемым средством отслеживания изменений в базе данных является "глючный" Erwin. К тому же, необходимо ещё и отслеживать зависимости клиентских модулей от серверных. Насколько я знаю, единственным продуктом, позволяющим это делать, является Rational Rose. К сожалению, в Rational Rose поддержка СУБД появилась совсем недавно, и по функциональности она не "дотягивает" до Erwin.

Разумеется, любая реальная бухгалтерская система будет гораздо сложнее рассмотренной модели, и проблем с её разработкой возникает гораздо больше. Тем не менее - надеюсь, что мои заметки могут быть полезны не только программистам, планирующим написать БД "с нуля", но и тем, кто сопровождает уже написанную систему.