在 RDBMS 中儲存產品的多次迭代的最佳實踐是什麼？

我們正在建構一個工具來隨著時間的推移跟踪產品價格，並使用 Postgres 作為我們的 RDBMS。重要的是可以更改產品屬性，並且永遠保留產品屬性的歷史。這是我們基於OpenStreetMap 的內部架構設計的架構：

我們在左側有一個“products”表，用於儲存每個產品的每個版本，在右側有一個“current_products”表，僅儲存每個產品的最新版本。每次我們想改變商店時，我們：

1. 在變更集中創建一個條目
2. 閱讀“產品”中產品的最新條目，將版本遞增一，並創建另一個包含更改的條目
3. 刪除“current_products”中的相應條目，並使用“products”中的更改和最新版本號創建一個新條目

我們希望在數據庫引擎中執行盡可能多的業務規則，而不是依靠我們的軟體來保持一致，而且這種模式感覺很“不正常”，所以我們歡迎任何建議。提前致謝！

---

編輯：根據@bbaird 的響應修改了架構。還決定包括商店和使用者的版本控制。將產品、商店和使用者與價格表捆綁在一起。

您感覺模式已關閉是正確的，因為它是 - 現在設計的方式不能保證一致性所需的最低標準：截至某個時間點，給定屬性只能存在一個值。

有兩種方法可以處理這個問題，具體取決於案例：

1. 應用程序需要訪問不同版本的屬性
2. 僅出於審計原因必須跟踪更改

解決方案：案例1

您將有一個Product表和一個Product_Version來儲存必要的資訊。您將需要一個視圖/函式來返回正確的值。

由於您正在處理食物（和標準來源），因此我將對鍵/數據類型做出某些假設。隨時發表評論以澄清。

CREATE TABLE Product
(
 Barcode  VARCHAR(13)  NOT NULL
 /* Store all invariant attributes in this table */
,CONSTRAINT PK_Product PRIMARY KEY (Barcode) /* This uniquely defines a product and is compact enough - no other key is necessary */
)
;

CREATE TABLE Product_Version
(
 Barcode        VARCHAR(13)    NOT NULL
,Change_Dtm     TIMESTAMP(6)   NOT NULL
,Name           VARCHAR(50)    NOT NULL
,Price          DECIMAL(8,2)   NOT NULL /* Adjust as necessary */
,Currency_Cd    CHAR(3)        NOT NULL /* Should reference a Currency table with ISO codes (USD, EUR, GBP, etc) */
,Delete_Ind     CHAR(1)        NOT NULL
,Change_UserId  VARCHAR(32)    NOT NULL
,CONSTRAINT FK_Product_Version_Version_Of_Product FOREIGN KEY (Barcode) REFERENCES Product (Barcode)
,CONSTRAINT PK_Product_Version PRIMARY KEY (Barcode, Change_Dtm)
,CONSTRAINT CK_Product_Version_Price_GT_Zero CHECK (Price > 0)
,CONSTRAINT CK_Product_Version_Delete_Ind_IsValid CHECK (Delete_Ind IN ('Y','N'))
)
;

要獲取特定產品在某個時間點的值，您可以使用以下查詢：

SELECT
 PV.Barcode
,PV.Name
,PV.Price
,PV.Currency_Cd
FROM
 Product_Version PV
WHERE
 PV.Barcode = '8076809513388'
   AND PV.Change_Dtm =
     (
       SELECT
         MAX(Change_Dtm)
       FROM
         Product_Version
       WHERE
         Barcode = PV.Barcode
           AND Change_Dtm <= '2020-10-29 12:30:00.000000'
     )

您還可以創建一個視圖來模擬具有靜態值的表的功能：

CREATE VIEW v_Product AS
SELECT
 PV.Barcode
,PV.Name
,PV.Price
,PV.Currency_Cd
FROM
 Product_Version PV
WHERE
 PV.Change_Dtm =
   (
     SELECT
       MAX(Change_Dtm)
     FROM
       Product_Version
     WHERE
       Barcode = PV.Barcode
   )

對於一對多關係（讓我們Ingredient在本例中使用），您將遵循如下模式：

