「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編）

　ずっと前から積ん読状態だった「SQLアンチパターン」を読みました。

噂通りとても良い本で、まさに「エンジニアとしての血肉本」と言えます。1, 2年目の頃に読んでおくべきだったなーと少し後悔しています。

SQLアンチパターン

　読んで終わりだと身につかないと思うので、自分なりのチェックポイントを言語化しておこうと思います。長くなってしまったので、章ごとに4つに分けたいと思います。この記事はその第一弾となります。

　※ アンチパターンごとに Bad ポイントが幾つか紹介されていますが、その中の一部（自分が重要だと思ったもの）のみを記載しています。良い設計方法についても同様で、代表的なもののみ書いている部分があります。あくまで自分用のチェックポイント、インデックスにしたくて作成したので、選択が良くないものがあれば申し訳ありません。

データベース論理設計のアンチパターンチェックリスト

　データベース論理設計についてこの記事では整理します。

　論理設計をしくじったアプリケーションをメンテすると、生産性が非常に落ちることは多くのエンジニアの方が経験していると思います。そして、それに気付いたときには、既にデータが大量に追加されており、最適化したくても手が出しにくい状況になっていることも頻繁にあるのではないでしょうか？

　そうならないために、最低限押さえておくべきポイントが8つ紹介されていました。詳細はこのあと記載しますが、チェックすべきポイントのサマリは下記の通りです。（ただし、各項目ごとに「許容してもいい場合」という紹介もあるので、詳しくは本を参照してみてください。）

□ カンマ区切りフォーマットのリストを格納していないか？
□ 3世代以上の階層があるデータ構造を扱う場合に、直接の親子関係のみを参照する形にしていないか？
□ 主キー名を何も考えず `id` にしていないか？
□ 必要な外部キーは設定しているか？
□ 柔軟に「列」を増やすために、Attribute とその Value を表す列を作成してしまっていないか？
□ 複数の異なるテーブルに紐付く可能性があるテーブルを作成する場合に、xx_type といった列を見て紐付く先のテーブルを判断する設計になっていないか？
□ 1対Nで保持したいデータがある場合に、列を row1、row2、、、rowN のように増殖させる設計にしていないか？
□ 時間経過によってテーブルや列を増やすことを前提とした設計になっていないか？

1. ジェイウォーク（信号無視）：カンマ区切りフォーマットのリストを格納していないか？

アンチパターン例

複数個の要素を持つ場合のテーブル設計をすることを考える。例えば、学生とその学生に割り当てられている役割（係）を表現する場合に、下記のようなカンマ区切りフォーマットのリストを格納していないか？

User

user_id(PK)	name	role_ids
1	Alice	1, 3, 6
2	Bob	2, 3

Role

role_id(PK)	name
1	図書係
2	保健係
3	xx係
6	yy係

グッドパターン例

交差テーブルを用いるようにする。

User

user_id(PK)	name
1	Alice
2	Bob

UserRole

user_id(PK)	role_id(PK)
1	1
1	3
1	6
2	2
2	3

Role（変更なし）

role_id(PK)	name
1	図書係
2	保健係
3	xx係
6	yy係

2. ナイーブツリー（素朴な木）：3世代以上の階層があるデータ構造を扱う場合に、直接の親子関係のみを参照する形にしていないか？

アンチパターン例

　

　このような階層構造を表したい場合（例えば、連絡網など）に、下記のように直接の親子関係をのみを表現する形にしてしまうと、幾つか問題が発生する。

Students

student_id(PK)	parent_id	tel
1	NULL	090-xxxx-xxxx
2	1	090-xxxx-xxxx
3	1	080-xxxx-xxxx
4	2	090-xxxx-xxxx
5	2	080-xxxx-xxxx
6	3	090-xxxx-xxxx

例えば「2を消したい」場合、繋ぎ直しのコストが非常に大きくなってしまう。「2以下を全部消したい」場合、まずは子供を辿った上で、子供から削除していくような処理が必要になる。今回のような小さい例であれば問題ないが、階層が大きくなると非常に辛い処理になる。

グッドパターン例

