Re: Account Hierachies

"Markus Janscha" <markus_at_janscha.co.uk> wrote in message news:89oqf8$gm9$1_at_gxsn.com...

> Sorry, perhaps I was not clear enough when I referred to parent child
> relationships.
> I am talking about account numbers not people.
> For example numbers 1 to 9 have a parent 10 (total of 1 to 9)
> Parents 10, 20, ..... are grouped under parent 100 (for example)
> I can draw a tree representing the hierarchy but what is the best table
> design?
> There are three parent levels
> Markus

Can you get away with just storing the account code? That way, you can get the rollup for 100 with:

select sum(amount) from whatever where account between 1 and 99

You might also consider Celko's nested sets model or the "parent path" in which all the parents are in an ordered list stored in a text field. I'm sure there are more models suitable for a tree, some of which will get you sued for patent infringement despite the supposed non-patentability of algorithms.

--
Joe Foster <mailto:jfoster_at_ricochet.net>  Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!