在不犧牲關係數據庫中數據一致性的情況下解決超類型-子類型關係

為了更好地設計數據庫，我注意到我總是被困在試圖解決完全相同的問題的變體。

這是一個使用常見要求的範例：

1. 一家線上商店銷售不同類別的product.
2. 系統必須能夠檢索所有產品類別的列表，例如food和furniture。
3. 客戶可以訂購任何產品並檢索他的order歷史記錄。
4. 系統必鬚根據產品類別儲存特定屬性；對任何產品和任何產品說expiration_dateand 。calories``food``manufacture_date``furniture

如果不是要求 4，該模型可能非常簡單：

問題是試圖解決要求 4。我想到了這樣的事情：

在這種方法中，關係product-furniture和product-food是超類型-子類型（或超類-子類）關聯；子類型的主鍵也是超類型主鍵的外鍵。

但是，這種方法不能保證category通過外鍵引用的product會與其實際的子類型保持一致。例如，沒有什麼能阻止我將food類別設置為表中具有子類型行的產品元組Furniture。

我閱讀了有關建模關係數據庫中的繼承的各種文章，尤其是這篇和這篇非常有幫助的文章，但由於上述原因並沒有解決我的問題。但是無論我使用哪種模型，我都對數據的一致性不滿意。

如何在不犧牲數據一致性的情況下解決需求 4？我在這裡錯了嗎？如果是這樣，根據這些要求解決此問題的最佳方法是什麼？

一種常見的方法是添加一個“繼承”的分類器，例如：

CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... NOT NULL 
,     UNIQUE (product_id, products_type)
,     CHECK (product_type IN ('food', 'furniture'))
);

product_type 通常是某種程式碼。可能是“查找”表的外鍵而不是檢查約束。對於子類型：

CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
, product_type ... DEFAULT 'food' NOT NULL
,    FOREIGN KEY (product_id, product_type)
    REFERENCES products (product_id, product_type)
,    CHECK (product_type = 'food')
);

和一個類似的家具。約束保證 product_type 在超子表之間是一致的。

有些產品（我聽說過）允許在 CHECK 約束中進行子選擇，但大多數都不允許。對於此類產品，例如：

CREATE TABLE food
( product_id ... NOT NULL PRIMARY KEY
, ...
,    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
,    CHECK (
        (SELECT product_type 
         FROM products p 
         WHERE p.product_id = product_id) = 'food'
    )
);

可用於。

後者的替代方法是使用 before 觸發器進行插入/更新，如果使用了錯誤的 product_type，則發出異常信號。就我個人而言，我不喜歡使用過程程式碼來進行完整性約束，但我想這是一個品味問題。

也許我正在簡化要求，但要滿足要求並且您關心的是只需將子類型中的所需列設置為 NOT NULL。當創建一個新的 PRODUCT 行食物時，也會創建一個新的 FOOD 行，如果沒有提供 FOOD 所需的資訊，那麼數據庫將拒絕該事務。

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