☰

データベースで継承を表現するにはどうすればいいですか? [closed] 質問する

tag-icon • tag-icon sql-server inheritance database-design class-table-inheritance
データベースで継承を表現するにはどうすればいいですか? [closed] 質問する

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

いくつかの属性は共通しているが、他の多くの属性は共通していないオブジェクト ファミリの詳細を保存する必要のあるアプリケーションについて考えてみましょう。たとえば、商業保険パッケージには、同じポリシー レコード内に賠償責任、自動車、財産、および補償が含まれる場合があります。

C# などでこれを実装するのは簡単です。セクションのコレクションを持つポリシーを作成でき、セクションはさまざまなタイプのカバーの必要に応じて継承されます。ただし、リレーショナル データベースではこれを簡単には実行できないようです。

主な選択肢は 2 つあることがわかります。

  1. ポリシー テーブルを作成し、次にセクション テーブルを作成します。このテーブルには、考えられるすべてのバリエーションに必要なすべてのフィールドが含まれますが、そのほとんどは null になります。

  2. カバーの種類ごとに 1 つずつ、ポリシー テーブルと多数のセクション テーブルを作成します。

これら 2 つの代替案はどちらも不十分であると思われます。特に、すべてのセクションにわたってクエリを記述する必要があり、多数の結合や多数の null チェックが必要になるためです。

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

ベストアンサー1

@ビル・カーウィン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必須であるべきサブタイプの属性を強制することもできません。これはアプリケーション内で処理する必要がありますが、これは一般的に理想的ではありません。

具体的なテーブル継承:

継承に対処するもう 1 つの方法は、サブタイプごとに新しいテーブルを作成し、各テーブルで共通の属性をすべて繰り返すことです。例:

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

このソリューションは、他の 2 つの設計で特定された問題を解決します。

  • 必須属性は で強制できますNOT NULL。

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

  • 特定のサブタイプに不適切な属性が設定されるリスクはありません。

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

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

  • ついに DRY を維持できるようになりました。テーブルを作成するときに、サブタイプ テーブルごとに共通属性を繰り返す必要はありません。

  • id各サブタイプ テーブルが個別にポリシーを生成するのではなく、ベース テーブルでポリシーの自動増分を処理できるため、ポリシーの自動増分の管理が容易になります。

  • サブタイプに関係なくすべてのポリシーを検索するのが非常に簡単になりました。 は不要でUNION、 だけですSELECT * FROM policies。

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

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

おすすめ記事