データベースで継承を表現するにはどうすればよいですか?

Question

SQL Server データベース内の複雑な構造をどのように表現するかを考えています。

いくつかのプロパティを共有するが、その他多くの珍しいプロパティを持つ一連のオブジェクトに関する詳細を保存する必要があるアプリケーションを考えてみましょう。たとえば、ビジネス保険パッケージには、同じ保険契約レコードに賠償責任、自動車、財産、および賠償の補償が含まれる場合があります。

これを、たとえば C# で実現するのは簡単です。パーツのコレクションを含むポリシーを作成し、さまざまな種類のオーバーライドに必要に応じてパーツを継承できるからです。ただし、リレーショナル データベースではこれが許可されていないようです。

主なオプションが 2 つあることがわかります:

1. 戦略テーブルを作成してから、考えられるすべてのバリエーションに必要なすべてのフィールドを含む部分テーブルを作成します。そのほとんどは空です。

2. 各テーブルが保険の種類に対応する、保険契約テーブルと複数の部分テーブルを作成します。

どちらの代替案も、特にクエリをすべての部分にわたって記述する必要があり、大量の結合または大量の null チェックが必要になるため、満足のいくものではないようです。

このシナリオのベスト プラクティスは何ですか?

Answer 1

3 番目のオプションは、「Policy」テーブルを作成し、次に「SectionsMain」テーブルを作成して、さまざまな種類のセクションに共通するすべてのフィールドを保存することです。次に、セクションのタイプごとに、一般的ではないフィールドのみを含む追加のテーブルを作成します。

どれが最適かを決定するのは、主にフィールドの数と SQL の記述方法によって決まります。それらはすべて機能します。フィールドが数個しかない場合は、おそらく #1 を使用するでしょう。 「多くの」分野については、私は 2 位か 3 位を好みます。

Answer 2

@Bill Karwin 著書 SQL アンチパターン の中で、SQL Entity Attribute Value アンチパターンを提案しました。簡単な概要は次のとおりです:

単一テーブルの継承 (階層ごとのテーブル継承):

最初のオプションのように 1 つのテーブルを使用するのが、おそらく最も単純な設計です。前述したように、多くのサブタイプ固有のプロパティには、これらのプロパティが適用されない行に NULL 値を与える必要があります。このモデルを使用すると、次のようなポリシー テーブルが作成されます:

リーリー

設計をシンプルに保つことは利点ですが、このアプローチの主な問題は次のとおりです。

• 新しいサブタイプを追加するときは、これらの新しいオブジェクトを説明するプロパティに対応するようにテーブルを変更する必要があります。サブタイプが多数ある場合、またはサブタイプを定期的に追加する予定がある場合、これはすぐに問題になる可能性があります。

• どのプロパティがどのサブタイプに属するかを定義するメタデータがないため、データベースはどのプロパティが適用され、どのプロパティが適用されないかを強制することができません。

• 強制する必要があるサブタイプ属性に NOT NULL を強制することもできません。これはアプリケーションで処理する必要がありますが、これは通常理想的ではありません。

特定のテーブルの継承:

継承の問題を解決するもう 1 つの方法は、サブタイプごとに新しいテーブルを作成し、各テーブル内のすべての共通プロパティを繰り返すことです。例えば：＃＃＃ リーリー

この設計は、単一テーブルのアプローチで特定された問題を基本的に解決します:

• 必須属性を

  NOT NULL 経由で強制できるようになりました。

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

• 属性ポリシーの

  vehicle_reg_no フィールドなど、特定のサブタイプに不適切な属性を設定するリスクもありません。

• 単一テーブル メソッドのような

  type 属性は必要ありません。タイプはメタデータ: テーブル名によって定義されるようになりました。

しかし、このモデルにはいくつかの欠点もあります:

• パブリック プロパティはサブタイプ固有のプロパティと混在しているため、それらを識別する簡単な方法はありません。データベースも知りません。

• テーブルを定義するときは、サブタイプ テーブルごとに共通のプロパティを繰り返す必要があります。これは決して

  干 ではありません。

• サブタイプに関係なくすべての戦略を検索することは困難になり、大量の

  UNION が必要になります。

タイプに関係なく、次の方法ですべてのポリシーをクエリする必要があります:

リーリー

新しいサブタイプを追加するには、サブタイプごとに

UNION ALL を追加して上記のクエリを変更する必要があることに注意してください。これを忘れると、アプリケーションで簡単にエラーが発生する可能性があります。

クラス テーブルの継承 (別名、型ごとのテーブル継承):

これは、@David が別の回答

で言及した解決策です。すべてのパブリック プロパティを含む基本クラスのテーブルを作成します。次に、サブタイプごとに特定のテーブルを作成し、その主キーがベース テーブルとしても機能します。例： ＃＃＃ リーリー このソリューションは、他の 2 つの設計で見つかった問題を解決します:

必須属性は、
• NOT NULL

  によって強制できます。

新しいサブタイプを追加するには、既存のテーブルに列を追加するのではなく、新しいテーブルを追加する必要があります。
特定のサブタイプに不適切なプロパティを設定するリスクはありません。

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

• 現在、パブリック プロパティはサブタイプ固有のプロパティと混合されなくなりました。

• ようやくドライな状態を維持できるようになりました。テーブルの作成では、各サブタイプ テーブルの共通プロパティを複製する必要はありません。

• id の自動インクリメント ポリシーの管理は、各サブタイプ テーブルが個別に生成するのではなく、ベース テーブルによって処理できるため、より簡単になります。

• すべてのストラテジ (サブタイプに関係なく) の検索が非常に簡単になりました。UNION は必要ありません。SELECT * FROM Strategy だけです。

ほとんどの場合、クラス テーブル方式が最も適切だと思います。

---

これら 3 つのモデルの名前は、Martin Fowler という書籍 Enterprise Application Architecture Patterns に由来しています。