カテゴリー: データベース

SQLアンチパターン

やろうと思っていた個人開発がどうにも滞ったまま何ヶ月も経ってしまい、でも久々に記事だけでも更新しなきゃなということで今回は技術書の紹介。
今回紹介するのはSQLアンチパターンという本。業務でDB設計をやる機会が増えて、設計に関して気をつけなきゃいけないことを把握しようと思って買ってみた。
本全体の感想としては、DB設計に関わる人なら一度は読んでおくべき本だなという印象。テーブル定義や設計などで避けるべきことが網羅されている感じ。
紹介されているパターンが25もあるので、今回は特にやりがちなパターンを3つほど紹介してみる。

1. 3章 : IDリクワイアド（とりあえずID）
新しいテーブルを作る時、何でもかんでもとりあえずIDカラムを入れるというのをやりがちな人は結構多いのではないかと（自分含む）。
例えば、学生ごとに受講する科目を定義するstudent_subjectというテーブルを考えてみる（studentテーブルとsubjectテーブルは別途定義されているとする）。
id student_id subject_id
1 1 1
2 1 2
3 2 3
4 3 2
また、テーブルを生成する際にUNIQUE KEY (student_id, subject_id)でstudent_idとsubject_idが一意になるようにする。
そうなると、”id”カラムってなんのために使うんだという話になってくるので、必要でないカラムをあえて入れておく必要はないのではということになる。
とはいえ、このテーブルに成績を表すgradeというカラムを追加して、さらに別のテーブルと連結させる必要が出てきたりした場合はidカラムに相当するものが必要になるので、ケースバイケースではあるが（もっとも、この場合はテーブル名をもっと別の適切な名前にすべきだが）。
要約すると、「このidカラム、本当に必要ですか？」ということか。

2. 10章: サーティーワンフレーバー（31のフレーバー）
あるカラムに入る文字列の値をいくつかに限定したい場合、MySQLではENUM型で定義することがある。他のRDBMSはあまり使ったこと長いのでわからないが、CHECK制約を入れて入る値を限定するやり方があるらしい。
例えば、従業員が所属する支社が”TOKYO”、”OSAKA”、”NAGOYA”、”SAPPORO”の4つのいずれかという場合、officeカラムをENUM(‘TOKYO’,’OSAKA’,’NAGOYA’,’SAPPORO’)という風に定義していたとする。
employeeテーブル
employee_id name        office
1                      Suzuki       TOKYO
2                      Sato            OSAKA
3                     Tanaka       NAGOYA
4                     Yamamoto TOKYO
5                     Yoshida      SAPPORO
ところがある日、那覇支社が新たに追加された場合ENUM(‘TOKYO’, ‘OSAKA’,’NAGOYA’,’SAPPORO’,’NAHA’)に定義を変更するのは結構面倒臭い。

このような状況を避けるには、以下のようにすればいい。
officeテーブルを新たに定義し、employeeテーブルにoffice_idを持たせる。
employeeテーブル(修正版)
employee_id name        office_id
1                     Suzuki     1
2 Sato 2
3 Tanaka 3
4 Yamamoto 1
5 Yoshida 4
officeテーブル（追加）
office_id office_name
1 TOKYO
2 OSAKA
3 NAGOYA
4 SAPPORO
5 NAHA

ただし、入る値が”LEFT”、”RIGHT”など絶対に追加されることが無いと断言できる場合はENUM型を使ってもいいと思う。なので、ENUM型での定義を考える場合は新たな値が追加される可能性をまず検討すべきかと。

3. 18章 : インプリシットカラム（暗黙の列）
プログラム内でデータベースから検索する時、’SELECT * from （テーブル名） where （検索条件）’に相当するような検索クエリを呼び出すような形で結果を取得するようなことがあると思う。
あるいは、’INSERT INTO （テーブル名） （カラム） VALUES （値）’でレコードを追加するときにデフォルト値が設定されているカラムを省略したりすることもあるはず。
けど、実はこれらには落とし穴があったりする。例えば、カラム数が10あったとして、取得した値をカラムごとに配列に入れていた場合、誰かが不要なカラムを1つ削除したとすると、配列の要素数が9しかないのに10番目の要素にアクセスしたらエラーになってしまう！
それ以外にも不要なデータまでも持ってきていることでクエリが必要以上に重くなってしまうこともあり得る。
なので、多少面倒でも必要なカラムのみを明示的に指定した方が後々問題が発生するのを避けることができる。

