はじめに
本エントリーは某社内で実施するSQL勉強会向けの資料となります。
本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはSQL Serverに置き換えて解説します。
また本章以外のエントリーおよび利用するSQLリソースなどは、以下のGitHubを参照ください。
#ポリモーフィック関連とは
ポリモーフィック関連とは、複数の親テーブルを参照する際に用いられるパターンです。
しかし、ポリモーフィック関連を用いるとそれぞれのテーブル間の参照整合性制約を定義することが出来なくなるというアンチパターンでもあります。
以下で詳細に述べていきます。
#使用する状況の例
バグテーブルに対して、コメントを書けるようにしたい
条件として、
- バグに対して複数のコメントを書けるようにする
- どのコメントもいずれか一つのバグへ関連付かなければいけない
つまりBugsとCommentsは1対多の関係にする
図1
しかし、Bugsテーブルと同じようにコメントを書き込みたいテーブルがもう一つ存在する(FeatureRequests)
図2
コメント自体は書き込む先がBugs、FeatureRequestsに関わらず、一つのテーブル(Comments)に格納したい。
そのような時、どのような設計にしよう...
#解決策1. ポリモーフィック関連
Commentテーブルに従来のissue_idのような外部キー列に加えて文字列型の列(issue_type)を追加。追加した列には、参照する親テーブルの名前を格納する。
今回の例ではissue_typeに「Bugs」または「FeatureRequests」が格納されます。
■Bugsテーブル
| issue_id | summary |
|---|---|
| 1 | <%:%> Is an error |
| 2 | Design is bad |
■FeatureRequestsテーブル
| issue_id | summary |
|---|---|
| 1 | I want the design of the ImKeeper to be improved |
| 2 | I'd like to add a help function |
■Commentsテーブル
| comment_id | issue_type | issue_id | comment |
|---|---|---|---|
| 1 | Bugs | 1 | ImKeeper was rubbish |
| 2 | FeatureRequests | 1 | ImKeeper is hard to use ! |
| 3 | FeatureRequests | 2 | That's a good idea |
■クエリ実行例
- 主キー値2のバグのコメントを取得したい場合
SELECT *
FROM Bugs INNER JOIN Comment
ON Bugs.issue_id = Comment.issue_id AND Comment.issue_type = "Bugs"
WHERE Bugs.issue_id = 2 ;
- コメントが付いているバグまたは機能要求を取得する場合
両方の親テーブルを外部結合してクエリを実行する必要がある
SELECT *
FROM Comment
LEFT OUTER JOIN Bugs
ON Bugs.issue_id = Comment.issue_id AND comment.issue_type = 'Bugs'
LEFT OUTER JOIN FeatureRequests AS f
ON f.issue_id = Comment.issue_id AND Comment.issue_type = 'FeatureRequests' ;
■デメリット
外部キーの宣言が出来ないため、 参照整合性制約 を定義できない
(今回のパターンでは複数のテーブルを参照先としており、外部キー宣言は一つのテーブルのみを指定しなければならないため)
Commentsテーブルのissue_idの値が親テーブル(Bugs or FeatureRequests)の値と一致するか保証できない
同様に、Commentsテーブルのissue_typeの値がデータベースに存在するテーブル名に対応しているか保証できない
■ポリモーフィック関連を意識的に選択するとき
ポリモーフィック関連を意識的に選択する理由として、「ORM(オブジェクトリレーショナルマッピング)を使った設計を行う際に、子側のテーブルを設計/作成する時点でまだ存在しない親テーブルとの関連を作成できる」こと、がある。
例として、
- ポリモーフィック関連の子側が複数の案件で共通して使われるフレームワークで、親側が個々の案件のアプリケーションである場合
- ポリモーフィック関連の子側がパッケージ製品で、親側がパッケージ購入者のアプリケーションである場合
- 関連の子側のテーブルと親側のテーブルの作成時期が離れているときなどである。
逆に言えば、ORMを使わない場合や、親子共に一つのアプリケーションで親子テーブルがほぼ同時期に設計される場合にはアンチパターンとなる。
以降で説明する解決策2、解決策3で設計を再検討するべきである。
#解決策2. 交差テーブル作成
CommentsテーブルとBugs、FeatureRequestsテーブルの間に交差テーブル(BugsComment、FeaturesComments)を作成。
■Bugsテーブル
| bugs_id | summary |
|---|---|
| 1 | ImKeeper terminated abnormally |
| 2 | Design is bad |
■FeatureRequestsテーブル
| featureRequests_id | summary |
|---|---|
| 1 | I want the design of the ImKeeper to be improved |
| 2 | I'd like to add a help function |
■BugsCommentsテーブル
| bugs_id | comment_id |
|---|---|
| 1 | 1 |
■FeatureCommentsテーブル
| featureRequests_id | comment_id |
|---|---|
| 1 | 2 |
| 2 | 3 |
■Commentsテーブル
| comment_id | comment |
|---|---|
| 1 | ImKeeper was rubbish |
| 2 | ImKeeper is hard to use ! |
| 3 | That's a good idea |
■クエリ実行例
- 特定のバグ又は機能要求を指定したコメントへのクエリを実行できる
SELECT *
FROM BugsComments As b
INNER JOIN Comments AS c
ON c.comment_id = b.comment_id
WHERE b.bugs_id = 1234 ;
- 両方の交差テーブルと外部結合を行うことで、あるコメントに関連づいたバグ又は機能要求を取得できる
SELECT *
FROM Comments As c
LEFT OUTER JOIN (
BugsComments INNER JOIN Bugs AS b
USING(bugs_id)
) USING (comment_id)
LEFT OUTER JOIN (
FeatureComments INNER JOIN FeatureRequest AS f
USING (featureRequests_id)
) USING (comment_id)
WHERE c.comment_id = 9876;
■メリット
- この交差テーブルではCommentsテーブルの外部キーに加え、各親テーブル(Bugs、FeatureRequests)にも外部キーが定義する。よってデータの整合性を取ることができる。
■デメリット
- 交差テーブルは本来、"多対多"の関連付けに使われるものである。
しかし今回、コメントは一つのバグ又は機能要求用であり、複数のバグ又は機能要求に同じコメントを使用することはない。
(多対多の例として・・・コメントテーブルの代わりにシステムテーブルがあるとすると、バグにシステムを紐づけ、
同じシステムに複数回バグが起きるとそのたび関連テーブルにbug_idは異なるが、同じsystem_idが入る状態である)
この状態(多対多の関連)をなくすには、各交差テーブルのcomment_id列にUNIQUE制約を付ければ良い。
しかし、一つのコメントが両方の交差テーブルから呼び出される場合には対処できていない。
先程述べた、コメントは一つのバグ又は機能要求用、ということに反してしまっている。
(例として、両交差テーブルのcomment_id の列に1 が入ったデータが存在する状態)
この状態を防ぐためにはアプリケーションコード側で対処しなければならない。
#解決策3. 共通の親テーブル作成
Issueという共通の親テーブルを作成する。
基底テーブルを参照するために、子であるCommentsテーブルに外部キーを加える。
BugsとFeatureRequestsの主キーは、外部キーでもあり、自身で新たな値を生成せず、Issueテーブルで生成された疑似キー(issue_id)を参照する。
Bugsテーブルとその先祖であるIssueテーブルの主キー値は同じであるため、Bugs、FeatureRequestsを直接Commentsに結合できる。
■クエリ実行例
- 特定のコメントが参照しているバグまたは機能要求を取得できる
Issueテーブルでissue_id以外の属性列を定義していない限り、クエリにIssueテーブルを含める必要はない。
SELECT *
FROM Comment
LEFT OUTER JOIN Bugs USING (issue_id)
LEFT OUTER JOIN FeatureRequests USING (issue_id)
WHERE Comments.comment_id = 2 ;
- 特定のバグのコメントを取得
SELECT *
FROM Bugs
INNER JOIN Comments USING (issue_id)
WHERE Bugs.issue_id = 1234;
■メリット
- Issueのような基底テーブルを使うことにより、外部キーによるデータ整合性制約に依存できる
■デメリット
- この方法でも、一つのコメントが両方のテーブルから呼び出される場合には対処できていない
#解決策4. 共通テーブル作成
Issueテーブルを作成し、そこにBugsもFeatureRequestsも含める設計
■メリット
- 上記で述べた解決策ではデータベース上CommentがBugsかFeatureRequestsのいづれか一つに存在する、ということが出来ないが、この解決策では実装することが出来る。
■デメリット
- Issueテーブルの属性に元となるテーブルの属性の総和を用意する必要がある
- Bugs、FeatureRequestsなどのテーブルを増やす際、影響が大きい
#解決策5. 別々に分ける
そもそもCommentを分けないという方法である。今回だと、BugsテーブルとFeatureRequestsテーブルのようなテーブルの場合ならよいが、
対象のテーブルに関連性が少ない場合、コメントも同じに見えるが、本質的には違うものの場合がある。
その場合はCommentテーブルをBugsCommentテーブルとFeatureRequestsCommentテーブルに分けるという設計が良いと思われる。
#まとめ
テーブル間の関連には、参照元テーブルと参照先テーブルが常にそれぞれ一つしかないことを意識することが大切である
設計にはメリットデメリットがあるのでケースに合わせて選択していきたい