ぽんぽんぺいんでつらたんなので、おしごとおやすみしまーす

🗣: バイクに乗ったり、ものづくりしたり、ひたすら寝たり。

SQLアンチパターンを読んだので、少しずつまとめてみる。

こんばんは！
最近、我が家の豆苗のテンションが低めです

しなだれる豆苗

▼ 『寒いんじゃ…』

私の食料用に育成しているこの人ですが、日陰+常に気温は10℃前後という過酷な環境でも育ってくれています。
もともと温室育ちなはずなのに… 私もどんな辛いプロジェクトに放り込まれても、根を張って耐え抜く強さを持ち続けていたいです笑


さて！寒さにめげずにアンチパターンの続きをまとめていきます！

前回はSQLアンチパターンを読んで、論理設計編をまとめるという内容で展開しました。
残りが消化できていないので、こちらも見ていきましょう🐕


1. ジェイウォーク（信号無視）

2. ナイーブツリー（素朴な木）

3. IDリクワイアド
4. キーレスエントリ

5. EAV（エンティティアトリビュートバリュー）

6. ポリモーフィック関連

今回は太字になっているIDリクワイアド, キーレスエントリについて振り返りをしていきます！


IDリクワイアド

前置き長いので、内容は本旨からどうぞ。

私は最近までRuby on Railsというフレームワークを使う現場で仕事をしていました。
RailsはORマッパーを採用していて、テーブルのデータをひとつのモデルとみなし、必要なデータの設計を高速に実施できます。
SQLで抽出したデータはActiveRecord::Relationというクラスでサポートされており、データの整形にキャスト(型変換)を意識せず素早く使うことができます。
経験や学びが浅く、エンジニアとして自信を無くしかけていた私にとって、このフレームワークの存在は本当にありがたかったです。昔では怖くて触れることができなかったロジックで色んな機能を実装することが出来、人の役に立つことが出来たので…。


その前はお客さん先でJavaとOracleを使ってDB周りのことをしていました。
A5M2というツールで眺めた証券系システムを成すテーブル群はゆうに300を超えていて、当時1:nもよくわからなかった私は衝撃を受けました。

研修で作ったアプリについても、「テーブルなんて実行する回数少なければひとつだけあればいいじゃない」みたいなぬるい感想を持った素人にとっては、あの光景はなかなかに破壊力があるものです。マジで過呼吸になるかと思った。
あ、A5M2はSQLパーな私にとって強力な調査の相棒となったので、WindowsOS使っている人に広めたい気持ちがあります。

マーケティングの人がSQLと仲良くなるきっかけとして。開発者と仲良くなるきっかけとして。
こちらもまた使ってみてまとめ記事あげたいなあ。

本旨

こちらがIDリクワイアドの内容の本旨になります。


私も研修の際に覚えたことですが、
各テーブルにおわすidはprimary key(主キー)として、そのデータが一意であることを示し、かつ他のテーブルのforeign key(外部キー)から参照され、テーブルの関連性を持つことが出来ます。

しかし、一意である=ダブらないといっても、本当にダブらないのかはちょっと疑問なところではあります。
下記は記事の詳細データを扱うテーブルです。

id reference_url article_id tag_id
1 423429 2 3
2 423429 2 3
3 524520 3 6

…

一度article_id=2の記事を消したものの、関連テーブルのデータは消えていませんでした。
このテーブルから記事に紐つけられている引用ページの数を知りたくて、以下のSQLを発行します。

select * article_image_mapping_id, count(*) as uniq_image_id from article_detail where tag_id = 3

すると、idが1と2のreference_urlが取れました。
idは一意であるものの、挿入されるデータは別物とは限りません。
この場合はid以外のカラムにユニーク制約(一意制約)をかけるべきだったのですが、ここで各テーブルのidは必要なのか？という疑問が浮かび上がります。

id列があって困るパターン

①単純に紛らわしい
articleテーブルに同名のid列があったらどうでしょう。
同じidのユニークな列が出来た際に、上のような出来事は避けられるものの、idでもarticle_idでもレコードは取れてしまいます。


②重複行を許可してしまう
上の例ではレコードを挿入する際に、テーブルのidはserial primary keyを許可されているものの、
他の2カラムについては重複を許可してしまっています。
新たに同じtag_id, article_idでレコードが更新されてしまうのです。


③キーの意味がプログラムにとってわかりづらい

select b.id, a.id
from tags b
inner join articles a on b.assign_to = a.id
where b.name = 'coffee';

上のクエリは記事とタグのテーブルを結合し、コーヒーのタグがついた記事を抽出するものです。
データ名で内容を識別するとき、article_id, tag_idのように識別されない為に、列で内容が上書きされてしまうケースもあるようです。
Railsを使用していれば起こりにくい問題ですが、一から設計した時には起こりうる問題です。


④using句の使いどころが減ってしまう
もしもidカラムにテーブル名を表す情報が付加されていた場合、以下のクエリを簡単に書き直すことが出来ます。