閉包テーブルという 直接の親子関係だけではなく、木全体のパスを格納する方式 を用いる。親子を辿る際に、JOIN が必要になるが、ノードを削除する場合（そのノードが関連するレコードを全て削除すれば良い）も、サブツリーを削除する場合（シンプルに上から順に消していくでいい）もコストが低くメンテナンス性が高い。

Students

student_id(PK)	tel
1	090-xxxx-xxxx
2	090-xxxx-xxxx
3	080-xxxx-xxxx
4	090-xxxx-xxxx
5	080-xxxx-xxxx
6	090-xxxx-xxxx

StudentsTree

parent_id(PK)	child_id(PK)
1	1
1	2
1	3
1	4
1	5
1	6
2	4
2	5
3	6

3. ID リクワイアド（とりあえず ID）：主キー名を何も考えず id にしていないか？

アンチパターン例

• なんでもかんでも無邪気に主キーとして id を定義してしまうパターン。

グッドパターン例

• 分かりやすい名前にする。id ではなく student_id とか。
• 複合主キーにできないか考える。 id(PK) , student_id , role_id ではなく student_id(PK) , role_id(PK)

4. キーレスエントリ（外部キー嫌い）：必要な外部制約は設定しているか？

アンチパターン例

• 外部キーを張らない
  • 結果としてアプリケーションプログラム側で付け焼き刃な対応が必要になり、ぐちゃぐちゃなコードができあがる
  • あくまで付け焼き刃なので、不具合の要因、遠因になる

グッドパターン例

• 外部キーを正しく貼る
• CASCADE 定義もしましょう

5. EAV（エンティティ・アトリビュート・バリュー）：柔軟に「列」を増やすために、Attribute とその Value を表す列を作成してしまっていないか？

　あるテーブルで可変属性（レコードによってある／なしのある属性）を扱いたい場合の設計。例えば、教師（Teacher）と生徒（Student）を管理する User テーブルを作成することを考える。このとき、

• 教師は、名前、電話番号、メールアドレスを管理する
• 生徒は、名前、電話番号、生年月日を管理する

といったように、レコードのタイプによって、入力される列が異なる場合がある。

アンチパターン例

柔軟に仮想「列」を増やすために attr_name と attr_value という列を追加してしまうパターン。

User

user_id(PK)	attr_name(PK)	attr_value
1	name	Alice
1	tel	090-xxxx-xxxx
1	birthday	2005/1/1
2	name	Bob
2	email	bob@xxx.xxx
2	telephone	080-xxxx-xxxx

パット見、柔軟に対応できそうに見えるが、

• 同じ要素でも、格納されている名前が異なる場合がある（例： tel と telephone ）
• 必須にできない
• 何が入るかわからないので適切な型も設定できない

など、負債になる原因をいくつも生み出してしまう。

グッドパターン例

継承の考え方を用いる。

• シングルテーブル継承
  • 名前、電話番号、メールアドレス、生年月日の全てを列として定義する
  • 不要な列（教師の生年月日など）は NULL とする
• 具象テーブル継承
  • 教師テーブルと生徒テーブルを分ける
• クラステーブル継承
  • 共通部分と個別部分に分けて考える
  • 下記が一例

User

user_id(PK)	name	tel
1	Alice	090-xxxx-xxxx
2	Bob	080-xxxx-xxxx

Teacher

user_id(PK)	email
2	bob@xxx.xxx

Student

user_id(PK)	birthday
1	2005/1/1

6. ポリモーフィック関連：複数の異なるテーブルに紐付く可能性があるテーブルを作成する場合に、xx_type などで紐付き先のテーブルを定義する設計になっていないか？

　（いい例か微妙なのですが）例えば、所属している（た）クラスを管理するテーブル ClassInfo を作成することを考える。

アンチパターン例

　ClassInfo のレコードが、教師と生徒のどちらに紐付くかは user_type 列で表すことにし、 user_id には対応する （Teacher｜Student）の主キーの値が入る「想定」という設計とする。

ClassInfo

id(PK)	year	class	user_type	user_id
1	2016	2-2	teacher	2
2	2017	1-1	teacher	2
3	2017	1-1	student	1
4	2018	3-1	teacher	2
5	2018	2-3	student	1

