Re: General Ledger db design

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

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.

Not sure what you mean, but you can in fact have any number of open
batches, on the assumption that it is a multi-user system.

> 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.

Using the batch # as the foreign key allows all batches to be isolated
from each other.

>
> 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.)

Well to be honest I don't manually code any of it, I have a generator
that does it, I don't trust myself to code something like that properly :)

The code generator lets me do necessary things like sum the transactions
to the batch row, preventing a close unless they balance, preventing an
update to the batch row when it is already closed, which as a bonus
prevents new rows being added, and "Distributing" (as we call it) the
close flag to the transaction rows when the batch closes.

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

--
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?