In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies.
Example 1: Hierarchy1, that categories patient's based on geography.
Country >> state >> etc… .
- A Patient can only be identified in one node of the hierarchy.
- When a patient moves, their association with node is also moved to appropriate new node.
Example 1: Hierarchy2, based on benefits they claim, they are put into one program or another.
Program >> CCMA >> etc
Again, same rules as before apply:
- A Patient can only be identified in one node of the hierarchy.
- When a patient changes plans (program), their association with node is also moved to appropriate new node.
We could have 1 or 2 or … n hierarchies. How do we handle them?
This is different from Patient_Groups that are used for ad hoc grouping.
Thanks,
Best Answer
Jeff Moden has written two excellent articles on SQL Hierarchies here (Hierarchies on Steroids #1) and here (Hierarchies on Steroids #2) which present efficient SQL algorithms for converting hierarchies stored as an Adjacency List (ie children have a Parent pointer - easier to visualize and more efficient to create) to a temporary table organized as nested sets (more efficient for reporting).
Given Jeff's good work in describing how to efficiently convert to Nested Sets as needed, I would recommend storing and maintaining your hierarchies as a Adjacency Lists.
By making each Hierarchy an independent table you will gain the benefit of decoupling the hierarchies from the base patient data, facilitating the addition of additional hierarchies as required.
The Code (Thank you Jeff):