具有有效間隔的可移植表的設計（歷史記錄、時態數據庫）
我正在為必須跟踪數據更改的應用程序設計數據模型。
第一步，我的應用程序必須支持PostgreSQL，但我想在第二步中添加對其他 RDBMS（尤其是 Oracle 和 MS SQL 伺服器）的支持。因此，我想選擇一個使用較少專有功能的可移植數據模型。（表的 DDL 可能因 RDBMS 供應商而異。但應用程序中的 SQL 查詢/語句對於所有支持的供應商應盡可能相同。）
例如，假設有一個
users和一個users_versions表。users_versions上有一個外鍵users。表的範例可能如下所示：
users ---------------- id | username ---------------- 1 | johndoe 2 | sally users_versions --> references id of user (userid) --------------------------------------------------------------------------- id | userid | name | street | place | validfrom | validuntil --------------------------------------------------------------------------- 1 | 1 | John Doe | 2nd Fake St | Faketown | 2018-01-04 | 2018-01-05 2 | 1 | John Doe | Real St 23 | Faketown | 2018-01-05 | null 3 | 2 | Sally Wu | Main St 1 | Lake Fake | 2018-04-02 | 2018-04-20 4 | 2 | Sally Wu | Other St 99 | Chicago | 2018-04-20 | null大多數 SQL 查詢將查詢目前有效的條目。在上面的概念範例中，這個 Woule 看起來像
SELECT * FROM users_versions uv INNER JOIN users u ON u.id = uv.userid WHERE uv.userid = 123 AND uv.validuntil IS NULL;一些案例（報告等）也需要選擇歷史版本的數據（例如，哪些數據在 ? 時有效
2017-12-31？）。但這些在我的應用程序中不會對性能至關重要。在上面的範例中，我可能會創建一個過濾的唯一索引，
validuntil以確保一次只有 1 個具有無限有效性的條目：CREATE UNIQUE INDEX foo ON users_versions ( userid ) WHERE validuntil IS NULL;據我所知，過濾索引只能用於 PostgreSQL 和 MS SQL 中的查詢優化，而不能用於 Oracle。此外，索引
null也可能是一件棘手的事情（可能/僅在多列索引中/不可能）。因此，另一種方法
users_versions可能是上述結構加上valid由應用程序管理的顯式列。最近的條目會得到一個1，所有歷史條目都會得到一個0。然後我可以創建兩個索引，一個用於查詢優化，一個用於完整性執行（一次只有 1 個有效條目）：CREATE INDEX optimization ON users_versions ( userid, valid );對於以下查詢：
SELECT * FROM users_versions uv INNER JOIN users u ON u.id = uv.userid WHERE uv.userid = 123 AND uv.valid = 1;還有一個索引來強制執行目前版本的完整性（例如 ORACLE 版本）：
-- ORACLE: Entry with null-only columns ignored in indexing: CREATE UNIQUE INDEX only_one_valid_version_per_user ON users_versions ( CASE WHEN valid = 1 THEN userid ELSE null END, CASE WHEN valid = 1 THEN valid ELSE null END );可能這個索引不能用於查詢優化，但它應該確保每個只能有 1 個有效條目，
userid但相同的無效條目（valid=0）數量不限userid。對於這種允許使用性能的歷史表的攜帶式設計，您有什麼建議？
validfrom+validuntil，在目前有效的條目中validuntil設置為 (nullable)nullvalidfrom+validuntil，validuntil（不可為空）設置為遠未來日期，如2999-12-31目前有效條目中validfrom++validuntil標誌valid，valid由應用程序管理並用於查詢目前有效條目的標誌- …?
插入新版本時，我的應用程序將始終執行兩個步驟：
- 使目前版本無效（設置
validuntil為目前日期（加上，可選地，將valid標誌設置為0））- 插入新版本（
validfromcurrent date，加上，可選，帶有valid標誌1）我不要求數據庫對歷史條目強制執行無重疊時間間隔。我只需要確保只有 1 個具有無限有效性的條目。
current對於一些非常大的表，可能值得拆分成history一個表：一個表只包含目前有效的版本（users_versions_current），另一個包含所有歷史版本（users_versions_history）。每當插入新版本時，都會將以前的版本與validfrom/一起validuntil插入到..._history表中。我應該考慮哪些方面？您知道文獻、最佳實踐建議等嗎？
我必須說我同意其他答案的精神，並且我認為您應該首先專注於建構具有特定數據庫管理系統（DBMS）的最佳*數據庫；*可移植性方面雖然很重要，但應該是次要的。
根據您的問題內容，您似乎對該主題非常熟悉。無論如何，我已經在這篇文章和另一篇文章（包含範例圖、說明性 DDL 程式碼等）中分享了我對涉及時間能力的兩個場景的看法，以防你想看看並建立一些類比。
概念檢查
從概念層面開始分析，所考慮的業務規則可以表述如下：
- 可以有一對多使用者
- 一個使用者恰好持有一個CurrentVersion
- 使用者持有零個或多個**PastVersions
如圖所示，實體類型CurrentVersion和PastVersion涉及一對零或多（或零或多對一）關聯。除了基數之外，可以推斷出我們正在處理兩種不同的實體類型，因為在這種情況下，CurrentVersion實例沒有ValidUntil屬性，而所有PastVersion實例都必須具有它。
邏輯級排列
因此，我建議 (a) 一張基表用於“目前版本”行，(b) 一張基表用於“過去版本”行。通過這種方式，每個表中保留的斷言（即行）代表了一種明顯不同的——儘管相關的——事實（根據關係模型理論），避免了在單個表中臨時引入歧義。
考慮到
user您提出的範例（並且與上述概念定義一致），兩個表的結構幾乎相同，但是user_version（即“過去”版本的表）包含一個附加valid_until列，其中user_id必須構成所述表的複合主鍵。該user_version.user_id列必須被限制為 FOREIGN KEY 引用user.user_id。操縱
當必須“保存”一個“最新”版本時，“先前”版本的整行都會執行 INSERT 操作 INTO
user_version表，並附上相應的valid_until值，指示執行操作的確切時刻。反過來，user（即，“目前”）表中“前一個”行的值通過 UPDATE 被替換為“最新”行。表中的每一行都
user將滿足您必須確保的無限有效性的需求（沒有valid_until列，值在它們被更新之前保持有效，這可能永遠不會到達）。正直
當然，必須注意關聯值的順序性（例如，防止重疊、拒絕無效日期等），就像整體完整性一樣。我將使用ACID 事務來保證相關操作被視為 DBMS 本身內的單個工作單元。具有適當權限的儲存過程（或 Postgres 中的函式）也會非常有幫助。
不需要 NULLable 列——帶有 NULL 標記的表不描述數學關係，所以不能期望它的行為是這樣的，它可以被規範化等等——也不需要
valid由一個（或多個）管理的列應用程序——違反數據庫自我保護原則，危及數據質量——。可導出性
中的值之間包含的時間段代表某個“過去”行是“目前”或“有效”的整個期間（可以按天、分鐘等計算，並且可以合併到視圖中或在應用程序
user_version.valid_from``user_version.valid_until``validity_interval$$ app $$程式碼方便）。這方面和其他相關方面意味著通過數據操作操作（主要是 SELECT 和一些子查詢）派生數據。 通過一個或多個應用程序從外部訪問數據庫
比方說，建構一個物件導向的程式“中間層”，反過來，由一個或多個應用程序（或另一種軟體組件）的“更高層”消耗，也有助於數據庫可移植性，允許程式碼重用以及與遷移到其他 DBMS 的相當大的隔離。這個關於 .NET (C#) 中的儲存庫模式的資源可以在這方面帶來一些想法。
可移植性注意事項
區分建立在 SQL DBMS 上的數據庫的兩個不同抽象級別是很重要的。表的 (1) 結構和 (2) 約束以及 (3) 對錶執行的數據操作操作——INSERT、SELECT、UPDATE、DELETE 及其組合——是邏輯級別的元素。支持表和/或約束的（4）基礎索引是（“較低”）物理級組件。
這樣，相同的邏輯設計原則適用於所有主要 SQL 平台，但是正如您在問題中提到的，各種 SQL DBMS 提供的用於創建邏輯元素的工具之間的差異主要是語法上的，因此可移植性將是受某些特定於方言的 SQL（DDL 和 DML）特性（也可能受特定於 DBMS 的數據類型特徵和名稱）的影響，因此方便的方法是編寫符合 ISO/IEC/ANSI 標準語法的 SQL 程式碼只要可行。
您將面臨的另一個問題是，相同的（邏輯）查詢將根據使用的特定 DBMS 以不同的方式（在物理級別）執行，因此響應時間會有很大差異，因此，您將不得不進行一些重寫以提高速度。
關於物理級機制，是的，每個 SQL 平台都提供不同類型的索引，儘管您應該確保特定於平台的索引設置不會影響數據庫的邏輯級佈局（i ) 在同一 DBMS 上創建/更改索引時或 (ii) 將某個數據庫移植到另一個 DBMS 時（這一點與稱為物理數據獨立性的主題有關）。
在這方面，擁有一個完全獨立於數據定義語言 (DDL) 的強大的儲存定義語言 (SDL) 將非常方便，這將有助於實現邏輯層和物理層之間的關注點的清晰分離，但這是一個不同的故事，所以你應該盡量將邏輯聲明的程式碼與物理設置的程式碼分開，以幫助可移植性——我知道，在目前的 DDL 混合特性下很難實現——。
速度
此外，您應該在物理抽象級別優化數據庫的性能（通過單列或多列索引、升級網路頻寬、改進作業系統和/或 DBMS 和/或硬體配置等），在不損害邏輯結構和約束的質量的情況下，因此將 (1) 數據的完整性和 (2) 結果集的可靠性置於風險之中。邏輯連貫性是一般性能的最重要因素，提供不連貫資訊的軟體很難被視為數據庫，它是否“工作”特別快並不重要。數據庫的可靠性和速度無疑是齊頭並進的。
觀察
至於您的問題中提供的說明性數據，該
users_versions.id列似乎過多，因為它似乎是一個額外的列，旨在保留系統控制的代理鍵（例如，SQL Server 表中具有IDENTITY屬性的列），使該表在邏輯上比必要的更寬，這意味著物理級別（例如，補充索引）上的“更重”結構（以字節計），減慢數據操作操作的執行速度。此外，由於代理鍵值沒有意義，它的封閉列不太可能被指定為 SELECT 操作的 WHERE 子句中的條件（相反，大多數查詢可能包括
users_versions.user_id和/或valid_from和/或valid_until，兩者都包含“自然” PRIMARY KEY），因此users_versions.id實際上根本不會增加任何好處，實際上這將是一個需要不必要管理的負擔。鑑於上述所有情況，我認為這是優化整體系統功能和管理時應考慮的另一個因素。我對系統控制的代理鍵列的更詳細介紹包含在此答案中，如果您感興趣，也包含在此答案中。
專門為一列啟用時間功能
快速回答
整體設計理念
我的建議：
- 永遠不要假設這些表將由一個且只有一個應用程序使用
- 將盡可能多的數據邏輯保留在數據庫中
- 使用 VIEWS 隱藏基礎表。
- 使用數據庫端事務 API
$$ XAPI $$
- 隱藏
Transactional APIs後面的INSTEAD OF觸發器VIEW視圖
而不是讓
SELECT應用程序中的語句執行JOINsResultSet get_current_users(void) { sql String = "select * from users a join user_information b on a.id=b.user_id where ...."; ... }讓數據庫使用 VIEW 隱藏多個表
ResultSet get_current_users(void) { sql String = "select * from current_user_information_view" ... }主要原因：您只有一個地方可以修改程式碼，而不是每個應用程序一個地方。
XAPI
XAPI 的一種解釋是在StackExchange上。
這個概念可能是由Tom Kyte 發起的，但我相信它可以應用於任何支持類似於
t-sql和的過程程式碼的 RDBMSpl/sql。您建議的表佈局將需要鎖定機制以及一個或多個 DML 語句和一個或多個 SELECT 語句。
Transational API我建議將所有這些都封裝在數據庫端。而不是讓應用程序執行所需的步驟
void update_user( user_id int, name int, ...) { lock_user( user_id ) current_row_id int = get_current_active_row( user_id ); etc. }您需要讓數據庫端程式碼執行所需的步驟
void update_user( user_id int, name int, ...) { sql String = "call { update_user( ?, ?, ?); }"; ... }INSTEAD OF 觸發器
通過在 VIEW 上實現 INSTEAD OF 觸發器，您可以讓 Hibernate 或 Oracle APEX 等框架通過簡單的 DML 語句神奇地使用某些 XAPI 呼叫。
我自己沒有使用過這個技巧（我不做休眠）。我只在其他一些執行緒中將其視為建議。您的 Millage 可能會有所不同。
最後的想法
你的工作是編寫高性能程式碼。為此，您需要利用每個數據庫的功能並解決每個數據庫的缺陷。
這表示：
- DDL 會有所不同。
- CREATE TABLES 會有所不同。
- CREATE INDEX 會有所不同
- 使用的鎖定方法會有所不同。
- CREATE VIEW 的 SELECT 語句可能不同。
- 數據庫端的程式碼
Transactional APIs會有所不同。- 對數據庫端 XAPI 的呼叫將有所不同。（我認為 MS-SQL 不支持
PACKAGES）。- INSTEAD OF 觸發器的支持能力可能不同。
此外，您不應相信程式碼只會在數據庫級別有所不同。程式碼也可以是特定於子版本的。
範例 假設您的業務需求是維護數據更改的審計跟踪，那麼您在 Oracle 11.2.0.3 SE 中執行此操作的方式與 11.2.0.4 SE 完全不同
這是因為Flashback Data Archive的許可發生了變化，因此該功能包含在 Oracle 數據庫11.2.0.4及更高版本（非優化版本）的所有版本中。
這將是您必須維護的大量程式碼。
如果您沒有時間、資源、人員、支持或管理批准來實施、單元測試、調試和修復程式碼，那麼與數據庫無關的應用程序的開發將會失敗。
因此，我建議您堅持我提到的整體設計概念，但只為 PostgreSQL 實現。