select * from tags as a
inner join articles as b
on a.id = b.tag_id;

select * from tags as a
inner join articles as using (tag_id);

using句便利！idカラムを実装するよりも、tablename_idの命名規則に沿うと楽そうですね♪


参考

idリクワイアドを適用してもいい場合

行で重複しても良い
クエリで個別の行を参照しない
外部キー参照をサポートしない

擬似キーをサポートしているデータベース
DB種類 機能名 参考url
mysql AUTO_INCREMENT MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.6.9 AUTO_INCREMENT の使用
mysql, postgres SERIAL https://www.postgresql.jp/document/7.3/user/datatype.html
sqlite ROWID Rowid Tables
Oracle SEQUENCE CREATE SEQUENCE


mysql_insert_id()は最後に生成されたAUTO_INCREMENTの値を取得してくれるそう。
SQLにも関数がある、これ応用できたら便利やろな…。


キーレスエントリ（外部キー嫌い）

冒頭のケースで語られるのは、外部キー参照のないDBで設計をした為に、データ重複や孤児（親データが削除された後も置き去りとなった関連データ）の扱いについてです。
不整合なデータを定期的に検出し、結果をメールで通知させる設計を追加したものの、テーブルの数とデータそのものが増えてきてクエリの実行に時間がかかるようになってしまったのでした。
本来であれば、ユーザーが無効なデータを入力した時にエラーを返せる設計にすればよかったとありますが、
では無効なデータとはどんなものを指すのでしょうか？

外部キーを参照したくないパターン
  • テーブルの設計がしょっちゅう更新される
  • データの更新が外部参照と衝突する
  • 本テーブルのデータを更新する為に外部テーブルも更新しなければならない-
  • クエリを調べるのに時間を割きたくない(笑)
  • 外部キー参照を実装していないDBを使用している(本文ではMyIsam)
完璧なクエリを発行してデータを保持していかなければならない

1:nのarticle, tagがあった場合に、tagを更新する際は親であるarticleが存在するかselectで確認する必要がある。
1:nのarticle, commentがあった場合に、articleを削除する際は子のcommentが存在していないか、selectで確認する必要がある。
（さらに、削除作業を行う際には新たなcommentが作成されているかもしれないことを念頭に置くこと。）

孤児データの検索が必要になる

select a.article_id, t.tag_name
from articles left outer join tags t
on a.tag_id = t.tag_id
where t.tag_id is null;

これを毎度発行して、定期的にデータの管理をする事を考えただけでも、気が遠くなりそうですよね…。

削除、更新の際は親のカラムを更新してから子のカラムを更新していかなければならないのです。。

ソリューション

外部キー制約を宣言しよう


複数テーブルの更新をサポートするカスケード更新

create table Bugs (
reported_by BIGINT UNSIGNED NOT NULL,
-- 正の数のみ, NULL許容しない

status VARCHAR(20) NOT NULL DEFAULT 'NEW',
-- デフォルト値はNEWで20文字までの制約あり, NULL許容しない

FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
ON UPDATE CASCADE
ON DELETE RESTIRCT,
-- カスケード処理を定義する, Bugsテーブルを参照しているaccount_idは削除時にエラーを返す

FOREIGN KEY (status) REFERENCES BugStatus(status)
ON UPDATE CASCADE
ON DELETE RESTIRCT
-- statusをNULLにした場合には、set defaultが作用してNEWが設定されるようになる 孤児の心配はない
);

テーブル作成時にカスケード更新を設定すれば、参照している子の行も更新が反映される…おったまげたぜ…

これで削除、更新前にselectを投げる必要がない
複数のテーブルの更新を気にして排他的ロックをかける必要がない
孤児を検索するスクリプトを書く必要がない

👏👏👏


ご参考

www.dbonline.jp
qiita.com



寒い日には美味しいラーメンが食べたい！

という事で、ぼっちラーメン散策して参りました👟
地元にあるこのラーメン屋さんですが、皿の上に乗っているものが全て美味しかったです。
特製煮干しラーメン(塩)
特製煮干しラーメンの塩を頼んできました。
スープつやっつやですよね！？一度口をつけたら飲み干すまで止まらないですよ…😈
体が欲しがる栄養スープって感じです！
体に優しいお味で、チャーシューの代わりに半ナマのローストビーフとチキンが乗っていました🐃🐓
優しい脂が、噛み締めるたびに口の中を満たします🤤
味玉はとろりとした半熟、海苔も噛み締めると濃いめの味が広がって、それでもスープを邪魔しない程度の主張。
麺は自家製みたいです。もくもくと噛み締めて、私は好きです。

特製とあってお値段少し張りますが、通常メニューのラーメンは880~からいただけるので、今度はこっちも食べにいきます！
家系よりもあっさりした方が好きな方は是非！


関連ランキング：ラーメン | 高円寺駅、東高円寺駅、新高円寺駅

http://tabelog.com/btb/0bbbdcd7680f03ca3988b4c586a62e53ea73e731111cc24a272de3e1bad7eba3/