What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?
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/storeZ/crateXand 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?
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
Itemstable 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
BOMtable (Bill of Materials) which stores a row for every parent-child relationship between two specific
In your case, things like
truck, etc would be in your
Itemstable. But a specific instance of
truckXwould live in your
BOMtable with their relationships by their identifiers (
TagId?, or however you internally identify an instance of an
Item). E.g. the
BOMtable 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
Items, in order of their
Levelwith 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
WHERE ParentId = 'worldY'
ORDER BY Level;
Recursive CTEs are actually pretty performant on properly structured and indexed data. I've recursively queried a
BOMtable 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
BOMtable if you choose that kind of implementation. Below are some features for tracking data changes in SQL Server:
- https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15 - "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)."
- https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15 - "Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications."
- https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql?view=sql-server-ver15 - "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."