How do I design a database so I can copy a Hierarchy?

Hi all,

I have built an application that has a hierarchy in it:

Grand Parent

  • Parent
    – Child
    — Grand Child
    ---- etc

Unlikely, but it is technically possible to go on and on and have many great great great (to infinity) grandchildren. So I need to design the app that way.

In the past, I saw an Ancestor Descendant table being used. One grandparent could have hundreds of descendants in the database.

The user needs to be able to copy a branch of the hierarchy over - say move the child and all its descendants to another parent.

Any ideas on how to do that? Should I create that ancestor descendent table? If so, how do you populate it when you add a new child to the hierarchy.


Hi there, @jeffrey.j.obrien… although it does not address your specific question, your post reminded me of this post, and I am wondering if my first response in this thread might be useful in your situation…

Anyway, I didn’t think it could hurt to post that link as food for thought, and I hope it helps.


Hi @mikeloc,

That is awesome. I did that and it works great. I have a robot wand, that is attached to a robot arm, in the parts room, which is in Singapore HQ.

I can now Schedule an API to run through the downstream list and update those items.

I have one follow on question. Say I want to move the robot arm to another location called parts room 2…?

How do I modify the Downstream for the robot arm and arm want to drop Singapore HQ and Singapore Parts Store, and add parts room 2?


Hey, @jeffrey.j.obrien… I’m stoked to hear that my response in the other thread was useful for you.

About your follow-up question, I’m getting stuck on the fact that there doesn’t appear to be anything that defines an asset as a location. So, from a data perspective, the arm wand (which is obviously not a location) is the same as Singapore Parts Store. Oh, and Singapore Parts Store is kind of a sub-location of Singapore HQ, which seems to complicate matters a bit more.

The above being said, there doesn’t seem to be an easy way to remove Singapore HQ and Singapore Parts Store from the lists unless they are explicitly specified in the user interface as the “locations” from which the assets are being removed or there is a field in the Assets data type that defines an asset as a location. Adding the new “location” (parts room 2) to the lists could be easy enough because I’m assuming it is specified as an input of some sort. But again, I’m not sure how to remove the existing locations given your current setup, assuming I am understanding that setup correctly (which could easily not be the case).

Any of that make sense?


Hey Mike,

I figured this out. For anyone interested, here’s how.

Here is an example hierarchy in my system.:

Take the bottom record for example - the HVAC pump is attached to the HVAC cooling system, which is part of the HVAC system in the board room, on the 12th floor of building 1.

Let’s say I want to move the HVAC system from the board room to the men’s toilet. I need to remove the Building 1, Floor 12, and Board Room records from the downstream components of the HVAC system and everything below it. Well that is just the downstream components on the board room (its parent) above it.

When I move it, I need to add the new parent’s downstream records.

Here’s how I did it:

  1. Created a database trigger to detect when the asset is moved (basically when the parent asset changes.)

  1. When the parent asset changes, schedule the API workflow to adjust the downstream element for all records below the move item

  1. Step 1 in the endpoint removes the move item’s parent asset’s downstream records

  1. Step 2 in the endpoint adds the move item’s NEW parent asset’s downstream records

This workflow covers another situation - what if the HVAC was removed and not re-located and the parent is left blank. The workflow removes the downstream records but doesn’t add any new.

When its added to a new parent in the future, the Database Trigger workflow kicks off again and only adds the new parent’s downstream of.

Hope this helps.


1 Like

That’s awesome, Jeff… thanks so much for sharing the details!