Re: General Ledger db design

On 02/25/2007 06:21:45 PM, Kenneth Downs wrote:
> Martin Winsler wrote:

>> This is a real world situation where referential
>> integrity needs to be broken in theory, I believe. Does anybody
>> have any experience or knowledge of building financial accounting
>> databases? Am I wrong about this?
>>
>> The problem is that with "double entry accounting" you have records
>> in tables that both reference other records in the same table as
>> well as different records in other tables depending on some fairly
>> complex logic.
>> For instance an invoice is a financial instrument, so the "parent
>> record" would naturally want to be part of a company wide "journal"
>> or "ledger." However, its child records would be actual invoice
>> lines as well as two different sets of entries in the general ledger
>> detail, all 3 sets of records must agree with each other on the
>> invoice parent record total.

> The solution I've always used is to introduce a table of batches.
> This is the table that unifies all of the others. When you post an
> invoice, you generate a new batch, give it type "AR". The invoice is
> stamped with the batch #, as are the GL transaction rows. When you
> post an AP voucher, do the same thing. Same for checks received,
> checks paid, etc, all of them have different batch types.

It's been a while since I've done finance apps but
this is my recollection of the situation.

The above proposal takes care of the data
structure/referential integrity
issues, but does not solve the data integrity issues.

The only way, at present, to solve the data integrity
issues is to write a FOR EACH STATEMENT trigger to be sure that
all the rows agree with each other and everything balances.
But this can only be done after all the data goes into the database.
For instance, insert the credit and debit rows
into a temporary table, then insert from the temporary
table into the actual GL transaction table in one go,
and have a AFTER ... FOR EACH STATEMENT go through
and make sure the entire ledger is still in balance.
From a performance standpoint this bites.

Of course you can insert the financial transaction
rows before inserting a row in the table of batches,
or whatever the parent table is. Then write
triggers on the batch table to make sure everything
stays in balance. Your business
rules are enforced, for every batch that exists,
but your referential integrity is lost and you
can wind up with dangling child rows. All the same
I sorta prefer this solution because it seems
to me that the mess is easier to clean up.

The traditional solution has always been to make sure all your
applications have no bugs. They need to do everything
in transactions and always insert both the credit and
debit sides of every financial transaction, otherwise
the ledger (or whatever) can get out of balance because one
side or another of the financial transaction is missing.

(I don't know why the traditional solution
is so popular. Maybe because it was always
done this way before ACID compliant databases.
Or, could be because it puts the onus for cleaning up the
mess on the accountants, and they're used to
it because it's the same sort of mess they've
always had to clean up. Or it could be because
application programmers hate it when the db
gives them errors and figure they do a good
enough job that it's not a problem. I've
also heard people complain about triggers
because they don't manage their code base
and don't know what triggers exist after
a while.)

You pretty much have the choice of either enforcing
business rules or enforcing referential integrity,
but not both. At least that was the conclusion I
recalling coming to back when I was doing finance stuff.

FWIW, I have long lusted after a per-row trigger that would
fire on transaction commit to solve these problems.
(Or any sort of trigger with access to the row
data so that it can be checked.)
I couldn't say whether such triggers are technically feasible,
but I'm pretty sure nobody's
interested enough to do the implementation.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein