Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

【SQL】アンチパターンや使えそうなパターンをまとめる ~論理設計~

SQLアンチパターン今更ながら読みました。
もっと早く読んでおくべきだった。ということでいくつかの記事に分けてアウトプットしていきたいと思います。

主に下記記事などを参考にしました。
SQLアンチパターン勉強会 - Qiita
「SQLアンチパターン」を避けるためのチェックリスト①(DB論理設計編) - log4ketancho

チェックリスト

  • 一つのカラムにカンマ区切りで値を入れていない?
  • 階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない?
  • 主キー名を id という名前にしていない?
  • 必要なテーブルに外部キー設定してる?
  • 複数の異なるテーブルを紐づける場合は、_type (ポリモーフィック関連)で管理していないか?
  • テーブルや列を増やす前提の設計になっていないか?

一つのカラムにカンマ区切りで値を入れていない?

あるテーブルに comment_ids みたいなカラムがあることを想定します。
そして1,2,3 のように string でカンマ区切りの値が入る場合が今回のケースになります。

解決策

中間(交差)テーブルを使いましょう。

階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない?

よくあるパターン

隣接リスト

階層構造が1つや2つの場合は、非常にシンプルでSQLも面倒にはなりません。

解決策

閉包テーブル

下記の記事を参考にしました!
閉包テーブル(Closure Table)を試してみた - enomotodev’s blog
閉包テーブル - Qiita
閉包テーブル (Closure Table) に順序を持たせソートした結果を取得する - Qiita

  • ancestor(先祖)descendant(子孫) をもつテーブルを別で作成
  • 上記2つは参照元の id を指すように設計

具体的には、下記の通りです。

ancestor descendant
1 1
1 2
1 3
2 2
2 3
3 3

image.png

主キー名を id という名前にしていない?

id という名前にしているテーブルをよく見ますが、この id に明確な意味はありません。
account_iduser_id などのように明確にしましょう。

必要なテーブルに外部キー設定してる?

下記記事のコメントでも書かれている通り、なんでもかんでも外部キーを貼ることこそがアンチパターンなのでしょう。
デメリットのところが非常にわかりやすかったので、そのまま引用させていただきます:bow_tone1:

外部キーを貼るデメリット
- シャーディングや別DBで利用できない
- ON UPDATE CASCADE 制約を詳しく無い人がさわって想定外のレコードが変更される可能性がある
- 上記、SELECT 文走らせまくるとか、ER図が無いと辛い
- そもそも DELETE が推奨されておらず論理削除がメインなので問題がおきづらい
- テストデータの作成にコストがかかる
- フレームワークで外部機キー制約っぽいものを制約しているものがある
- 行を挿入する前に、親の行の存在確認をしないといけない。=> コード量が増える
- 制約があっても確認するはず
- insert の失敗時に制約エラーの内容からハンドリングするほうがコード量増えそう
第四章 キーレスエントリ(外部キー嫌い) - Qiita コメント箇所より引用

外部キーの概要と制約を使うことのメリット・デメリット - Qiita

外部キーの制約について

MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは? - Qiita
外部キー制約について - Qiita

CASCADE

CASCADEを指定すると,被参照表の主キーに変更があった場合,外部キーも同じように変更されます。
参照制約の定義

ON DELETE SET NULL・・・参照先deleteすると参照元がnullにする
ON UPDATE CASCADE・・・

下記記事でも検証してくれていますが、ON UPDATE CASCADE を指定すると、参照先をupdateした際、参照元も同じupdateがされます。
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは? - Qiita

複数の異なるテーブルを紐づける場合は、_type (ポリモーフィック関連)で管理していないか?

第6章のポリモーフィック関連の箇所です。

ポリモーフィック関連の問題点

SQL を実行するときに、id だけを取得するだけでなく、type の値も同時に確認する必要が出てきます。
なので、typeが2つ、3つ存在するのであれば、そのうち1つしか外部結合ができなくなってしまいます。

解決策

  • 中間テーブルの作成
  • 共通の親テーブルの作成

SQLアンチパターンを読んで (ポリモーフィック関連について) | MotiMoti++
Railsのポリモーフィック関連とはなんなのか - Qiita

MySQL

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用

テーブルや列を増やす前提の設計になっていないか?

8章のメタデータトリブルの箇所です。
8章 Metadata Tribble(メタデータ大増殖) - Qiita

年ごとに Bugs_2018 Bugs_2019 Bugs_2020 と増やしていく設計を考えている場合は、水平パーティショニング、垂直パーティショニングを検討しましょう!!
データパーティショニングで巨大DBも楽々管理

sanoyo
自衛隊からソフトウェアエンジニア
https://note.com/yokosano
engineerlife
技術力をベースに人生を謳歌する人たちのコミュニティです。
https://community.camp-fire.jp/projects/view/280040
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away