CREATE TABLE Product_Ingredient
(
 Barcode     VARCHAR(13)   NOT NULL
,Ingredient  VARCHAR(50)   NOT NULL  /* Should reference an Ingredient table */
,Rank        SMALLINT      NOT NULL  /* Uniqueness of this value needs to be handled through transaction logic */
,Change_Dtm  TIMESTAMP(6)  NOT NULL
,Delete_Ind  CHAR(1)       NOT NULL
,CONSTRAINT FK_Product_Ingredient_Used_In_Product FOREIGN KEY (Barcode) REFERENCES Product (Barcode)
,CONSTRAINT PK_Product_Ingredient PRIMARY KEY (Barcode, Change_Dtm)
,CONSTRAINT CK_Product_Ingredient_Delete_Ind_IsValid CHECK (Delete_Ind IN ('Y','N'))
)
;

然後要在某個時間點獲取Ingredientsfor a的列表Product，您將使用以下查詢：

SELECT
 PI.Barcode
,PI.Ingredient
,PI.Rank
FROM
 Product_Ingredient PI
WHERE
 PI.Barcode = '8076809513388'
   AND PI.Change_Dtm =
     (
       SELECT
         MAX(Change_Dtm)
       FROM
         Product_Ingredient
       WHERE
         Barcode = PI.Barcode
           AND Ingredient = PI.Ingredient
           AND Change_Dtm <= '2020-10-29 12:30:00.000000' /* Or whatever */
     )
   AND PI.Delete_Ind = 'N'

與前面的範例類似，您可以創建一個視圖來為每個一對多關係提供目前值。

解決方案：案例 2

如果你只需要儲存歷史，你只需對結構做一個小的修改：

CREATE TABLE Product
(
 Barcode        VARCHAR(13)    NOT NULL
,Name           VARCHAR(50)    NOT NULL
,Price          DECIMAL(8,2)   NOT NULL
,Currency_Cd    CHAR(3)        NOT NULL
,Change_UserId  VARCHAR(32)    NOT NULL
,Change_Dtm     TIMESTAMP(6)   NOT NULL
,Delete_Ind     CHAR(1)        NOT NULL
,CONSTRAINT PK_Product PRIMARY KEY (Barcode)
,CONSTRAINT CK_Product_Price_GT_Zero CHECK (Price > 0)
,CONSTRAINT CK_Product_Delete_Ind_IsValid CHECK (Delete_Ind IN ('Y','N'))
)
;

CREATE TABLE Product_Audit
(
 Barcode        VARCHAR(13)    NOT NULL
,Name           VARCHAR(50)    NOT NULL
,Price          DECIMAL(8,2)   NOT NULL
,Currency_Cd    CHAR(3)        NOT NULL
,Change_Dtm     TIMESTAMP(6)   NOT NULL
,Change_UserId  VARCHAR(32)    NOT NULL
,Delete_Ind     CHAR(1)        NOT NULL
,CONSTRAINT PK_Product_Audit PRIMARY KEY (Barcode, Change_Dtm)
)
;

在這種情況下，每當為 a 呼叫更新或刪除時，都會Product執行以下操作：

1. 將目前行插入審計表Product
2. Product使用新值更新表

筆記：

1. 這個討論中隱含的是，只有當數據發生變化時才會寫入新數據。您可以通過事務/ETL 邏輯或觸發回滾嘗試插入與先前值完全相同的數據來強制執行此操作。這不會影響為給定查詢返回的數據，但它可以確保您的表大小不會不必要地爆炸。
2. 如果您有很多屬性，並且有些屬性經常更改（例如Price），而另一些則沒有（Name, Description），您總是可以將事物拆分為更多表（Product_Price,Product_Name等）並創建一個包含所有這些元素的視圖. 除非實體具有很多屬性，否則通常不需要這種級別的工作，或者您將有很多臨時查詢，這些查詢會詢問特定時間的問題，這些問題依賴於知道先前值實際上是不同的，例如“哪個產品在此時間段內漲價？”
3. 至關重要的是，您不要遵循僅Id在每張桌子上都貼一個並認為可以提供任何價值的模式。時變數據總是需要復合鍵，並且只有在數據正確規範化到至少 3NF 時才會返回一致的結果。不要使用任何不支持複合鍵的 ORM。

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