Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Jan Lehnardt <ja...@apache.org>]

On 3 Sep 2009, at 14:35, Metin Akat wrote:

> I am writing an accounting application.
> Generally, so far my design is as follows:
>
> 1. Account document.
>
> 2. Transaction document. It has kind of the usual structure...
> "debit", "credit", "qty", "value" etc.
> Here "debit" and "credit" are two account UUIDs
>
> I have a view with a reduce, that emits all the transactions for each
> account and calculates the account balance.
> Something quite similar to what is described here:
> http://books.couchdb.org/relax/receipts/banking
>
> The accounts are a tree structure. Every account can have child
> accounts. And transactions are only executed against
> accounts that have no children (leaf nodes).
> So far I have no problems in calculating the balance of the leaf  
> nodes.
>
> Every node in the tree has a property called "lineage" that is a list
> with its "path" in the tree.
>
> So what I'm trying to do now is to calculate the balance of the branch
> nodes across the tree.
>
> One way is to do it client side (fetch all the leafs (view collation)
> that belong to some branch and calculate the balance in my
> application).
> This won't be a great problem, because I don't really believe that
> I'll have more than several hundred leafs altogether.
> Though I'm still interested to find out if there is some "cool" way to
> do it with couchdb.

query your reduce view with group_level=1 (or 2 or 3 .. depending on  
how many level you want to descend into your tree). Your "path" to the  
root node should be an array as in: ["toplevel", "firstlevel",  
"secondlevel"] as the key of your view.

Cheers
Jan
--

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Metin Akat <ak...@gmail.com>]

>
> Why not record the account hierarchy with each transaction and think
> of that as a snapshot of the hierarchy when the transaction occurred?
> You get a little extra historical data that way.  You can emit a view
> row for every account in the lineage so that when you calculate
> account balances you only need to use the account id as the key.  So
> it does not matter if the hierarchy in the transaction does not match
> the current hierarchy


That's not good in my situation. When I move an account, that action
has to update the balances of all parent accounts.
I mean, that must update all the balances up to the root of the tree.
It can't be historic.

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Jesse Hallett <ha...@gmail.com>]

On Sat, Sep 5, 2009 at 8:42 AM, Metin Akat<ak...@gmail.com> wrote:
> On Sat, Sep 5, 2009 at 10:20 AM, Chris Anderson<jc...@apache.org> wrote:
>> On Fri, Sep 4, 2009 at 11:34 PM, Metin Akat<ak...@gmail.com> wrote:
>>> One way I can think of is to bulk update all transactions "belonging"
>>> to a leaf account when moving that leaf account around.
>>> I can live with having to wait some time till this gets executed.
>>> Is this "bad thinking" or is it OK to some extent?
>>>
>>
>> It can be problematic if you need ACID compliance as bulk updates
>> aren't transactional. (Especially consider what can happen during
>> replication.)
>>
>> This can often be remedied in your app model, but this is can get complex.
>>
>
> So this probably means that the least problematic way to go would be
> if I calculate aggregate balances client side.
> OK, for now I don't plan having millions of accounts, so I'll choose
> this way after all.
>

Why not record the account hierarchy with each transaction and think
of that as a snapshot of the hierarchy when the transaction occurred?
You get a little extra historical data that way.  You can emit a view
row for every account in the lineage so that when you calculate
account balances you only need to use the account id as the key.  So
it does not matter if the hierarchy in the transaction does not match
the current hierarchy.

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Metin Akat <ak...@gmail.com>]

On Sat, Sep 5, 2009 at 10:20 AM, Chris Anderson<jc...@apache.org> wrote:
> On Fri, Sep 4, 2009 at 11:34 PM, Metin Akat<ak...@gmail.com> wrote:
>> One way I can think of is to bulk update all transactions "belonging"
>> to a leaf account when moving that leaf account around.
>> I can live with having to wait some time till this gets executed.
>> Is this "bad thinking" or is it OK to some extent?
>>
>
> It can be problematic if you need ACID compliance as bulk updates
> aren't transactional. (Especially consider what can happen during
> replication.)
>
> This can often be remedied in your app model, but this is can get complex.
>

