在業務需求中，經常需要我們在系統中能夠記錄歷史資訊，能夠檢視到歷史變動情況，這時我們可以通過增加開始結束時間欄位來記錄資料的歷史版本。對資料的歷史記錄主要分為：關係、屬性歷史，實體歷史和變更歷史。
關係、屬性歷史記錄
所謂關係歷史記錄就是指兩個實體之間的關係存在歷史版本。比如部門表和員工表，對於某一個時刻來說，一個部門有多個員工，一個員工只屬於一個部門，所以是個一對多的關係。而我們希望把這個關係記錄下歷史變動，那麼就會形成多對多關係。多對多關係就形成中間表，然後我們在中間表上加入“開始時間”欄位和“結束時間”欄位即可記錄這個關係的歷史。
對某個實體的屬性記錄歷史記錄會形成一對多的關係表，比如產品價格屬性，我們希望把所有歷史定價都記錄下來，那麼就會形成產品和價格一對多的關係。
在AdventureWorks資料庫中，我們可以看到大量的這種記錄關係歷史的設計。比如：
員工、部門、輪班的歷史記錄：
這就是前面提到的一對多關係因為記錄歷史變為多對多關係的例子。
產品對成本和售價的歷史記錄：
這就是典型的屬性歷史記錄，對於產品的眾多屬性，我們之關係成本和售價這兩個屬性的歷史，所有可以建立一對多關係的價格歷史表。
銷售和區域以及銷售配額的歷史記錄：
區域和銷售本來也是普通的一對多關係，一個銷售屬於某個片區，一個區域對應多個銷售。現在由於歷史記錄，所以形成多對多的關係表SalesTerritoryHistory。而對於銷售配額，因為是記錄到季度的，一季度只有一個銷售配額，所以不需要開始時間和結束時間，只需要一個季度第一天即可（結束時間是可以根據這個季度的第一天而計算出來的，所以不需要再儲存）。
區域與銷售人員的關係在增加了中間表形成多對多後，仍然保留了原來的一對多關係，從資料上來看不是這樣的，因為兩個表的資料是不一致的，所以我推斷這是另外一個一對多關係，而不是原來的區域和銷售的分配對應關係表。
小結：
當需要對關係或屬性記錄歷史時，會把關係提升一個複雜度，也就是說原來是一對一的，現在會變成一對多，原來是一對多的，現在會變成多對多。在歷史記錄表中增加“開始時間”和“結束時間”兩個欄位來表示該行資料的時間有效性。AdventureWorks資料庫中使用了NULL值設為“結束時間”來表明這條資料是當前有效的，但是筆者並不推薦這麼做，最好是把兩個欄位都設定為NOT NULL，在比較時可以得到統一的查詢語句：
另外SalesTerritoryHistory這個表只記錄“開始時間”而不記錄“結束時間”這也是一個不好的設計，雖然結束時間是可以計算出來的，但是每次查詢的時候還需要去計算結束時間，真不是一個好方法。最好是把兩個欄位都保留，使用者只需要輸入開始時間，由前端程式去初始化結束時間，然後一併儲存。
實體歷史記錄
主實體歷史記錄
實體的歷史記錄是指對一個實體資料的任何更改，都把整條資料都產生一條新記錄，而不是隻針對某個屬性或者關係。對實體進行歷史記錄，我們也可以採用新增開始時間結束時間的方式，但是更多的時候我們對整個實體記錄歷史並不是為了隨時查詢歷史上某個時間點這個實體的值，而是為了記錄一個“版本Version”資訊，方便在審計某個實體的變更時對比。如果我們是出於審計的需要而記錄的歷史版本，那麼這些歷史資料平時是不會參與到業務查詢中的，所以並不需要記錄開始時間，結束時間，取而代之的，我們可以增加“版本”欄位，當然還有審計用到的“最後更新時間”和“最後更新人”，
這樣就實體的變化情況，如果我們僅僅是增加Version欄位，在查詢當前版本時會很麻煩，因為我們必須拿到最高的那個版本號，然後才能把這個最新版本的記錄作為當前記錄，為了優化這個效能問題，我們一般還需要再新增布林型的“是否當前版本IsCurrent”欄位來標識當前版本。增加了這個欄位後，那麼在更改實體資料時就會更麻煩一些。首先需要將老資料版本號獲得，+1生成新的版本號，然後將老資料的“是否當前版本”欄位置為0，更新老資料的“最後更新時間”和“最後更新人”，然後插入新版本號的資料，而且新版本是當前版本。我在AdventureWorks資料庫中並沒有看到關於實體的歷史記錄的設計，不過我們可以看SharePoint的資料庫設計，就是採用我這裡提到的版本設計的方法。有興趣的可以檢視一下SharePoint的ContentDB的AllUserData表，tp_Version就是記錄版本的，tp_IsCurrent和tp_IsCurrentVersion就是標記當前版本的。
附屬實體的歷史記錄
在進行實體歷史記錄時，還面臨的一個問題是，附屬的子實體是否也需要一併進行歷史記錄。比如我們要對採購訂單這麼一個實體進行歷史記錄，每次對採購訂單的修改都會生成一個新版本的採購訂單。如果一個採購訂單下面有100條採購明細，那麼我們在編輯了採購訂單主表後，建立了新版本的採購主表資料，是否對這100條明細也建立對應的新版本資料呢？如果建立，那麼採購明細表的資料量就會飛漲，而且實際上我們這裡並沒有編輯這100條明細，新版本的明細資料是一模一樣的，如果不建立，那麼怎麼保持這種外來鍵約束呢？畢竟明細表上面的外來鍵對應的可是老版本的採購訂單的ID啊！
其實兩種方案都可以，第一種方案開發簡單，如果明細並不是那麼多，或者本身單據的資料量並不大，那麼重複一點明細表並不會帶來太大的影響。第二種方案開發會很複雜，需要新老資料逐條對比，找到差異，如果主表有更改，那麼為主表建立新版本，如果100條明細中有2條更改，那麼就為這2條建立新版本。
下面詳細說一下采用第二種的解決方案的模型設計。首先，我們需要斷開主表和附屬表的外來鍵，將Form和Item作為兩個獨立的實體，各自新增“版本”，“是否當前版本”等屬性。為Form新增業務主鍵“FormNumber”，用於唯一標識一個表單（由於版本記錄的原因，所以FormNumber不是Form的主鍵），然後在Item表中新增“FormNumber”，用於標識這些Item是屬於哪個表單。
from Form
where IsCurrent=1 and IsDeleted=0 and FormNumber=@formNumber;
select *
from Item
where IsCurrent=1 and IsDeleted=0 and FormNumber=@formNumber;
變更歷史記錄
無論前面講到的對關係，屬性還是整個實體的歷史記錄，都會在業務表中形成新的資料，資料的增加一方面會導致查詢的效率變低，另一方面也使得每次查詢時都需要帶上額外的查詢條件，非常不方便。於是我們想到了另一種儲存歷史記錄的方式，那就是我們像記錄日誌一樣，把變更了的部分記錄到日誌表中。
記錄變更日誌的好處是不影響現有資料庫模型的設計，也就是說所有實體和關係都不需要改，我們只需要增加一個變更日誌表即可。但是變更日誌一般是前端程式通過對比前後記錄，找到變更的屬性，然後寫入的，並不是資料庫做的事。壞處也顯而易見，那就是還原歷史資料不方便，不能像前面的模型那樣可以快速的查詢資料的歷史狀態。
所以變更日誌表這種處理方式只用於審計的需求，而不能用於業務上要對歷史資料的查詢需求。在AdventureWorks資料庫中有一個TransactionHistory表，用於記錄各個訂單事務的，雖然不是記錄訂單變更的，但是也有和變更歷史記錄類似的結構。
歷史資料查詢優化
前面提到由於保留歷史資料的原因，所以會將資料庫中對應表的資料量增加很多倍，資料量的增加必然導致查詢變慢，所以我們在記錄歷史資料後很有必要對錶進行查詢優化。優化可以採用以下解決方案：
歸檔表
如果我們的歷史資料在平時的業務中並不需要，只有在特殊場景才會用到歷史資料表，那麼我們可以將歷史資料表建立一模一樣結構的歸檔表，然後定時將業務系統中的歷史資料轉移到歸檔表中。當然，前端軟體系統也要做對應的修改，對於老的歷史資料需要查詢歸檔表，而新的資料是查詢當前表。在AdventureWorks只對TransactionHistory就建立了對應的歸檔表。
分割槽
建立分割槽比歸檔表的好處是在物理上，老資料和新資料可以儲存在不同的地方，新老資料可以各自建立各自的索引樹，而在邏輯上對程式來說仍然是訪問一個表，前端程式不需要做什麼修改。比如對於開始結束日期的歷史資料記錄方式，我們可以把結束日期為9999-12-31的資料（當前有效資料）分到一個區，剩下的分到另一個區。對於版本記錄的方式，我們可以將“是當前版本”分到一個區，把其他的資料分到另一個區。
分割槽後在更新資料時會導致老資料的區塊轉移，因為老資料本來是在Current區塊的，現在由於更改了實體，老資料需要轉移到Old區塊，然後將新資料插入到Current區塊，除了分割槽的移動還有對應的索引的變動，所以更新資料時會相對慢一些。
索引
如果對於Oracle資料庫，那麼我們可以對IsCurrentVersion欄位建立點陣圖索引，如果是SQL Server這種不支援點陣圖索引的資料庫，那麼我們也可以在建立B樹索引時把IsCurrentVersion放在第一列，因為這個列是必然放入過濾條件的。