しかし、このパターンは、幾つかの問題をはらんでおり、

• user_id は Teacher テーブル、あるいは Student テーブルを外部キー参照したいができない。
  • テーブルを一意に決めることができないため
  • 「想定」と書いたのはそういう理由から
• JOIN が難しい
  • user_id を見て JOIN するテーブルを決定する形にするので、クエリが複雑になります

といった負債につながってしまう。

グッドパターン例

• 参照を逆にする
  • Teacher や Student から ClassInfo を参照する形にする。
  • 交差テーブルを用意し、Teacher - ClassInfo 間、Student - ClassInfo の多対多を表現する。
  • 交差テーブルにおいて ClassInfo 側の主キーに UNIQUE 制約を貼ることで、同一の ClassInfo が複数の（Teacher｜Student）に紐付かないようにする。
    • ただし、この対応をしても、ひとつの ClassInfo がある Teacher とある Student に同時に紐付いてしまう可能性はある。
• これらのテーブルを一つのテーブルとして見たい場合は UNION あるいは COALESCE 関数を用いる。
• 1. のように Teacher テーブルと Student テーブルの基底クラスを作れる場合は、【基底クラスに対して】外部参照するようにする。

7. マルチカラムアトリビュート（複数列属性）：1対Nで保持したいデータがある場合に、列を row1、row2、、、rowN のように増殖させる設計にしていないか？

　生徒が興味を持っている分野（宇宙、芸術、などなど）を登録できるように Student テーブルを拡張することを考える。

アンチパターン例

　エイヤで一定数の列を追加してしまうパターン。

Student

student_id(PK)	Name	fav1	fav2	fav3
1	Alice	Design	NULL	NULL
2	Bob	NULL	NULL	NULL
3	Charlie	Dance	Design	Art

イケてない理由として、

• Design に興味がある生徒 を探すクエリが複雑になります。
• UPDATE, DELETE も難解です。
• fav1 と fav2 に同じ値が入ってしまう可能性があります。
• そして何より3つまでしか、興味のある分野を登録できません。

などが挙げられる。

グッドパターン例

　下記のように従属テーブルを作成するのが、クエリも簡単に書け、拡張性も高い設計になる。

Student

student_id(PK)	Name
1	Alice
2	Bob
3	Charlie

Favorite

student_id(PK)	fav(PK)
1	Design
3	Dance
3	Design
3	Art

8. メタデータトリプル（メタデータ大増殖）：時間経過によってテーブルや列を増やすことを前提とした設計になっていないか？

　学校において「購入した備品を年ごとに効率的に参照したい」という要件があったとする。

アンチパターン例

　要件を満たすために、【年ごとに】購入した備品を格納するテーブルを作る（例：Equipment_2017）としてしまうのは、様々な問題に繋がる。

• 年が変わるたびにテーブルを増やし、アプリケーションに修正を加える必要が出てくる。
• 誤った年のテーブルにレコードが追加された場合に、それを移す際に一手間かかる。
• 異なるテーブル間で UNIQUE 性を担保するのが難しく、同じ備品が複数のテーブルに挿入されることをDB側では防ぐことができない。

（同様の問題は「テーブル」ではなく「列」でも発生する。）

グッドパターン例

• 参照性能を高めることだけ考えるのであれば、同一のテーブルで管理しつつも、年を表す列でパーティショニングする方法を用いる。
• また、保守性を向上させるには student_id と year が主キーの従属テーブルを新たに定義する。

まとめ

　論理設計についてまとめました。別の記事で、

• データベース物理設計のアンチパターン
• クエリのアンチパターン
• アプリケーション開発のアンチパターン

についてもまとめていきたいと思います。また、著者の方のお焚きあげ（今となってはこのパターンは少し違うよね）情報もあるそうなので、こちらについても最後にまとめられれば、と考えています。

　この記事は「私の理解はこうです」という内容になっています。気になる項目が見つかった方は、ぜひ本を読んでみてください。とても勉強になる良書です。集中して読めば5時間程度で読み終わる内容だと思います。

SQLアンチパターン