Re: Accounting Schema

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(at)catalyst(dot)net(dot)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