如何設計將歷史數據視為一等公民的 SQL 模式？

我目前正在為由高度結構化的“樹”數據組成的應用程序設計 SQL Server 數據庫架構。因此，表格可能如下所示：

id
parentId (references id in same table)
text

這很簡單，但我還需要能夠更新節點的 parentId 或節點的文本（或刪除節點），並以與可以檢索目前修訂相同的速度訪問樹的每個修訂. 我目前的解決方案是有兩張桌子。首先是修訂表：

id
revisionDate

然後是修改後的節點表：

id
UID (same for all revisions of a specific node)
revisionId (references id of revision table)
deleted (flag for deleted node)
parentUID (references UID of parent node)
text

因此，要獲取特定修訂的樹，您需要獲取該修訂ID 和它之前的所有修訂ID，然後查詢具有這些修訂ID 的所有節點的節點表，然後只獲取共享一個UID 的節點的最高修訂ID 節點，然後刪除任何節點設置了已刪除標誌。

這行得通，但很快就會變得醜陋（我使用的是一個簡化的範例）我實際上有大約 10 個這樣的表需要修訂跟踪，每個表都有相互的交叉引用。這種方法也破壞了外鍵，因為 UID 不是唯一的。我不能簡單地為每個修訂複製完整的樹數據，因為每小時可能有數百個修訂。

對於這樣的問題，最佳做法是什麼？如果它明顯更好，我願意使用非關係數據庫。

嘗試做 FK 只會讓你感到沮喪——你實際上並不需要它。

如果您使用 InsertDateTime 概念處理版本控制，那麼您基本上是在描述許多數據倉庫使用的 Type2 維度。有很多關於調整位於數據倉庫頂部的系統的材料，但從 T-SQL 的角度來看，請考慮使用 APPLY。像這樣：

SELECT ...
FROM ... AS t
CROSS APPLY (
   SELECT TOP (1) ...
   FROM ... AS rev
   WHERE rev.UID = t.UID
   AND rev.InsertDateTime < @revdatetime
   ORDER BY rev.InsertDateTime DESC) AS something
WHERE something.IsDeleted = 'N'

這種構造受益於（IsDeleted、UID、InsertDateTime DESC）上的索引。

或者，如果您想在特定時間查看整個表，請使用 ROW_NUMBER() ，例如：

WITH numbered AS
(  SELECT *, ROW_NUMBER() OVER (PARTITION BY UID ORDER BY InsertDateTime DESC) AS rownum
   FROM ...
   WHERE InsertDateTime < @revDateTime
),
VersionAtTime AS
(   SELECT *
   FROM numbered
   WHERE rownum = 1
   AND IsDeleted = 'N'
)
SELECT ...
FROM VersionAtTime
....

（從原始編輯以將 IsDeleted 謂詞移到子查詢之外，刪除該特定時間的所有版本）

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