On 02/26/2007 07:40:17 AM, Kenneth Downs wrote:
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.
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.
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.
I haven't entirely focused my brain around this problem, but
it seems to me that you still get rows in, e.g., the financial
transaction detail table -- the credits and debits -- that
don't balance out for some period of time. Forever if the
application has a bug. Why is your approach better than
just putting the "check that everything balances" code,
plus whatever updating you want to do elsewhere in the db,
directly into the parent table's trigger (the batch table)
and having the application insert into the batch table last?
You can put triggers into the financial transaction detail table
that says that the batch id has to be valid if it exists
to get your referential integrity right.
You can also not allow new rows to be inserted if there
is already a batch row, thus the insertion of a
batch row "closes" the batch. Trying to add new credits
or debits or change the values of existing credits or
debits (prevented in the cr/db table's update trigger), things
would cause the batch to go out of balance, are thus
prevented.
If something
goes wrong, you've got some extra rows laying about and
you can easily identify them because there's no corresponding
row in in the batches table. (Your proposal has good error
recovery too, but seems like it's more work to impliment,
as far as having to go back and update the "closed" flag,
and even more instruction needs to be given to the
application programmer come time to use the db.)
Karl <kop@xxxxxxxx>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
