Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #95352 > unrolled thread

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

Back to article view | Back to comp.lang.python

Contents

  How to model government organization hierarchies so that the list can expand and compress Alex Glaros <alexglaros@gmail.com> - 2015-08-13 12:10 -0700
    Re: How to model government organization hierarchies so that the list can expand and compress "Stéphane Wirtel" <stephane@wirtel.be> - 2015-08-13 21:17 +0200
    Re: How to model government organization hierarchies so that the list can expand and compress Ian Kelly <ian.g.kelly@gmail.com> - 2015-08-13 13:26 -0600
    Re: How to model government organization hierarchies so that the list can expand and compress Laura Creighton <lac@openend.se> - 2015-08-13 21:43 +0200
    Re: How to model government organization hierarchies so that the list can expand and compress "Frank Millman" <frank@chagford.com> - 2015-08-14 08:36 +0200
      Re: How to model government organization hierarchies so that the list can expand and compress Alex Glaros <alexglaros@gmail.com> - 2015-08-14 16:42 -0700
        Re: How to model government organization hierarchies so that the list can expand and compress Rustom Mody <rustompmody@gmail.com> - 2015-08-14 18:21 -0700
        Re: How to model government organization hierarchies so that the list can expand and compress Laura Creighton <lac@openend.se> - 2015-08-15 06:54 +0200
        Re: How to model government organization hierarchies so that the list can expand and compress "Frank Millman" <frank@chagford.com> - 2015-08-15 07:59 +0200
    Re: How to model government organization hierarchies so that the list can expand and compress Friedrich Rentsch <anthra.norell@bluewin.ch> - 2015-08-14 23:25 +0200
    Re: How to model government organization hierarchies so that the list can expand and compress Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-08-14 19:38 -0400
    Re: How to model government organization hierarchies so that the list can expand and compress Denis McMahon <denismfmcmahon@gmail.com> - 2015-08-15 22:51 +0000
      Re: How to model government organization hierarchies so that the list can expand and compress Alex Glaros <alexglaros@gmail.com> - 2015-08-17 13:37 -0700

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

It's like the desktop folder/directory model where you can create unlimited folders and put folders within other folders. Instead of folders, I want to use government organizations. 

Example: Let user create agency names: Air Force, Marines, Navy, Army. Then let them create an umbrella collection called "Pentagon", and let users drag Air Force, Marines, Navy, etc. into the umbrella collection. 

User may wish to add smaller sub-sets of Army, such as "Army Jeep Repair Services" 

User may also want to add a new collection "Office of the President" and put OMB and Pentagon under that as equals. 

What would the data model look like for this?  If I have a field: next_higher_level_parent that lets children records keep track of parent record, it's hard for me to imagine anything but an inefficient bubble sort to produce a hierarchical organizational list. Am using Postgres, not graph database.

I'm hoping someone else has worked on this problem, probably not with government agency names, but perhaps the same principle with other objects. 

Thanks! 

Alex Glaros

[toc] | [next] | [standalone]

#95354

Create a model with a parent_id on the current model

and you can use the mptt concept or some others for the reading.


On 13 Aug 2015, at 21:10, Alex Glaros wrote:

> It's like the desktop folder/directory model where you can create 
> unlimited folders and put folders within other folders. Instead of 
> folders, I want to use government organizations.
>
> Example: Let user create agency names: Air Force, Marines, Navy, Army. 
> Then let them create an umbrella collection called "Pentagon", and let 
> users drag Air Force, Marines, Navy, etc. into the umbrella 
> collection.
>
> User may wish to add smaller sub-sets of Army, such as "Army Jeep 
> Repair Services"
>
> User may also want to add a new collection "Office of the President" 
> and put OMB and Pentagon under that as equals.
>
> What would the data model look like for this?  If I have a field: 
> next_higher_level_parent that lets children records keep track of 
> parent record, it's hard for me to imagine anything but an inefficient 
> bubble sort to produce a hierarchical organizational list. Am using 
> Postgres, not graph database.
>
> I'm hoping someone else has worked on this problem, probably not with 
> government agency names, but perhaps the same principle with other 
> objects.
>
> Thanks!
>
> Alex Glaros
> -- 
> https://mail.python.org/mailman/listinfo/python-list


--
Stéphane Wirtel - http://wirtel.be - @matrixise

[toc] | [prev] | [next] | [standalone]

#95355

On Thu, Aug 13, 2015 at 1:10 PM, Alex Glaros <alexglaros@gmail.com> wrote:
> It's like the desktop folder/directory model where you can create unlimited folders and put folders within other folders. Instead of folders, I want to use government organizations.
>
> Example: Let user create agency names: Air Force, Marines, Navy, Army. Then let them create an umbrella collection called "Pentagon", and let users drag Air Force, Marines, Navy, etc. into the umbrella collection.
>
> User may wish to add smaller sub-sets of Army, such as "Army Jeep Repair Services"
>
> User may also want to add a new collection "Office of the President" and put OMB and Pentagon under that as equals.
>
> What would the data model look like for this?  If I have a field: next_higher_level_parent that lets children records keep track of parent record, it's hard for me to imagine anything but an inefficient bubble sort to produce a hierarchical organizational list. Am using Postgres, not graph database.
>
> I'm hoping someone else has worked on this problem, probably not with government agency names, but perhaps the same principle with other objects.

https://en.wikipedia.org/wiki/Tree_(data_structure)

[toc] | [prev] | [next] | [standalone]

#95356

Figure out, right now, what you want to do when you find a government
agency that has 2 masters and not one, so the strict heirarchy won't
work.

Laura

[toc] | [prev] | [next] | [standalone]

#95371

"Alex Glaros"  wrote in message 
news:b3c1e2da-9f72-420a-8b68-288dddf9fc67@googlegroups.com...

> It's like the desktop folder/directory model where you can create 
> unlimited folders and put folders within other folders. Instead of 
> folders, I want to use government organizations.

> Example: Let user create agency names: Air Force, Marines, Navy, Army. 
> Then let them create an umbrella collection called "Pentagon", and let 
> users drag Air Force, Marines, Navy, etc. into the umbrella collection.

> User may wish to add smaller sub-sets of Army, such as "Army Jeep Repair 
> Services"

> User may also want to add a new collection "Office of the President" and 
> put OMB and Pentagon under that as equals.

> What would the data model look like for this?  If I have a field: 
> next_higher_level_parent that lets children records keep track of parent 
> record, it's hard for me to imagine anything but an inefficient bubble 
> sort to produce a hierarchical organizational list. Am using Postgres, not 
> graph database.

Before you read my response, make sure that you read Laura's. Are you 100% 
sure that a hierarchical model is the correct solution for your requirement?

It is not clear whether you want the data model to be expressed in Python or 
in the database. The following thoughts assume the latter.

There are two classic approaches to using a relational database for this - 
'adjacency lists' and 'nested sets'. Google for both and you will find many 
interesting articles.

Nested sets were invented to overcome a problem with building complex 
queries on adjacency lists, but this problem has been overcome in recent 
years by the introduction of recursive queries, which most modern databases 
support. For PostgreSQL, read this page of the docs - 
http://www.postgresql.org/docs/9.1/static/queries-with.html . Because 
adjacency lists are easier to manipulate, and your comments above imply that 
this is a major requirement, I think that this is the way to go.

As an aside, I mentioned in a recent post that I had upgraded the version of 
sqlite3 shipped with python3.4 to the most recent version. The reason was 
that the older version did not support recursive queries properly, but the 
latest version does.

Regarding allowing your users to manipulate the structure, I think this is a 
function of the gui that you choose. Most gui toolkits will have a 'tree' 
widget, and hopefully make it easy to implement 'drag-and-drop' between the 
nodes. Then your task becomes one of getting the data out of the database 
and into the gui, and back again. Once you get used to the concepts, it is 
not that difficult.

HTH

Frank Millman

[toc] | [prev] | [next] | [standalone]

#95383

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.

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?

4. Solution will expressed in the DB, not Python.

Much appreciated!

Alex

[toc] | [prev] | [next] | [standalone]

#95384

On Saturday, August 15, 2015 at 5:12:13 AM UTC+5:30, Alex Glaros wrote:
> 3. Could not find Laura's response. Was it deleted?

I dont see it either. I expect its in some other thread
Laura's mail client is doing funny things to threading... 
Something Ive been noticing for a few days

[toc] | [prev] | [next] | [standalone]

#95386

In a message of Fri, 14 Aug 2015 16:42:00 -0700, Alex Glaros writes:
>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.
>
>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?
>
>4. Solution will expressed in the DB, not Python.
>
>Much appreciated!
>
>Alex

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.

Laura

[toc] | [prev] | [next] | [standalone]

#95387

"Alex Glaros"  wrote in message 
news:ae4e203d-c664-4388-af0b-41c41d5ec724@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

[toc] | [prev] | [next] | [standalone]

#95381

On 08/13/2015 09:10 PM, Alex Glaros wrote:
> It's like the desktop folder/directory model where you can create unlimited folders and put folders within other folders. Instead of folders, I want to use government organizations.
>
> Example: Let user create agency names: Air Force, Marines, Navy, Army. Then let them create an umbrella collection called "Pentagon", and let users drag Air Force, Marines, Navy, etc. into the umbrella collection.
>
> User may wish to add smaller sub-sets of Army, such as "Army Jeep Repair Services"
>
> User may also want to add a new collection "Office of the President" and put OMB and Pentagon under that as equals.
>
> What would the data model look like for this?  If I have a field: next_higher_level_parent that lets children records keep track of parent record, it's hard for me to imagine anything but an inefficient bubble sort to produce a hierarchical organizational list. Am using Postgres, not graph database.
>
> I'm hoping someone else has worked on this problem, probably not with government agency names, but perhaps the same principle with other objects.
>
> Thanks!
>
> Alex Glaros

