The hierarchy is an everyday concept, most often encountered when describing interrelationships within a business, where A supervises B, who oversees C, and so on and so forth. From CEO to presidents, VPs, directors, to any number of mid-to-low level managers, the hierarchy is very pervasive. But, if one is dealing with a relational DBMS, a hierarchy remains an awkward concept. Hierarchy was not a natural predicate calculus idea. And as such, not a base function instinctively supported by the standard database tool. Initially, there was not a base SQL operator dealing with hierarchies in an intuitively obvious fashion.
Certainly, some DBMS’ have extended their internal functionally to address hierarchies in some fashion, but this functionality is not ubiquitous nor consistent. Adding another layer of complexity, hierarchical needs are often a bit schizophrenic, in that several hierarchies may need to be laid across the same base business objects. One may have customers, or employees, that need to be grouped together and aggregated in one fashion for one department or functional perspective, and in a differing fashion for a different functional perspective. Matrix management has only increased such needs. Organizations often desire a one-size-fits-all solution, something slick, something magical. The wish is for one simple solution to provide for everything needed by anyone. Sadly, that magic still does not exist.
One fairly practical option might be to split one’s approach, based on the main functional needs. Those functional areas are gathering, storing, and maintaining the data, versus doing reporting and analytics using such information. The gathering, storing, and maintaining is more of an operational nature. These tasks could be under the umbrella of a custom application, or possibly an off-the-shelf MDM or reference data supporting tool. Since this is an application function, a more normalized approach is more appropriate. And with the various needs, such as multiple hierarchies containing the same leaf-node business objects, an appropriate solution’s data structures would be abstracted to a rather high level. For example, should the low-level business objects be customer, one might define tables like: CUSTOMER, CUSTOMER_TYPE, CUSTOMER_RELATIONSHIP, CUSTOMER_RELATIONSHIP_TYPE. And then build from there as a base. Each customer may be involved with many relationships, and each of those relationships may be from unique relationship types.
The abstract structures, used to store the various kinds of relationships and their details, can be very complex areas through which to navigate. Therefore, the usage part of the functionality could have a different solution. Effectively, a function would be defined, and this function would thread through the stored elements and unravel the tree comprising a single hierarchy. The output could then be put to it’s specific and intended use for analytics and reporting. This output could be designed to build a temporary table for use, or serve as input to a data warehousing or OLAP solution likely containing a dimension table flattening out a single hierarchy for use in reporting (or perhaps many extracts populating many dimensions, one each for various hierarchies).
There could exist a one-for-one function for each of the many hierarchies an organization needs to support. Alternatively, one might be very clever and create a single parameterized function to build any of the supported hierarchies, or better yet, embed it all in a glowing API. This framework can be considered a single one-size-fits-all solution, albeit one with a few moving parts. The division of sections by the base functions supported (gathering versus querying) is where the pieces come together in a whole that is much greater than those individual parts. As the wise ones say, make a solution as simple as possible, but no simpler. When some complexity is necessary, embrace that vital piece.