SQLアンチパターンその4 キーレスエントリ(外部キー嫌い)
SQLアンチパターンは全部で25章あるので、毎日書いても1ヶ月かかる内容だった。 なので、今後はそんなにがっつり書かずにポイントだけ。
キーレスエントリ(外部キー嫌い)とは
外部キー制約を使わない
いつ起こるのか
テーブル間で参照をするとき
何をしてはいけないのか(アンチパターン)
外部キー制約を使わない
何故ダメなのか
外部キー制約は使われず、でも外部キーとして参照されている列がある場合、アプリケーションコード側で完璧にお守りをしてあげないと迷子になる行が出てくる。アプリが完全であることを前提にしてはいけない。
いい解決策は
外部キー制約使え
参照関係をそもそも壊さない仕組みだから、外部キー制約使うべき。
マスタになるキーを更新する際、ON DELETE CASCADE, ON UPDATE CASCADEとか使えば、外部キーとして参照しているテーブル側もちゃんと更新なり削除がかかる。 知らなかった。これ覚えておくべき。
外部キー使う場合、たしかにオーバーヘッドになり得るけれど、アプリケーションコード側で事前チェックとか気にする必要なくなるし、DB側でできる部分は無理にアプリケーションに持ってくること無い。
ファイル内の重複行削除
ファイル内の重複している行を削除する。
追記: kotyさんに教わった部分を入れてみて、FileUtility2.csとして投稿。かなりスッキリ
Enumerable.Distinct(TSource) メソッド (IEnumerable(TSource)) (System.Linq)
SQLアンチパターンその3 IDリクワイアド(とりあえずID)
IDリクワイアド(とりあえずID)とは
名前のまま、「とりあえずID列作っておこうぜ」ということ
いつ起こるのか
主キーを作るルールがあるとき
何をしてはいけないのか(アンチパターン)
どんなテーブルに対しても「ID」という名前の列(サロゲートキー)を作ること
例:店で販売する商品
CREATE TABLE Goods( id SERIAL PRIMARY KEY, product_id BIGINT, name NVARCHAR(20), ); (id, product_id, name) ---------------------------------- (1, 1014, "レッドブル エナジードリンク 缶185ml") (2, 1237, "ライジン 缶185ml") (3, 1935, "モンスターカオス 缶355ml") (4, 2084, "リゲイン エナジードリンク 缶190ml") (5, 2365, "バーン エナジードリンク 缶250ml") ...
何故ダメなのか
冗長な列ができてしまう
まさに上の例とかそうだけど、他の列を自然キーとして扱えるのならば、あえてid列を追加する必要は無い。
いい解決策は
「id」以外の名前をつける
「商品コード」とか、「user_id」みたいなわかりやすい名前を付けることを心がけること。交差テーブル作った時に、交差する両方のテーブルの主キーが「id」だと交差テーブルが扱いにくくなる。
自然キーで使えそうなものって、JANコードとかかなーと思ったんですが、再利用される可能性あるし、見極め難しそう。サロゲートキー使うでも良さそうだし、ケースバイケースだろうなと。
SQLアンチパターンその2 ナイーブツリー(素朴な木)
ナイーブツリー(素朴な木)とは
「素朴な」という訳がぴんとこなかった… 「単純、未熟な」(思慮から生まれる)ツリー構造。
いつ起こるのか
表の構造で、ツリー(階層)構造を格納しようとするとき
何をしてはいけないのか(アンチパターン)
階層として「親への参照だけ」を持つこと。
例:会社の組織図を表すテーブルを作る
CREATE TABLE Departments( department_id SERIAL PRIMARY KEY, parent_id INT, name NVARCHAR(20), FOREIGN KEY (parnt_id) REFERENCES Departments(department_id) ); (department_id, parent_id, name) ---------------------------------- (1, NULL, "本社") (2, 1, "A本部") (3, 1, "B本部") (4, 2, "A営業部") (5, 2, "A開発部") (6, 3, "B営業部") ...
何故ダメなのか
階層の探査をするクエリが非効率的。1階層下の子ノードの集合を取るのには、以下の様なクエリが必要。
SELECT d1.*, d2.* FROM Departments d1 LEFT OUTER JOIN Departments d2 ON d2.parent_id = d1.department.id;
これがn階層になると、d1., d2., ... dn.* となり、もちろん階層分だけJOINすることになる。
中間のノードの削除を行う際に、親子関係を適切に組み替えるためのクエリも複雑になる。
いい解決策は
閉包テーブル(Closure Table)を作る
あるノードを取り上げた時、そのノードのすべての祖先と自分に対しての組み合わせを、行として格納する。 先ほど挙げたDepartmentsテーブルに対して考えると、以下のようになる。
CREATE TABLE TreePaths( ancestor INT NOT NULL, descendant INT NOT NULL, PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCE Departments(department_id), FOREIGN KEY (descendant) REFERENCE Departments(department_id) ); (ancestor, descendant) -------------------------- (1, 1) (1, 2) (1, 3) (2, 2) (2, 4) (2, 5) (3, 3) (3, 6) (4, 4) (5, 5) (6, 6)
閉包テーブルを作ると、A本部の部署を取得する場合、以下のようなクエリでいい。
SELECT d.* FROM Departments d INNER JOIN TreePaths As t ON d.department_id = t.ancestor WHERE t.descendant=3
中間のノードの削除を行う場合でも、全ての祖先への参照が格納されているため、消したいノードのdepartment_idの行だけ消せばいい。
SQLアンチパターンその1 ジェイウォーク(信号無視)
ジェイウォーク(信号無視)とは
(例えば)カンマ区切りで複数の値を持つ列を作ること。
例:アカウントのテーブル。このアカウントは複数のグループに所属できる。
(account_id, name, group_id) --------------------------- (1, "hoge", "1,2,36,103") (2, "moga", "2,4")
なぜアンチパターンなのか
区切り文字を気にしてアプリを実装する必要がある
区切り文字をカンマにした場合、区切り文字以外にカンマが出てくる可能性はゼロではない。(上の例だとgroup_idは数値だから大丈夫に見えるけど。)じゃあどんな区切り文字だったら大丈夫なのかは分からない。
列のデータ型が文字列になってしまう
group_idのデータ型がIntegerだとしたら130は入っても、"piyo"なんて値は入るはず無い(エラーする)けど、「カンマ区切りで記された数値」を保存する列(varchar)だった場合、"1,2,4"と、"1,2,piyo"はどちらもデータとしては間違っているが、データ型としては正しい。
列の最大サイズを決められない
いったい何個のgroup_idがカンマ区切りで入ってくるのか予想はできない。どれくらいのサイズを取っておけば安全なのか誰もわからない。
データの修正が複雑
文字列解析してgroup_idを追加・変更・削除するのはとても面倒。
どうするべきか
第一正規化して別テーブル(交差テーブル)を作る。
(1, "hoge", "1,2,36,103") (2, "moga", "2,4") ↓ (1, "hoge") (2, "moga") (1, 1) (1, 2) (1, 36) (1, 103) (2, 2) (2, 4)
文字列をsplitする必要もないし、データ型がおかしいデータも入らない。サイズに関しても予想がつく範囲内におさまるし、データの修正や削除もSQL文1行で出来る。
アンチパターンに限らない話だと思いますが、「アプリ側できちんと制御してやれば変なデータは入らない。」というのは「安全装置ついてない刃物むき出しの機械なんだけど、注意すれば事故は起こらない」って言ってるのと同じ。しなくてもいい注意は排除して、安全な設計でいきましょう…ということだと思います。
SQLアンチパターン読み始めた
仕事でDBを触る機会が増えました。 転職前はSQLもうろ覚えだったんですけど、割と慣れてきました。
いざ使いはじめると業務が持つデータ量の多さにビビります。 あとは、アプリ開発していく中で既存のテーブルをメンテナンスしたり、自分で必要なテーブル作るようになってきたので後で困らないような設計にしないとなーと思って、「SQLアンチパターン」読み始めました。
- 作者: Bill Karwin,和田卓人(監訳),和田省二(監訳),児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (40件) を見る
まだ第一部しか読んでませんが、割と思い当たるところがあったので頭の整理のためにもまとめていきます。 長くなりそうなので、記事分ける。。。
会社でjQuery使ったら無能上司に怒鳴られたんだがwwwww - IT速報 を読んで思ったこと
会社でjQuery使ったら無能上司に怒鳴られたんだがwwwww : IT速報
「何でこんなことも知らないの?老害なの?」と仕事しながら思ってる人はそれなりに居ると思うし、私でSIerとして働いてた頃は割とそう感じるところがありました。(昨年辞めましたが。)
なんでこうなるのかなー
- ホントに上司の性根が腐っているから
- 会社として新しいことを取り入れなくても食っていけるから
- 開発を「誰でもできる」ようにするために、小難しいことは排除するのが正義だから
最初のは、「それは大変ですね。心中お察しいたします。」って感じで、その次は「いい会社ですね。」って感じですね。
色々言いたかったのは、最後のだけです。 開発を「誰にでもできる」ようにしているっていうのは、極端な言い方するとそこら辺の人を連れてきて、マニュアル通りにポチポチやればできるみたいな、「決められたことを決められた手順で作る」仕組みだと思います。(規模に比例してその特徴が濃く出てくる。) 世の中の多くのITシステム屋はこのスタンスでソフトウェアを作っていると思うんですが、そういう環境で意識高く新しい技術を取り入れていくのは、一人だけでは当然無理なわけで。 「お前らのやり方は古いし非効率的だ。これくらい世の中では普通に使われてるし、分からないなら自分で勉強しろよ。俺は俺のやり方でやらせてもらう。」みたいな>>1は無責任極まりないと言われてもしょうがない罠。
かといって>>1が悪い、って終わらせるのも不幸だなとは思うので
じゃあどうすればいいの
- 組織全体の知識の底上げを図る
- 偉くなって、自分でルールを敷く側に行く
- 「誰にでもできる」ための仕組みを作る側に行く
- 別の道を行く
「誰にでもできる」の「誰にでも」が下のレベルに沿うし、常に人が入れ替わるようなところでは知識の底上げが難しいんだと思いますが、地道に頑張るしかない。 偉くなってルールを敷く側に行くのはとても時間がかかるとは思いますが、地道に頑張るしかない。
「誰にでもできる」ための仕組みを作る側というのは、Webサーバーの動作知らなくてもWebサービスが作れるみたいなフレームワーク開発をするということなんですけど、ここでどんなにイケてる技術使ってても実際に開発する人たちが直接触ることがなければ、実装は好きにできるというメリットを享受しつつ、気がつけば「何でそんなことも知らんの〜」という状況を作り出す一旦を担ってしまう諸刃の剣。でも、間違いなく自分の力はつくと思います。
地道に頑張っても状況が打開できないなら、もうそういうスタンスでやっているITシステム屋から抜けて、別の道に行くのもいいのではないでしょうか。辛い中頑張って疲れきってしまうのはやっぱりよろしくないと思います。
まとめると
「何でそんなことも~」って愚痴ってるだけだと幸せになれない。
自分だけでも幸せを求めるなら別の道を探したほうがいい。
今の状況を変える気があるなら、草の根的に情報共有とかして頑張るか、偉くなって上から組織を変えるか。どちらにしても時間と根気( 胆 力 )が必要。
胆力なかったら自分だけでも幸せになったほうがいいと思う。
僕は今のところ幸せです。