Re: Accounting Schema
От | Andrew McMillan |
---|---|
Тема | Re: Accounting Schema |
Дата | |
Msg-id | 3AF524A4.DBBB80CA@catalyst.net.nz обсуждение исходный текст |
Ответ на | Web-based Problem/Project tracking system ... (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-general |
Dave Cramer wrote: > > Hi all, > > Can anyone out there with more experience than me give me some hints as to > how to do accounting tables in a db. The problem I am wrestling with is how > to represent credits, and debits. > One way is to identify credits and debits with a transactiontype, and keep > all of the numbers positive which models the way accountants do it. This > makes things liking calculating the balance difficult with a sql statement. > > Any hints would be appreciated. In accounting systems I have written I seem to be evolving towards a schema along these lines: AccountGroup ( <pk>AccountGroupCode</pk>, Description ) ChartOfAccount ( <pk>AccountCode</pk>, <fk>AccountGroupCode</fk>, Name ) AcctgEntityType ( <pk>AcctgEntityTypeCode</pk>, Description ) AcctgEntity ( <pk>AcctgEntityCode</pk>, <fk>AcctgEntityTypeCode</fk>, Description ) AccountTransaction ( <fk>AcctgEntityCode</fk>, <fk>AccountCode</fk>, Description, Amount , Date, <fk>FinancialPeriodCode</fk> ) From there I denormalise substantially, holding balance records which are the sum of the AccountTransaction records for FinancialPeriods (maintained by a trigger). I hold budget / revised budget figures on those records as well. All of this also gets summarised (again by triggers) to maintain a "current balance" which is the sum of all transactions, ever (and budgets etc). The denormalisation really helps reporting because most reports want figures at the end of some financial period, usually the one just prior to the current one. This means that taking the "current balance" and subtracting any balances for months after the end of the period we're looking for is (heuristically) the quickest way to get a balance as at the end of any period. The accounting systems I have constructed in this way typically hold around fourteen years of data now, so that heuristic really does apply in most real-world cases. I could hold a cumulative balance for each period, rather than just a total transactions for the period, but it is much more work to maintain the consistency of data in that sort of form, and accounting systems need to be provably giving the correct answers. As for the amounts: NUMBER would seem the best type to represent them, although INT8 could also be a nice way. The decision would largely rest on the handling of multiple currencies. In my experience Credits are universally recognised as nasty things which we don't want to have too many of, and should be portrayed in as negative a manner as possible.... :-) Feel free to enquire further by private e-mail if you are interested in more details. Regards, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@catalyst.net.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709
В списке pgsql-general по дате отправления: