Re: General Ledger db design - Mailing list pgsql-general

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@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein