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