After struggling for years with a tree-like estate management system 
(onwer at the top, next level: real estate, banks, art collection, etc., 
third level: real estate units, bank accounts, etc. fourth level: 
investment positions, currency accounts, etc)--it recently occurred to 
me that I had such a system all along: the file system. The last folder 
at the bottom end of each branch names its contents (AAPL or USD or 
Lamborghini, etc) the contents is a csv file recording an in and out, 
revenue, expense history (date, quantity, paid or received, memo, . . 
.). Any documentation on the respective value item may also be stored in 
the same folder, easy to find without requiring cross referencing.

Managing the data is not a awkward as one might fear. A bash wizard 
could probably do it quite efficiently with bash scripts. Bash dummies, 
like me, are more comfortable with python. Moving, say, a portfolio from 
one bank to another is a matter of "mv i/banks/abc/account-123 
i/banks/xyz" (system call). With the tabular data base system (MySQL) I 
have, simple operations like this one are quite awkward.

Well, you might laugh. Or others might. If your task is a commercial 
order, then this approach will hardly do. Anyway, I thought I'd toss it 
up. If it won't help it won't hurt.

Frederic

[toc] | [prev] | [next] | [standalone]

#95382

On Thu, 13 Aug 2015 12:10:12 -0700 (PDT), Alex Glaros
<alexglaros@gmail.com> declaimed the following:

>It's like the desktop folder/directory model where you can create unlimited folders and put folders within other folders. Instead of folders, I want to use government organizations. 
>
>Example: Let user create agency names: Air Force, Marines, Navy, Army. Then let them create an umbrella collection called "Pentagon", and let users drag Air Force, Marines, Navy, etc. into the umbrella collection. 
>
>User may wish to add smaller sub-sets of Army, such as "Army Jeep Repair Services" 
>
>User may also want to add a new collection "Office of the President" and put OMB and Pentagon under that as equals. 
>
>What would the data model look like for this?  If I have a field: next_higher_level_parent that lets children records keep track of parent record, it's hard for me to imagine anything but an inefficient bubble sort to produce a hierarchical organizational list. Am using Postgres, not graph database.
>
>I'm hoping someone else has worked on this problem, probably not with government agency names, but perhaps the same principle with other objects. 
>

	Practically any Genealogy program (though the best two, IMNSHO, have
both been discontinued -- Ultimate Family Tree and The Master Genealogist;
both were "event based" rather than the common "family based" types
[families in UFT/TMG result from the event of a birth event, whereas others
present a form that is based upon two parents automatically]).

	I keep joking that I'm going to put the life of Standard Oil into TMG
(the split up would be lots of children "born" at the same time the parent
company "died"; mergers would be "marriages" with/without a change of name
event and death of one of the marriage partners).

	If you are using a database, why do you envision a bubble sort in
use... select the top-level entities -- sorted by the database engine. Loop
over the result set, for each entity perform a select of the children of
that entity, again sorted by the DBMS. Perform this recursively until no
more grandchildren are found...


>Thanks! 
>
>Alex Glaros
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#95398

On Thu, 13 Aug 2015 12:10:12 -0700, Alex Glaros wrote:

> What would the data model look like for this?

Define an organizational unit. Give it a unique name. Give it a list of 
superiors and a list of subordinates.

government = {

'president' : { 'superiors' : [], 'subordinates' : ['jcs', 'cia', 'fbi', 
'nsa', 'treasury', 'nasa' ..... ] },

'jcs' : { 'superiors': ['president', 'congress', 'senate', 'treasury'], 
'subordinates' : ['army', 'navy', 'air force', 'jsoc'] }, 

'navy' : { 'superiors' : ['jcs', 'nsa', 'cia'], 'subordinates' : 
['seals', 'marines', 'pacific fleet' ....] }, ........

}

The multiple parenting means that you need to use something as 
references. You can't represent the hierarchy as a simple tree, because 
in a simple tree a node only has one parent.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#95445

Perhaps most jointly parented governmental organizations are functionally, collaborative "projects" (not organizations) which my model handles. But thanks to Laura, will not assume there are none, or will never be any in the future, so will use adjacent list (see fast response times documented in explainedextended.com article below), but created new 1:M table to handle multiple parents.

ORGANIZATION
-----------------------------
1. organization_Id (PK)
2. organization_name

ORGANIZATION_HIERARCHY (1:M)
------------------------------
1. organization_Id (FK to above table) - can have multiple parents
2. adjacent_parent_id (FK to above table) 

Thanks to all for the assistance.  Led me to some great articles including
http://explainextended.com/2009/09/24/adjacency-list-vs-nested-sets-postgresql/

Alex

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web