1

I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process of creating a cellphone in a warehouse causes a container to appear in an existing container (the warehouse) and be placed into a box (which is also in the warehouse) in a crate (in the warehouse) in a truck (currently parked in the warehouse). When the truck moves out it transitions to being "in the world" (which is a container), and it might drive onto a ship (another container), or some of the crates might be taken out of it and loaded into another container (a warehouse, another truck, a store..)

Ultimately the goal is to be able to say at any particular point in time, or range of time where a particular container was - in what containers, and what other containers did it contain/were in there along with it. At any time a tracking sensor might be attached to a container too, and provide info about the container environment or location

It thus seems need to be able to start with either a product or a sensor and ask "what is the full tree of parents and children of the container associated with this product/sensor, and what other products/sensors were near by?" - that seems like a recursive operation, possibly quite a heavy one

I'm reading about hierarchyIds and temporal tables and wondering if tracking changing hierarchyIds over time meets the goal. At the moment it seems I could have a table that tracks Containers with a HierarchyId that describes where the container currently is in the grand hierarchy of Containers, and make it a temporal table so that any time I change the hierarchy (reflecting a transfer) the new hierarchy and the old are memorized. To transfer a crate X containing 100 boxes of 10 cellphones out of truck Y and into store Z, I should update all containers that currently have a root hierarchy of /world/truckX/crateX into /world/storeZ/crateX and this will update 1001 containers.. At some later point in time I could ask the temporal table "where was crateX at time T?" or "between times T1 and T2 where were all the places you saw crate X?" and having updated everything downstream in the hierarchy updated too means I'm burning storage space to save on having to calculate a tree.

I reason that I could save storage space by keeping just "containerid" and "insidecontainerid" i.e. a fragment of the tree, and then have to piece together the tree with "what was crate X inside at time T? what was immediately inside crate X? now.. what were each of those inside and what was inside each of those? and what were inside... over and over" - does a recursive CTE easily solve that, walking up and down the tree to provide a complete representation, for as many times as the tree structure changed over the time period?

Is it considerably easier for later querying to (effectively) store an entire downstream tree change every time there is a change (update all child nodes to have a new absolute path), or is it easier to recalculate a new tree by just keeping the delta? What SQL Server facilities exist to help solve this problem?

1 Answer 1

1

This kind of reminds me of my experience working in the manufacturing industry. Products were composed of different levels of other items in a hierarchical nature. Sometimes product ABC could be sold as is, or it could be used to build an end product of a higher level, XYZ.

Researching general manufacturing / MRP database schemas might be of help to you. Specifically, I'd look into two types of tables, the first being an Items table which is a unique list of all items that could be used to build products, including the final product itself. The second being known as a BOM table (Bill of Materials) which stores a row for every parent-child relationship between two specific Items.

In your case, things like crate, truck, etc would be in your Items table. But a specific instance of crateX or truckX would live in your BOM table with their relationships by their identifiers (SerialNumber?, TagId?, or however you internally identify an instance of an Item). E.g. the BOM table could have the following columns (ParentTagId, ChildTagId, Level) and store the following rows that relate each parent to child (worldY, storeZ, 0), (storeZ, crateX, 1).

When you structure your data this way, you can then use a recursive CTE to get the full list of all related BOM Items, in order of their Level with a query like so:

WITH _RecursiveCTE
(
    SELECT ParentTagId, ChildTagId, Level
    FROM BOM

    UNION ALL

    SELECT B.ParentTagId, B.ChildTagId, B.Level
    FROM _RecursiveCTE AS RC
    INNER JOIN BOM AS B
        ON RC.ChildId = B.ParentId
)

SELECT ParentTagId, ChildTagId, Level
FROM _RecursiveCTE
WHERE ParentId = 'worldY'
ORDER BY Level;

Recursive CTEs are actually pretty performant on properly structured and indexed data. I've recursively queried a BOM table that stored millions of rows, for all potential combinations of every Item, in under 0 seconds.

Once you have the schema figured out for your use cases then you can decide on what feature is best for logging changes to the data in your schema, specifically your BOM table if you choose that kind of implementation. Below are some features for tracking data changes in SQL Server:

  1. Temporal Tables - "A system-versioned temporal table is a type of user table designed to keep a full history of data changes to allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine)."
  2. Change Tracking - "Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications."
  3. CHANGE TABLE - "Returns change tracking information for a table. You can use this statement to return all changes for a table or change tracking information for a specific row."
4
  • Thanks for the insight JD.. So the one part of everything that still breaks my brain somewhat is how to reconstruct the timeline of every change that happened. If we have a BOM table then that's essentially a fragment of a tree; cellphone box A is in case B. Another row will say case B is in crate C. crate C is in truck D. If I then go into the truck and open the crate and take that case out but leave the other case (say, Z) in the crate, and I put case B into shelf S (and shelf S is in warehouse W), then I have effectively got 2 trees: A>B>C>D at time T0 and, A>B>S>W at T1. Am I rebuilding..
    – XOR cyst
    Jan 31, 2022 at 10:27
  • ..that manually from the change table? It seems like temporal tables do that building part because they also know T0 and T1.. The problem also expands. For the perspective of cellphone A, I now no longer care where truck D is or goes, but I'm now interested in S and W. For any given time period Tn to Tn+1 there is some set of things that are in-scope. Shelf S has box A, and box A has a temperature sensor. Shelf S also has prior box H with a humidity sensor, so now humidity is in scope for A, but only until box H leaves S.. And wiring up these N levels of scope is what I want to make easier..
    – XOR cyst
    Jan 31, 2022 at 10:43
  • @XORcyst To your first question, talking pseudo, you could either generate your recursive CTE over the BOMTemporal table itself for each time period you're interested in. This may or may not be cumbersome depending on how you go about it. Or you could generate your recursive CTE once on the current BOM table, and then try joining in only the changes from the BOMTemporal table (by ParentTagId) for a given Parent's tree and then displaying the results in a meaningful way in your application. To your second question, it sounds like those details are all just meta-data that you can...
    – J.D.
    Jan 31, 2022 at 12:21
  • ...just join in as your last step after you get your hierarchy of BOMs. That meta-data would probably live in the Items table to describe attributes about certain Items, e.g. HasTemperatureSensor which is a bit field, and would be set to true for boxA. So you would just need to join to your Items table once at the end to get all of the additional meta-data. (Of course the meta-data could be normalized into other tables as well if there's a lot of different unrelated fields, but same concept, just join what's needed after generating your hierarchal BOMs).
    – J.D.
    Jan 31, 2022 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.