Converting the Closure Table from a Weak Entity

By p medved

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.

FamilyMemberID Name
1 John Doe
2 Billy Doe
3 Mary Doe
4 Bobby
5 Bobby Doe
6 Sammy Doe
7 Mae Doe

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.

FamilyMemberID NodeID
1 101
2 102
3 103
4 104
5 105
6 106
7 107

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.

ParentID ChildID PathLength
101 101 0
101 102 1
101 103 1
101 104 2
101 105 1
101 106 2
101 107 2
102 102 0
102 104 1
103 103 0
103 106 1
103 107 1
104 104 0
105 105 0
106 106 0
107 107 0

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.

About these ads

2 Responses to “Converting the Closure Table from a Weak Entity”

  1. I am working on a project that uses closures at the moment. I am trying to wrap my head around all of the SQL and how these tables related to each other. I have a question about this statement.

    “Family members can exist in multiple locations in the tree since our NodeIDs are unique as ParentID, ChildID combinations in the closure table.”.

    Do you just add the FamilyMemberID to the Node table twice? So in your example above if I want FamilyMemberID 5 (Bobby Doe) to exist in the tree in two places how would I go about that?

    • You got it! Adding the FamilyMemberID 5 (Bobby Doe) to the Node table twice will give you two distinct NodeID’s for us in the tree. Good luck on your project and let me know how the closure’s works out.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: