How to model government organization hierarchies so that the list can expand and compress
frank at chagford.com
Sat Aug 15 07:59:06 CEST 2015
"Alex Glaros" wrote in message
news:ae4e203d-c664-4388-af0b-41c41d5ec724 at googlegroups.com...
> Frank, thanks for describing the terminology of what I'm trying to do.
> 1. Do the recursive join Postgres examples you linked to, use a data
> structure where the child has the adjoining parent-ID? Examples look
I think that their example is very simple - it seems that each level
contains one link to the level below, and the level below has no link to the
level above (or at least they did not make use of it).
The following (untested) example assumes that each row can have multiple
children, and each row has a link to its parent called parent_id.
WITH RECURSIVE included_parts(part, quantity) AS (
SELECT part, quantity FROM parts WHERE part = 'our_product'
SELECT p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.parent_id = pr.part
SELECT part, SUM(quantity) as total_quantity
GROUP BY part
You will find many variations in various on-line tutorials. For example, you
can traverse *up* the tree by changing the WHERE clause to WHERE p.part =
> 2. Not 100% sure that hierarchical is the perfect solution but will go
> with that now. Of course some agencies will be at equal levels; will
> ignore for now.
> 3. Could not find Laura's response. Was it deleted?
Laura responded to this in another post, but in case you cannot see that one
either, I reproduce it here -
What I said was, that real agencies are almost never perfectly
heirarchical. That's an oversimplification. In the real world
Organisation A (say Army) and Organisation B (say Navy) decide they
have common interests and make a Joint Department. If you run that
department you report to superiors in _both_ organisation A and
organisation B, get orders from both places and so on and so forth.
You must decide now what you want to do when you run into such
departments, because that will greatly influence your design.
> 4. Solution will expressed in the DB, not Python.
> Much appreciated!
Glad I could help.
More information about the Python-list