如何表示資料庫中的繼承？

Question

我正在考慮如何在 SQL Server 資料庫中表示複雜的結構。

考慮一個需要儲存一系列物件的詳細資訊的應用程序，這些物件共享一些屬性，但有許多其他不常見的屬性。例如，商業保險套餐可能在同一保單記錄中包括責任險、機動險、財產險和賠償險。

在 C# 等實現這一點很簡單，因為您可以建立包含部分集合的策略，其中部分根據各種類型的覆蓋的需要繼承。然而，關係資料庫似乎不允許這樣做。

我可以看到有兩個主要選擇：

1. 建立一個策略表，然後建立一個部分錶，其中包含所有可能的變體所需的所有字段，其中大部分為空。

2. 建立策略表和多個部分錶，每個表對應一種保險。

這兩種替代方案似乎都不能令​​人滿意，特別是因為需要跨所有部分編寫查詢，這將涉及大量聯接或大量空檢查。

此場景的最佳實踐是什麼？

Answer 1

第三個選項是建立一個「Policy」表，然後建立一個「SectionsMain」表，用於儲存跨不同類型的部分所共有的所有欄位。然後為每種類型的部分建立其他表，僅包含不常見的欄位。

決定哪個最好主要取決於您有多少欄位以及您想要如何編寫 SQL。他們都會工作。如果你只有幾個字段，那麼我可能會選擇#1。對於「很多」領域，我傾向於#2 或#3。

Answer 2

@Bill Karwin 在他的 SQL Antipatterns 書，在提出 SQL 實體屬性值反模式。這是一個簡短的概述：

單表繼承（又稱每層次結構表繼承）：

像第一個選項一樣使用單一表格可能是最簡單的設計。正如您所提到的，許多特定於子類型的屬性必須在這些屬性不適用的行上被賦予 NULL 值。使用此模型，您將擁有一個策略表，如下所示：

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

保持設計簡單是一個優點，但這種方法的主要問題如下：

• 在新增的子類型時，您必須變更表格以適應描述這些新物件的屬性。當您有許多子類型或您計劃定期添加子類型時，這很快就會成為問題。

• 資料庫將無法強制執行哪些屬性適用，哪些不適用，因為沒有元資料來定義哪些屬性屬於哪些子類型。

• 您也無法強制執行本應強制執行的子類型屬性 NOT NULL。您必須在應用程式中處理這個問題，這通常並不理想。

具體表繼承：

解決繼承問題的另一種方法是為每個子類型建立一個新表，重複每個表中的所有公共屬性。例如：

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+
                          
--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

這種設計基本上將解決單表方法所確定的問題：

• 現在可以透過 NOT NULL 強制執行強制屬性。

• 新增子類型需要新增資料表，而不是新增資料表列。

• 也不存在為特定子類型設定不適當屬性的風險，例如屬性策略的 vehicle_reg_no 欄位。

• 不需要像單表方法中的 type 屬性。該類型現在由元資料定義：表名稱。

但是這種模型也有一些缺點：

• 公共屬性與子類型特定屬性混合在一起，沒有簡單的方法來識別它們。資料庫也不知道。

• 定義表格時，您必須為每個子類型表重複公共屬性。這絕對不是幹。

• 無論子類型如何，搜尋所有策略都變得很困難，並且需要一堆 UNION。

無論類型為何，您都必須透過以下方式查詢所有策略：

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

請注意，新增的子類型將需要為每個子類型使用附加的 UNION ALL 來修改上述查詢。如果忘記此操作，很容易導致應用程式出現錯誤。

類別表繼承（又稱每個類型的表繼承）：

這是@David 在中提到的解決方案另一個答案。您為基類建立一個表，其中包括所有公共屬性。然後，您將為每個子類型建立特定的表，其主鍵也充當基底表。範例：

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

該解決方案解決了其他兩種設計中發現的問題：

• 可以透過 NOT NULL 強制執行強制屬性。

• 新增子類型需要新增資料表，而不是新增資料表列。

• 沒有為特定子類型設定不適當屬性的風險。

• 不需要 type 屬性。

• 現在公共屬性不再與子類型特定屬性混合。

• 我們終於可以保持乾燥了。建立表格時無需重複每個子類型表的公共屬性。

• 管理策略的自動遞增 id 變得更容易，因為這可以由基底表處理，而不是每個子類型表獨立產生它們。

• 搜尋所有策略（無論子類型為何）現在都變得非常容易：不需要 UNION - 只需 SELECT * FROM 策略。

我認為類別表方法在大多數情況下是最合適的。

---

這三個模型的名稱來自Martin Fowler一本書企業應用架構模式。