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