Search Postgresql Archives

Re: General Ledger db design

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux