Re: General Ledger db design

Karl O. Pinc wrote:
>
> 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.

Yeah, there is going to be some kind of extra work here.

My own solution is to add a "closed flag" to the batch and a calculated
column on the GL entries. If the closed flag is "N", the calculated
column is zero, so that the ledger remains in balance while the entries
are going in one-by-one.

A trigger on the batch table traps the setting of closed="Y" and sets
the calculated values to the trx values, so the entire batch is
committed inside of a single transaction. If the batch is not balanced,
it will reject a setting of closed="Y".

Other trigger code prevents new entries to a closed batch or the
re-opening of a batch.

--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200 Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this
::question: do you worry about how to throw away a garbage can?