So this probably means that the least problematic way to go would be
if I calculate aggregate balances client side.
OK, for now I don't plan having millions of accounts, so I'll choose
this way after all.

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Chris Anderson <jc...@apache.org>]

On Fri, Sep 4, 2009 at 11:34 PM, Metin Akat<ak...@gmail.com> wrote:
> One way I can think of is to bulk update all transactions "belonging"
> to a leaf account when moving that leaf account around.
> I can live with having to wait some time till this gets executed.
> Is this "bad thinking" or is it OK to some extent?
>

It can be problematic if you need ACID compliance as bulk updates
aren't transactional. (Especially consider what can happen during
replication.)

This can often be remedied in your app model, but this is can get complex.

>
> On Thu, Sep 3, 2009 at 4:06 PM, Metin Akat<ak...@gmail.com> wrote:
>> Well, there is a problem with this. I don't like the idea of recording
>> the whole path in each transaction, because it is anticipated that
>> accounts are able to change places in the tree. So my TRANSACTION only
>> holds information for only the "actual" account it's issued against,
>> not for its whole lineage.
>>
>> On the other hand, ACCOUNTS do have the information for THEIR
>> path(lineage), exactly as you suggested.
>> So the problem now is making the connection between an account and its
>> balance :)
>>
>>
>>>
>>> query your reduce view with group_level=1 (or 2 or 3 .. depending on how
>>> many level you want to descend into your tree). Your "path" to the root node
>>> should be an array as in: ["toplevel", "firstlevel", "secondlevel"] as the
>>> key of your view.
>>>
>>> Cheers
>>> Jan
>>> --
>>>
>>>
>>>
>>
>



-- 
Chris Anderson
http://jchrisa.net
http://couch.io

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Metin Akat <ak...@gmail.com>]

One way I can think of is to bulk update all transactions "belonging"
to a leaf account when moving that leaf account around.
I can live with having to wait some time till this gets executed.
Is this "bad thinking" or is it OK to some extent?


On Thu, Sep 3, 2009 at 4:06 PM, Metin Akat<ak...@gmail.com> wrote:
> Well, there is a problem with this. I don't like the idea of recording
> the whole path in each transaction, because it is anticipated that
> accounts are able to change places in the tree. So my TRANSACTION only
> holds information for only the "actual" account it's issued against,
> not for its whole lineage.
>
> On the other hand, ACCOUNTS do have the information for THEIR
> path(lineage), exactly as you suggested.
> So the problem now is making the connection between an account and its
> balance :)
>
>
>>
>> query your reduce view with group_level=1 (or 2 or 3 .. depending on how
>> many level you want to descend into your tree). Your "path" to the root node
>> should be an array as in: ["toplevel", "firstlevel", "secondlevel"] as the
>> key of your view.
>>
>> Cheers
>> Jan
>> --
>>
>>
>>
>

Re: Tree structure: how to calculate branch node sum, using results from a view that calculates leaf node sums.

[Metin Akat <ak...@gmail.com>]

Well, there is a problem with this. I don't like the idea of recording
the whole path in each transaction, because it is anticipated that
accounts are able to change places in the tree. So my TRANSACTION only
holds information for only the "actual" account it's issued against,
not for its whole lineage.

On the other hand, ACCOUNTS do have the information for THEIR
path(lineage), exactly as you suggested.
So the problem now is making the connection between an account and its
balance :)


>
> query your reduce view with group_level=1 (or 2 or 3 .. depending on how
> many level you want to descend into your tree). Your "path" to the root node
> should be an array as in: ["toplevel", "firstlevel", "secondlevel"] as the
> key of your view.
>
> Cheers
> Jan
> --
>
>
>