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?
