I'm working on a dual entry bookkeeping system, and want to track
transactions (not the db kind) occuring on accounts. I group these
transactions in 'entries', to add common attributes and so on, but
that's probably not relevant for this discussion.
Thing is, I'd like to structure my chart of accounts as a hierarchy,
e.g. Expenses / Cars / Volvo / Gas, and whenever i tank up the Volvo,
I'd also like the expenses total to change.
To make this happen, I'm thinking a view that duplicate transactions
for all of the accounts' ancestors might do the trick? Updating
multiple locations certainly isn't an option.
Which data structure would you suggest for my chart of accounts? I
assume that being able to efficiently select all ancestors for a node
is fairly important? Perhaps there are even better structures than a
tree for grouping accounts?
This is the kind of thing views are used for, right, and performance
shouldn't be too bad? I'm using postgres 8.1.something.
a final note; how should I go about being 100% sure the transactions
in an 'entry' sum up to zero? (yup, this belongs on the application
level, but nm that for now)
Any replies appreciated,
Isak

<isak.hansen@gmail.comwrote in message
news:1146662576.746994.50000@
I'm working on a dual entry bookkeeping system, and want to track
transactions (not the db kind) occuring on accounts. I group these
transactions in 'entries', to add common attributes and so on, but
that's probably not relevant for this discussion.

Thing is, I'd like to structure my chart of accounts as a hierarchy,
e.g. Expenses / Cars / Volvo / Gas, and whenever i tank up the Volvo,
I'd also like the expenses total to change.

To make this happen, I'm thinking a view that duplicate transactions
for all of the accounts' ancestors might do the trick? Updating
multiple locations certainly isn't an option.

Which data structure would you suggest for my chart of accounts? I
assume that being able to efficiently select all ancestors for a node
is fairly important? Perhaps there are even better structures than a
tree for grouping accounts?

This is the kind of thing views are used for, right, and performance
shouldn't be too bad? I'm using postgres 8.1.something.

a final note; how should I go about being 100% sure the transactions
in an 'entry' sum up to zero? (yup, this belongs on the application
level, but nm that for now)

Any replies appreciated,
Isak

I suggest you look into the "nested sets" model of expressing the hierarchy
of accounts. You may find it relevant to
what I'm going to call "rolling up" the balances in individual accounts to
balances of group accounts. Excuse me if my terminology is wrong, here.

Speaking of terminology, I would have used the term "transaction", where
you used the word "entry". And I would have used the term "transaction
item" where you used the term "transaction". I wonder which set of
terminology an accountant would use.

As an accountant, I use the term transaction for items posted to the
general ledger.

As an accountant, I use the term transaction for items posted to the
general ledger.

As an accountant, I use the term transaction for items posted to the
general ledger.

"TKnTexas" <tkntexas55@aol.comwrote in message
news:1147224694.467832.94250@
As an accountant, I use the term transaction for items posted to the
general ledger.

Let me make sure I understand. If I use an ATM to transfer $100 from
savings to checking, that results in TW transactions: to debit my
savings account, and the other to credit my checking account.

Am I understanding you right?

Yep, you got it.

Thanks for the feedback, we are experimenting with nested sets at the
moment.

And while we're on the topic of terminology; what word would you use
for those two transactions in the example above as a single entity (or
any set of transactions that belong together).