Need help with hierachle design for MS-Access database.

[Ralmaken]

Hello everyone,



I have a situation in a database design which is requiring the ability to track different projects along different program levels. For example project x may be assigned to program x1 in department e3. on level f5. So his tree in a traditional excel spreadsheet would be x1.e3.f5. Now the tree will go to approximately 5 levels and report summaries would like to be able to filter data for each level from level 1 to level 5 as you can in excel. So I am wondering in being limited to Access 2007 as a database how should I go about designing this physical model?

Rules basically would be that the fields would required cascade updating and at minimum must have the top 1st program level.

[alansidman]

Here is a link to schema for staff assignments that you may be able to modify for your needs.

http://www.databaseanswers.org/data_...ents/index.htm

[Ralmaken]

This seems to take care of 1 level of program but not the next levels

[Ralmaken]

bumping this

[Ralmaken]

bumping post

[June7]

I doubt it will be easy. Don't think I've ever seen one.

Search forum on hiearchy or hiearchical.

Here's one:
https://www.accessforums.net/access/...ing-38942.html

[Dal Jeanis]

Sorry, I can't make out most of your shorthand for the requirements.

project, program, department, level: precisely what do those words mean in the real world?

Is Program always level one, Department level two, Level level three? If so, what are the words for the next two levels?


I can envision a workable schema based on something like this -

tblNodes
  NodeKey      PK, Autonumber
  NodeLevel    Number (1 to 5)
  NodeParent   FK to tblNodes, can be Null    Pointer to immediate parent
  NodeName     Text

tblProjects
  ProjID       PK Autonumber
  ProjNodeKey  FK to tblNodes  
  ProjData     The rest of the project info

Those are all that's needed for organizing the organization into the hierarchy
and assigning the projects to any given spot in the organization.

For reporting, the system would generate a flat version of the hierarchy
immediately before running the reports. Reassignment of projects would not
require update of the flat version - only moving of nodes. A system level
flag could be set whenever a node was moved, to indicate that recalculation
of the flat version was required.

tblNodesFlat
  NodeKey      Number  (FK to tblNodes)
  NodeLevel    Number (1 to 5)
  NodeName     Text  Name of This Node 
  NodePath     Text  Full path of this node
  NodePathL1   Text  Full path of Node at L1 in hierarchy
  NodePathL2   Text  Full path of Node at L2 in hierarchy, can be null  
  NodePathL3   Text  Full path of Node at L3 in hierarchy, can be null  
  NodePathL4   Text  Full path of Node at L4 in hierarchy, can be null  
  NodePathL5   Text  Full path of Node at L5 in hierarchy, can be null  
  NodeParent   FK to tblNodes, can be Null    Pointer to immediate parent
  NodeParentL1 FK to tblNodes, can be Null    Pointer to L1 in hierarchy, if below L1
  NodeParentL2 FK to tblNodes, can be Null    Pointer to L2 in hierarchy, if below L2
  NodeParentL3 FK to tblNodes, can be Null    Pointer to L3 in hierarchy, if below L3
  NodeParentL4 FK to tblNodes, can be Null    Pointer to L4 in hierarchy, if below L4

Given a structure like that, you could bind tblProjects to tblNodesFlat and then
pick out any arbitrary nodepath level to filter or report at.

Not all of those fields would be needed for any given query, but I can see
times that any of them could be useful, and they don't take up much space.