質問: 下記のような3つのテーブルの設計方法が知りたいです。 ・Issuesテーブル ・BugsCommentsテーブル ・FeaturesCommentsテーブル ・Issuesテーブルのカラム id ・BugsCommentsテーブルのカラム id, issue_id ・FeaturesCommentsテーブルのカラム id, issue_id IssuesテーブルがBugsCommentsテーブルとFeaturesCommentsテーブルの「どちらか片方だけ」と 関連したい場合、 アプリケーション側で管理する必要があると考えているのですが、 RDBの機能で整合性を担保してくれるような機能or設計はできますでしょうか? テーブルはSQLアンチパターンの6章ポリモーフィック関連を参考にしました。
回答: 書籍SQLアンチパターンの記述に沿って説明を試みてみます。 前置き === `Issue`, `Bug`, `FeatureRequest`クラス(p.52 5.1節 図5-2)をクラステーブル継承(p.62 5.3.3節)でテーブルへマッピングすると、`Issues`, `Bugs`, `FetureRequests`で構成された次の形になります(p.78, 6.5.6節 図6-5)。 !図5-2 Issue図クラスツリー **図5-2 Issueクラスツリー** !図6-5' Issueクラスマッピング(クラステーブル継承戦略) **図6-5' Issueクラスマッピング(クラステーブル継承戦略)** ここで登場する`Bugs`に複数のコメントが付けられるようにするには、`Bugs`と`Comments`を1対多で関連付ければ良い(p.67 6章 図6-1)のですが、更に`FeatureRequests`に対しても`Comments`を付けたい場合にはどうしよう、というのが6章のテーマです。 !図6-1' BugsとFeatureRequest双方に同じテーブルについて1対多関連を追加したい **図6-1' BugsとFeatureRequest双方に同じテーブルについて1対多関連を追加したい** 本章の結論では、交差テーブル`BugsComments`,`FeaturesComments`を導入し次のような形にする、としています(p.75 6.5.3節 図6-4/なお下図は書籍の図を上下反転していることに注意)。 !図6-4' 交差テーブルを導入したIssues, Comments関連 **図6-4' 交差テーブルを導入したIssues, Comments関連** ところで、交差テーブルとして導入した2テーブルですが、これは`Comment`の派生クラスとみなすこともできます。 クラス図で表現すると次のようになります。 !画像の説明をここに入力 **図6-4' クラス図で表現** そして本書の次節の説明(p.78 6.5.6節)では、派生クラス同士の関連を基底クラス同士の関連に置き換えること相当を行っています(リファクタリング手法でいうところのプルアップ)。 クラス図で表現すると次のようになる操作です。 !画像の説明をここに入力 **図6-5' プルアップ後クラス図表現** 回答 == > > IssuesテーブルがBugsCommentsテーブルとFeaturesCommentsテーブルの「どちらか片方だけ」と関連したい > > > プルアップを行わずに、前述の **図6-4'** の状態で止めておけば良い、ということになります。 そうすれば、`BugComment`としか関連しない`Bug`と`FeatureComment`としか関連しない`FeatureRequest`が表現できます。これらはいずれも`Issue`の具象クラスです。 この回答についての問題 =========== `Issues`は`Bugs`か`FeatureRequests`のどちらか一方にしか関連しない前提があるのですが、この前提はRDBの制約では表現できません(p.63 5.5.3節のsql参照)。 すなわち実際には、今回の質問文にある、「アプリケーション側で管理する必要」な部分が他の箇所に転移しただけです(別の制約をアプリケーションで正しく管理しなければならない)。 本書でなぜこれが問題であると書かれていないのかというと、おそらく、(本書の記載とは反しますが)継承関係のマッピングはO/Rマッパに管理させることが念頭にありアプリケーションでは意識する必要が無い、と考えているのではないかなと想像します。 (※似たような問題はp.75 6.5.3節で言及されているにもかかわらずクラス継承関係については触れていないことから想像。このことへの言及を避けるためにERDの記述も一貫性を欠いている箇所がある。)