Organization Hierarchy

Primary Business Case

The Organization Hierarchy allows a hierarchy of relationships to be established between multiple Organizations.

Viewing the Hierarchy

To view the Organization Hierarchy, go to Admin → Members → Organization Hierarchy

Managing the Hierarchy

To establish relationships, edit a Member in the Admin area with a user that has the My Account → EditParentMember permission.

If the feature toggle is enabled (see the Requirements sidebar) and you have the necessary permission, you should be able to assign the user a "Parent". This is the member, within the hierarchy, below which this Member should appear.

Technical Details

The MEMBER table contains a column called CACHED_PARENT_CHAIN that contains a denormalized representation of each Member's position in the hierarchy. 

Given a hierarchy like this: 
Parent Org (ID 3121) → Middle Org (ID 3122) → Leaf Org (ID 3123)

The value stored in the CACHED_PARENT_CHAIN table for "Leaf Org" would be:

3121§3122§º§3121§Parent Org§¦§3122§Middle Org§

This is a string with two parts, separated by a º character.

• The first part contains a delimited list of ancestor IDs. In this example, the current record ("Leaf Org") is a direct child of ID #3122 ("Middle Org"), which itself is a direct child of ID #3121 ("Parent Org"). This list is delimited by, and starts and ends with, a § character.
  
  
• The second part contains an embedded list, delimited by a ¦ character. Each element in this list is a delimited tuple of the ancestor's Member ID and the ancestor's name, with § characters between those values and at the start and end of the element.

The denormalized names in this field are updated whenever the Member Hiearchy data is changed.

Design Goals

This schema was designed to allow the following:

• Supports searching for all Members that are descendants of a specific Member Id. By adding leading and trailing delimiters, we can search for "1" without also finding "10" (e.g. by searching for the string "§1§");
  
  
• The ancestor names are stored in a denormalized way so that Reports can easily display a Member's name in context, such as "Parent Org → Middle Org → Leaf Org" simply by parsing the string, without needing to make recursive database lookups;
  
  
• The ancestor names are prefixed with their ID so that the cached data can be easily updated when a member name changes