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

[Frank Millman]

"Alex Glaros"  wrote in message 
news:b3c1e2da-9f72-420a-8b68-288dddf9fc67 at 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