How to model government organization hierarchies so that the list can expand and compress

[Frank Millman]

"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 
> great.

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'
      UNION ALL
        SELECT p.part, p.quantity
        FROM included_parts pr, parts p
        WHERE p.parent_id = pr.part
      )
    SELECT part, SUM(quantity) as total_quantity
    FROM included_parts
    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 = 
pr.parent_id

>
> 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.

Frank