Коротко о принципах написания бухгалтерской системы
Как вы уже, наверняка, догадались, речь в этой заметке пойдет о разработке бухгалтерского “софта”. Было дело, несколько лет назад мне довелось сопровождать парочку бухгалтерских систем, поэтому я рискну высказать пару рекомендаций на тему “как обустроить бухгалтерию”. Хочу сразу сказать, что речь пойдёт о реализациях на платформе реляционных баз данных, а мои советы адресованы в первую очередь тем заказчикам и разработчикам, которые стремятся объединить транзакционную и аналитическую базы данных.
Конечно, вы можете сразу задать вопрос – а почему возникает необходимость написания системы “с нуля”? Почему бы не построить систему, например, на базе “1С-предприятия” или какого-либо из его аналогов? Оказывается, не всё так просто, как кажется. Дело в том, что покупая “закрытую” систему, вы неизбежно принимаете все действующие в этой системе ограничения, и нередко оказывается, что нужного эффекта можно достичь только, как говорится, “танцем с бубнами”. Кроме того, система может быть недостаточно надёжной и плохо работать на больших объёмах данных. Если вспомнить любимую в России 1С, то я не уверен, что она “выживет” на 10 млн. проводок. В отличие от “закрытых” систем, разработки на платформе реляционных СУБД типа Oracle, Sybase, Informix позволяют, с одной стороны, относительно легко решать оптимизационные проблемы, а с другой стороны – использовать всю мощь ОС Unix. Кроме того, не придётся мучиться с непродуманными, ограниченными (если не сказать, убогими) средствами разработки интерфейса, которыми часто отличаются “закрытые” системы.
Чтобы не быть голословным, я рассмотрю некую воображаемую систему, похожую на реально сопровождаемую мной программу. Эту гипотетическую систему я буду использовать для моделирования процесса взаиморасчетов (клиринга) между фирмами, непосредственно предоставляющими туристические услуги и агентами по продаже туристических путёвок. Все организации, участвующие в клиринге, можно поделить на четыре категории: кемпинги (пансионаты), агентства по продаже путёвок, страховые компании различного профиля и клиринговая палата (КП), координирующая взаиморасчёты. Ключевыми в нашей схеме являются сущности “План счетов”, “Проводка” и “Документ”. Как известно, каждая проводка соответствует финансовой операции по передвижению “суммы проводки”со счёта, задаваемого значением поля “счёт по расходу”, на счёт, задаваемый полем “счёт по приходу”. Документ является финансовой макрооперацией и содержит несколько проводок, выполняемых в заданном порядке. Если таблицы плана счетов, документов и проводок являются основным звеном практически любой бухгалтерской системы, то таблицы “Отчет о продаже путевок” и “Продажи путевок” уже специфичны для описываемой нами схемы туристического бизнеса.
Как я уже говорил, каждое агентство ежемесячно отчитывается перед КП. При этом агентства, во-первых, присылают сам отчёт о продажах в электронном или бумажном виде, а во-вторых, переводят на счёт КП деньги для оплаты отчетов. Переводы могут полностью покрывать сумму отчётов, а могут быть частичными, поэтому один и тот же отчёт может оплачиваться в несколько приемов. Это означает, что одному отчёту может соответствовать несколько документов. В принципе, возможны ситуации, когда несколько отчётов оформляется в виде одного финансового документа. Несложно видеть, что отношение между сущностями “Документ” и “Отчет о продаже путёвок” – типа “многие ко многим”. Это отношение реализуется с помощью связующей таблицы “Продажи путевок”.
Ещё раз повторюсь, что основой всей этой схемы является тройка таблиц: документы, счета и проводки, поэтому в первую очередь – нужно отладить процедуры проведения (в том числе задним числом) и отмены документов. Не забывайте, что при добавлении каждой новой проводки, программа должна списывать сумму проводки с одного счёта и переводить её на другой. Это должно работать при любой погоде и вне зависимости от настроения начальника! Лучше всего реализовать этот механизм с помощью триггеров. Но будьте
бдительны: практически невозможно отследить план выполнения запроса внутри триггера, а изменение порядка обработки соединения таблиц в SQL-запросе может существенно увеличить время выполнения этого запроса. Забегая вперед, скажу, что лучше использовать подсказки оптимизатору. В таблице документов, не считая поля “id документа”, всего два атрибута – “дата” и “тип”. Эти два атрибута являются самыми важными в таблице документов. Почему? Ответ прост: индексирование по дате и типу документу – краеугольный камень эффективного формирования отчётов.
В нашей модели вся финансовая деятельность жёстко привязана к периодам. В конце отчётного месяца каждому агенту и кемпингу нужно посчитать суммы выручки и комиссии. Для этого требуется анализ документов, выполненных в течение месяца. В простейшем случае – это суммирование всех проводок, поступивших на определенный счет.
Чтобы получить “чистый” приход, надо вычесть из полученной величины сумму проводок, в которых счет “выручка” – расходный. Поскольку в процессе функционирования программы количество проведенных документов может составлять сотни тысяч, и даже миллионы – без индекса по дате такое вычисление может затянуться на месяц. По той же самой причине необходимо поле “тип документа”: для того, чтобы максимально быстро выбирать документы, относящиеся к конкретному типу финансовых операций – покрытию отчетов, переводу средств между счетами, или, скажем, выполнению платежных поручений (ПП). Казалось бы, совершенно тривиальные наблюдения, но в сопровождаемой мной системе они были выстраданы. Так, изначально в ней отсутствовала индексация по дате документа, а документы разных типов в некоторых случаях имели одно и то же значение поля “тип документа”. Иногда приходилось реализовывать запутанные алгоритмы определения типа финансовой операции по номерам счетов проводок документа.
Хочу дать еще одну “тривиальную” рекомендацию, которая применима не только к бухгалтерским системам: чаще используйте избыточность представления данных. Так, в нашем примере имеет смысл перенести в таблицу документов поля “агентство” и “кемпинг”- чтобы выбирать документы по оплате отчетов некоторых заданных агентов без обращения к таблице “Отчёты о продажах путевок”.
Можно выразить эту простую мысль и несколько по-другому: храните промежуточные значения. Одним частным, но очень важным случаем промежуточных значений являются величины сумм на счетах агентов и кемпингов (так называемые сальдо). В нашей системе имеет смысл отдельно хранить сальдо счетов на начало и конец каждого обработанного периода. Прочитав предыдущий абзац, некоторые разработчики могут возразить мне, сказав, что избыточность хранения – это уход от нормализации базы данных и замедление выполнения транзакций. Сразу хочу сказать, что это справедливо только в случае, если есть две базы: транзакционная и аналитическая. Спрашивается, а где вы видели такое в российских условиях? Кроме того, вопреки довольно распространенному мнению, нормализация может иногда существенно “затормозить” даже чисто транзакционную базу данных. Простой пример: автоматическая нумерация платежных поручений (ПП). Чтобы реализовать возможность оплаты ПП, расширим нашу систему новой сущностью “Платежное поручение” и сущностью-связкой “Оплата ПП”. Раз уж речь зашла об оптимизации, нельзя умолчать о самих оптимизаторах СУБД.
Работая с СУБД Sybase (далеко не худший продукт на рынке), я пришел к неутешительному выводу: не давайте оптимизатору не единого шанса! Как это ни печально, оптимизатор склонен “заваливаться” на элементарном соединении по двум таблицам – если, например, сисадмин забыл обновить статистику таблиц. Поэтому, чтобы избежать неожиданного замедления работы системы, используйте подсказки оптимизатору. Оптимизатор по определению не знаком с предметной областью, и не всегда может знать параметры запроса. Не надейтесь, что он построит план запроса лучше вас! Если СУБД не позволяет явно указать индекс или порядок выполнения соединения, такой продукт просто нельзя использовать – если, конечно, вы не собираетесь программировать на API низкого уровня. Платёжное поручение (далее – ПП) является, по сути, переводом денег с некоторого расчетного счёта (Р/СЧ) на счёт получателя платежа в другой организации. Каждая “платёжка” обладает своим номером, на единичку превосходящим номер последнего ПП, выполненного с того же расчетного счёта. Программа должна осуществлять нумерацию автоматически, заметьте, что для этого требуется найти ПП, выполненное с того же Р/СЧ, с наибольшим номером. Если вы “забудете” построить соответствующий индекс, то система просто “умрёт”, выполняя единственную операцию несколько секунд или даже минут! Однако, даже если проиндексировать поля “номер ПП” и “расчётный счёт” – всё равно остается проблема блокировок, потому что нужно блокировать таблицу ПП на время выполнения операции, чтобы другой процесс не мог занести туда запись – и изменить, таким образом, максимальный номер платежки. Кроме того, индекс “тормозит ” занесение новых записей, если таблица большая. Все проблемы легко решаются при помощи дополнительной таблицы с двумя полями: “расчётный счёт” и “максимальный номер ПП”, в которой будет храниться максимальный использованный номер ПП для каждого расчетного счета.
Действительно, обычно количество расчётных счетов значительно меньше количества платежных поручений, и время обновления такой таблицы меньше времени обновления индекса.
И в заключение – несколько слов о сопровождении клиент-серверных приложений. Поскольку в России нормативные акты, касающиеся финансовой деятельности в целом и бухучета в частности, сыплются как из рога изобилия – программы приходится менять очень часто. И процессу тестирования, как водится, много времени не уделяется. Значит – очень важно, чтобы изменение одного куска кода не приводило к неработоспособности всей программы. Лучше до минимума сократить зависимости между модулями и таблицами, клиентским и серверным ПО, а также уметь такие зависимости отслеживать. К тому же, необходимо ещё и отслеживать зависимости клиентских модулей от серверных. Разумеется, любая реальная бухгалтерская система будет гораздо сложнее рассмотренной модели, и проблем с её разработкой возникает гораздо больше. Тем не менее – надеюсь, что мои заметки могут быть полезны не только программистам, планирующим написать БД “с нуля”, но и тем, кто сопровождает уже написанную систему.