As I said earlier, I am quite green with PGSQL, so please bear with me when I ask "Stupid" questions... --- On Mon, 13/10/08, justin <justin@xxxxxxxxxxxxxxx> wrote: > I just redid the accounting side of an application we have > access to > source code, so been here and done this. > > If i was not for the rest of the application i would have > completely > redone the accounting table layout something like this Ok with the tables > I would used views and the application to create the tree > list view i think your after. As you also need to know the Open > Balances, Debit, Credits and Closing Balances by accounting period.. > One idea is is > create a functions that scans through the > general_ledger_transactions > table to get your values So create a View something like > this > > Example would by > Select Sum(debits) + > Case when coa.doIhaveChildren then > GetChildAccountDebits(coa.coa_id, > period_id) > else > 0.0 > end; > from general_ledger_transactions, coa, > where general_ledger_transactions.coad_id = coa.coa_id > and coa.coa_id = SomPassedAccountID > group by general_ledger_transactions.period_id > I start getting lost : SomPassedAccountID ??? Where is this coming from? > What happen is the GetChildAccountDebits() function takes > two parameters. One is the coa_id and the other is accounting > period to search > > The function would look something like this > > return Select Sum(debits) + > Case when coa.doIhaveChildren then > GetChildAccountDebits(coa.coa_id, period_id) > else > 0.0 > end; > from general_ledger_transactions, coa, > where general_ledger_transactions.coa_id= coa_id > and coa.parent_id = ThePassedAccountID > and general_ledger_transactions.period_id = PassedPeriodID PassedPeriodID ??? ...and this? > This creates a loop back which can be dangers if > Parent_account is also a Child_account of itself which creates > an endless loop then creates a stack error. I think this is easy enough to control with a CHECK constraint I think. Otherwise, I see the sense in using two columns for transactions - If I were writing an application for a bank, then using one column only may have potential pitfalls. Regards -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
