はじめに
こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅰ部のDB論理設計のアンチパターンについて記載します。
1章.ジェイウォーク（信号無視）
アンチパターン
１つの列にカンマ(,)やスラッシュ(/)を使用して、複数の値を格納すること。
(例)
User
| user_id | name | role_id |
|---|---|---|
| 1 | TARO | 1,3,4 |
| 2 | JIRO | 2,3 |
Role
| role_id | role |
|---|---|
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
| 4 | DDD |
デメリット
①検索や集約処理を行うのに手間がかかる
②カンマ(,)やスラッシュ(/)を使用することによって数値型ではなく文字列型で定義する必要があるため、不正なデータを許容してしまう
③リストの長さの上限しかデータを登録できない
解決策：交差テーブルを作成する
UserとRoleの２つのテーブルをつなぐ、交差テーブルを作成する。
(例)
User
| user_id | name |
|---|---|
| 1 | TARO |
| 2 | JIRO |
UserRole
| user_id | role_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 1 | 4 |
| 2 | 2 |
| 2 | 3 |
Role
| role_id | role |
|---|---|
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
| 4 | DDD |
2章.ナイーブツリー（素朴な木）
アンチパターン
例のようにparent_idを持たせて、直近の親のみを参照するようにするツリー構造で設計する。
常に親のみに依存するこの関係を「隣接リスト」と呼ばれています。
（例）組織の関係を表すテーブル
Organization
| id | parent_id | name |
|---|---|---|
| 1 | null | 会社 |
| 2 | 1 | 東日本 |
| 3 | 1 | 西日本 |
| 4 | 2 | 東京本社 |
| 5 | 3 | 大阪支社 |
| 6 | 4 | A事業部 |
| 7 | 4 | B事業部 |
デメリット
・ツリーの全体やサブツリーの取得が難しい
・非葉ノードを削除する際に子ノードから削除・編集する必要がある
解決策：代替ツリーモデルを使用する
いくつか代替ツリーモデルはありますが、一番のおすすめは「閉包テーブルモデル」です。
「閉包テーブルモデル」とは別テーブル（Relationテーブル）でノード同士の関係性を管理するようにする。
（例）
Organization
| id | name |
|---|---|
| 1 | 会社 |
| 2 | 東日本 |
| 3 | 西日本 |
| 4 | 東京本社 |
| 5 | 大阪支社 |
| 6 | A事業部 |
| 7 | B事業部 |
Relation
| parent_id | child_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 2 | 2 |
| 2 | 4 |
| 2 | 6 |
| 2 | 7 |
| 3 | 3 |
| 3 | 5 |
| 4 | 4 |
| 4 | 6 |
| 4 | 7 |
| 6 | 6 |
| 7 | 7 |
3章.IDリクワイアド（とりあえずID）
主キー名を何も考えず id にしていないでしょうか？
なんでもかんでも無邪気に主キーとしてidを定義してしまうアンチパターン
(例)
ArticleTag
| id | article_id | tag_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
Article
| id | name |
|---|---|
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
デメリット
・キーの意味が分かりにくくなる
・重複行を許可してしまう(主キーの意味をなさない)
例のArticleTagにarticle_id=2,tag_id=3の値を誤ったクエリを実行してしまっても、登録できてしまう
解決策：状況に応じて適切に調整する
・良い設計のためには、規約に縛られて柔軟性を欠いてしまわないことが大切
・わかりやすい列名にする
主キーの名前は、主キーが識別する対象のエンティティを表すものにすべき
4章.キーレスエントリ（外部キー嫌い）
名前の通り外部キーを張らないアンチパターン
デメリット
・完璧なコードを前提にしている
外部キー制約を設定しなかった場合、アプリケーションプログラム側で参照整合性を保証するためのコードを書く必要がある
・ミスを調べなければならない
ミスを発見するために、定期的にデータのチェックを行うクエリを実行する必要がある
解決策：外部キー制約を宣言する
・テーブルを作成する際に外部キーを正しく設定する
・カスケード更新を行う
まとめ
今回は第Ⅰ部のDB論理設計のアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。
以上、小川でした。