終わりに
25のアンチパターンの中で、自分も気をつけていないとついやりがちなパターンを3つほど上げてみたけど、逆に「これは普通やらないだろ」ってのもちらほらあったなあ。
例えば、1章の「ジェイウォーク（信号無視）」は1つのカラムに無理やり複数の値をカンマ区切りで入れるのは少なくとも自分は気持ち悪く感じるし、20章の「SQLインジェクション」は最近のフレームワークだとほぼ対策がされているのではないかと（それでも、ユーザー向けのシステムの場合はテストの時にフォームにSQL文を入れて変なことにならないかの確認はやるけど）。
それでも、こういう「べからず集」は意識して開発していけば後々のトラブルの元が減らせることになるので、設計していて違和感を感じたら本を見返して問題点と対策を把握しておくのがいいだろうな。

半年近く滞っていた個人開発の方も、どこかのタイミングで再開したいなあ。

令和2年秋　データベーススペシャリスト受験記

10月18日日曜日、4月に行われるはずだったのが延期になっていたデータベーススペシャリストの試験を受けて来た。会場は沖縄県立那覇工業高校。

午前1はこの時のおかげで免除で、午前2から。
自分の受けた教室は受験人数は20人より少し少ないくらいか。

午前2
問題はこちら。いきなり問1にCAP定理が出て、焦る。昨年CAP定理が出たから今年は出ないだろうと思っていたけど、違う切り口で出てきた。これはやはりCAP定理についてちゃんと理解しておく必要があるということか。
問2も新作問題のNoSQLについてだが、参考書で別のノードに時間差で反映されるのがあった気がして、イにマークする。
問3からは過去問通りの問題が出て来て、ペースが上がってくる。
最後の方に電気泳動型電子ペーパーとか知らない問題も出て来たが、終わってみるとまあ大丈夫かなという感触。
解答はこちら。自己採点で18/25。一安心。

午後1
問題はこちら。
予定では問1＋問2か問3のどちらかなので、まずは問1を解いていく。
問題の意味はまあわかるのだが、45分（90分の半分）でやるにはどうにも量が多い。最後の方は結構慌てて書いたので、リレーションをいくつか書き損ねてしまう。
問1が終わったところで47〜8分くらい経過。問2と問3をざっと見て、問2の方が記述の量が少なそうなので問2に行くが、これが失敗だった。
設問1と設問2の最初はまあわかったのだが、そこから先がさっぱりわからず、これはまずいと思い問2を退散し、問3に。10分くらいタイムロスしてしまったので30分で問3を解かなければならない。
しかし、問3はやってみると３問の中では一番簡単で、販売額がNULLになるケースと一番最後のテーブル構造以外はそんなに悩まず解くことができた。
試験後のツイッターとか5chとかを見た感じだと、やはりみんな問1が時間かかり過ぎという声が多かった。

午後2
問題はこちら。
こちらは物理設計が苦手なのでお絵かきの問2と最初から決めており、問1には目もくれず問2にかかる。
こちらは時間的には午前1よりは十分にあるのだが、ルート巡回とかがいまいちイメージしきれず、リレーションの線を引く箇所も結構迷うところが多く、なかなかしんどかった。
特に設問2(2)の6箇所中5箇所が同じ回答になってしまい、本当かよと思いつつ、他に回答が思い浮かばず6箇所中5箇所が同じ回答のまま進める。
どうにか回答を埋めきったけど、全然答えに自信が持てないorz
去年のパン屋の問題がかなり難しかった反動で易化を期待したけど、残念ながらそうはならなかったか。

その他
試験勉強はこの本を主に使いました。
午前2は何回か過去問をやって大丈夫そうだったので、午後にやや重きを置いて勉強しました。
勉強の仕方としては、問題と解答をよく読んで解き方を理解して行くというものでした。ただ、今になって思えばもう少し手を動かせばよかったかなと。

合格発表は12月25日のクリスマス。去年のアーキテクトも含めて高度試験は3回午後2で沈没しているので、そろそろ受かりたいけど、どうなるか。。。

合格発表は12月25日のクリスマス。去年のアーキテクトも含めて高度試験は3回午後2で沈没しているので、そろそろ受かりたいけど、どうなるか。。。

2020/12/25追記

何とか合格しました！！
午前１:免除、午前２:７２点、午後１:６９点、午後２:62点でした。
午後2は本当ギリギリだったので（配点がわからないので何とも言えないけど）、リレーションをあと数本引き忘れていたら落ちていたと思うと、本当ゾッとする（汗）
勝因としては、最後まで諦めずに粘ったことと、午後1で手が動かなくなった時点で問2を退却して問3に切り替えたことか。
とは言え、現在業務でテーブル設計とかやっているけど、実務の点ではまだまだだと痛感するので、これから勉強を積んで本当の意味でデータベースのプロになれるよう研鑽を積んでいきたい。

次は何を受けるかを考えると、ネットワーク（に限らずインフラ全般）は苦手だし、エンベデッドは業務と結びつかないし、セキュリティ改め情報処理安全確保支援士は資格を維持するのにかなりお金がかかるみたいなので、長文を書くのは苦手だけど、論文系の資格（システムアーキテクトorプロジェクトマネージャ）を狙うしかないなあ。