SQLServer 的哪些功能有助於實現隨時間跟踪層次結構的目標？

我需要創建一個系統，該系統在一段時間內跟踪本質上是容器層次結構，對供應鏈進行建模。鏈中的一切都是一個容器，因此在倉庫中創建手機的過程會導致容器出現在現有容器（倉庫）中，並被放入板條箱（在倉庫）在一輛卡車上（目前停在倉庫裡）。當卡車離開時，它會轉變為“在世界上”（這是一個集裝箱），它可能會駛上一艘船（另一個集裝箱），或者一些板條箱可能會從船上取出並裝載到另一個集裝箱中（一個倉庫，另一輛卡車，一家商店……）

最終的目標是能夠在任何特定的時間點或時間範圍內說出特定容器所在的位置 - 在哪些容器中，以及它包含/與它一起包含的其他容器。在任何時候，跟踪感測器也可能附加到容器上，並提供有關容器環境或位置的資訊

因此，似乎需要能夠從產品或感測器開始，並詢問“與該產品/感測器相關聯的容器的父子樹的完整樹是什麼，附近還有哪些其他產品/感測器？” - 這似乎是一種遞歸操作，可能相當繁重

我正在閱讀有關hierarchyIds 和臨時表的資訊，並想知道隨著時間的推移跟踪不斷變化的hierarchyIds 是否符合目標。目前，我似乎可以有一個表來跟踪具有 HierarchyId 的容器，該表描述容器目前在容器的大層次結構中的位置，並將其設為臨時表，以便任何時候我更改層次結構（反映轉移）新的層次結構和舊的層次結構被記住。要將包含 100 盒 10 部手機的箱子 X 從卡車 Y 轉移到商店 Z，我應該更新目前具有根層次結構的所有容器/world/truckX/crateXinto/world/storeZ/crateX這將更新 1001 個容器。在稍後的某個時間點，我可以詢問臨時表“T 時刻 crateX 在哪裡？” 或“在時間 T1 和 T2 之間，您看到箱子 X 的所有地方在哪裡？” 並且更新了層次結構中下游的所有內容也意味著我正在消耗儲存空間以節省計算樹的時間。

我的理由是我可以通過只保留“containerid”和“insidecontainerid”（即樹的一個片段）來節省儲存空間，然後必須將樹與“在時間 T 裡面的 crate X 是什麼？什麼是 crate X 裡面的東西”拼湊起來?現在..裡面的每一個是什麼，每個里面是什麼？裡面有什麼……一遍又一遍” - 遞歸CTE是否可以輕鬆解決這個問題，在樹上走來走去以提供完整的表示，與樹結構在一段時間內發生變化的次數一樣多？

每次發生更改時（更新所有子節點以具有新的絕對路徑），以後查詢（有效地）儲存整個下游樹更改是否容易得多，或者僅通過保持增量來重新計算新樹是否更容易? 有哪些 SQL Server 工具可以幫助解決這個問題？

這讓我想起了我在製造業工作的經歷。產品由不同層次的其他項目組成，具有層次結構。有時產品 ABC 可以按原樣銷售，或者可以用於建構更高級別的最終產品 XYZ。

研究一般製造/ MRP 數據庫模式可能對您有所幫助。具體來說，我會研究兩種類型的表，第一種是一個Items表，它是可用於建構產品的所有項目的唯一列表，包括最終產品本身。第二個稱為BOM表（材料清單），它為兩個特定的父子關係儲存一行Items。

在您的情況下，諸如crate,truck等之類的內容將在您的Items表格中。crateX但是or的特定實例將通過它們的標識符（？，？，或者您在內部標識 an 的實例）與它們的關係truckX存在於您的表中。例如，該表可以具有以下列並儲存以下將每個父級與子級相關聯的行。BOM``SerialNumber``TagId``Item``BOM``(ParentTagId, ChildTagId, Level)``(worldY, storeZ, 0), (storeZ, crateX, 1)

當您以這種方式建構數據時，您可以使用遞歸 CTE 來獲取所有相關的完整列表BOM Items，按照它們的順序Level使用如下查詢：

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;

遞歸 CTE 在正確結構化和索引的數據上實際上非常有效。我已經在 0 秒內遞歸查詢了一個BOM儲存了數百萬行的表，對於 every 的所有潛在組合。Item

一旦為您的案例確定了架構，您就可以決定哪種功能最適合記錄對架構中數據的更改，特別BOM是如果您選擇這種實現，則特別是您的表。下面是一些用於在 SQL Server 中跟踪數據更改的功能：

1. 時態表- “系統版本的時態表是一種使用者表，旨在保留數據更改的完整歷史記錄，以便輕鬆進行時間點分析。這種類型的時態表被稱為系統版本的時態表，因為每行的有效期由系統（即數據庫引擎）管理。 "
2. 變更跟踪——“變更跟踪是一種輕量級的解決方案，它為應用程序提供了高效的變更跟踪機制。 ”
3. CHANGE TABLE -"返回表的更改跟踪資訊。您可以使用此語句返回表的所有更改或特定行的更改跟踪資訊。 "

引用自：https://dba.stackexchange.com/questions/306699