I’ve found the closure table very useful in cases where there is a high degree of interaction with hierarchy data. Recently I chose a closure table for shopping cart categories where categories could exist in multiple places in the hierarchy and furthermore an administrator could drag and drop categories anywhere in the hierarchy to rearrange the structure of the hierarchy. My original post on closure tables doesn’t support items existing in multiple places in the tree and while it supports rearranging items in the tree its always a painful rearrangement of relationships – we can do a little better than that.
We’ll deal with family members again as per my previous posts. This time, let’s say we want to rearrange our family members in some other order. Meet the Doe’s again.
Typically, at this point, we’d go straight to the closure table and begin arranging the family members as a hierarchy by using the FamilyMemberIDs in the closure. If we back up and make some observations we realize why comprising our closure table with the keys of our entity boxes us in. First, note that our closure table is a weak entity. What would make our life a little easier is if the tree (closure table) were a little stronger to stand on its own. If we construct our tree with “nodes” rather than family members, where each node “contains” a family member, we’ll get the strong data structure we’re hoping for. By introducing an associative table between our closure table and our entity (FamilyMembers) we’re done.
The FamilyMemberIDs come from our FamilyMember entity and the NodeID is an Identity which we’ll take to our closure table. Now, let’s build up closure table using the NodeIDs. Nothing changes as far as how we build up our closure table, except that we’re using NodeIDs for ParentID and ChildID, not the surrogate key of our entity.
What have we gained from this subtle change? Well, we’ve shifted the weak entity from the closure table to the associative table which allows for the structure of our tree to change independent of our FamilyMembers. Now, we can easily support both uses cases. Family members can exist in multiple locations in the tree since our NodeIDs are unique as ParentID, ChildID combinations in the closure table. Second, we get a little wiggle room on the complexity of restructuring the tree.
For example, suppose we want to move Sammy Doe above Mary Doe. There are now two options, we rearrange the structure of the closure table just to swap the two nodes or we simply swap the FamilyMemberIDs in the associative table to effectively change out the payload of the two nodes without moving the nodes themselves. That is, FamilyMemberID 6 (Sammy Doe) would be assigned NodeID 103 and FamilyMemberID 3 (Mary Doe) would be assigned NodeID 106. Naturally, if we move the node in the tree we still need to rearrange the structure of the closure table; there is no escaping that.
By applying a little data modeling to our hierarchal data structure we’ve gained some freedom for our consuming application(s) to do some cool(er) stuff. We can now move the content of each node independently of the node itself. Furthermore, we can support a more complex tree structure by allowing for our entities to exist in multiple locations in the tree.