[解決済み】データベースで継承を表現するには？

質問

SQL Serverデータベースで複雑な構造を表現する方法について考えています。

あるアプリケーションで、いくつかの属性を共有し、他の多くの属性が共通でないオブジェクトのファミリーの詳細を保存する必要があるとします。例えば、商業保険のパッケージには、賠償責任保険、自動車保険、財物保険、損害賠償保険が同じ保険レコードに含まれていることがあります。

C#などでこれを実装するのは簡単で、Sectionのコレクションを持つPolicyを作成し、Sectionは様々なタイプのカバーに必要なように継承されます。しかし、リレーショナルデータベースでは、これを簡単に実現することはできないようです。

大きく分けて2つの選択肢があることがわかりますね。

1. Policyテーブルを作成し、次にSectionsテーブルを作成し、すべての可能なバリエーションについて、すべてのフィールドを必須とし、そのほとんどはNULLとなるようにします。

2. Policyテーブルと多数のSectionテーブルを、カバーの種類ごとに作成します。

特に、すべてのセクションにまたがるクエリを作成する必要があるため、多数の結合や多数のNULLチェックを伴うことになり、これらの選択肢はどちらも満足のいくものではないように思えます。

このシナリオのベストプラクティスは何でしょうか？

解決方法は？

ビル・カーウィン は、3つの継承モデルについて説明しています。 SQLアンチパターン の解決策を提案する際に、SQL エンティティ-アトリビュート-バリュー アンチパターン その概要を紹介します。

単一テーブル継承（別名：テーブル・パー・ヒエラルキー継承）。

最初の選択肢のように単一のテーブルを使用するのが、おそらく最もシンプルな設計でしょう。おっしゃるとおり、サブタイプ固有の属性の多くには、その属性に対応するために NULL の値は、その属性が適用されない行で使用されます。このモデルでは、次のような1つのポリシー・テーブルを持つことになります。

+------+---------------------+----------+----------------+------------------+
| 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 属性は、単一テーブル方式の場合と同様です。型はメタデータであるテーブル名で定義されるようになりました。

しかし、このモデルには、いくつかのデメリットもあります。

• 共通属性とサブタイプ固有の属性が混在しており、簡単に識別することができない。データベースもわからない。

• テーブルを定義する際、サブタイプのテーブルごとに共通属性を繰り返す必要があります。それは絶対に DRY .

• サブタイプに関係なく、すべてのポリシーを検索することは難しくなり、大量の UNION s.

このように、タイプに関係なく、すべてのポリシーを照会する必要があります。

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 が言及しているように . ベースクラス用に1つのテーブルを作成し、そのテーブルにはすべての共通属性が含まれています。それから、それぞれのサブタイプに対応したテーブルを作成し、その主キーは 外部キー をベース・テーブルに追加します。例

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)
);

このソリューションは、他の2つのデザインで確認された問題点を解決するものです。

• 必須属性は NOT NULL .

• 新しいサブタイプを追加するには、既存のテーブルにカラムを追加するのではなく、新しいテーブルを追加する必要があります。

• 特定のサブタイプに不適切な属性が設定されるリスクがない。

• は必要ありません。 type 属性があります。

• これで、共通属性とサブタイプ固有の属性が混在することはなくなりました。

• ようやくDRYでいられるようになりました。テーブルを作成する際に、各サブタイプテーブルの共通属性を繰り返す必要はありません。

• オートインクリメントの管理 id は、各サブタイプ・テーブルが個別に生成するのではなく、ベース・テーブルで処理できるため、ポリシーの生成が容易になります。

• サブタイプに関係なく、すべてのポリシーの検索が非常に簡単になりました：No UNION は必要ありません。 SELECT * FROM policies .

私は、ほとんどの場面で、クラステーブルのアプローチが最も適していると考えています。

この3つのモデルの名前の由来は マーティン・ファウラーの 著書 エンタープライズアプリケーションアーキテクチャのパターン .