極北データモデリングスマートフォン用の表示で見る極北データモデリングこの広告は、90日以上更新していないブログに表示しています。見る話題のSQLアンチパターンの目次に「アンチパターン:すべてのテーブルにID列を用いる」とあるのを見て、大胆にもサロゲートキーを否定しているのかと思って読んでみたが、どうも主張がはっきりしない。第3章には以下のようなことが書いてある。KEY制約を付与する」というパターンのこと。複合主キーが使いにくいからという理由で、id列を主キーにするのは良くないどうすべきかidという列名を止めて、xxx_id(customer_idとか)にしましょうO/Rマッパーがそれを要求している場合はアンチパターンを使ってもよい(ただし列名はxxx_idにしよう)自然キーの一意性・不変性があてにならない場合は、ID列のようなサロゲートキーを導入してもよい複合主キーは必ず排除すべきものではない。■複合主キーを推す理由が書かれていない著者は複合主キーを擁護し「複合主キーが使いにくいからという理由で、ID列を主キーにするのは良くない」と言っているが、読み返してみても何が良くないのかよくわからない。理由が書かれるべき位置には開発者の複合キーへの拒否反応は、数学者が2次元や3次元の座標を使うことを拒否し、物体が1次元に存在するかのようにして計算することに似ています。これによって幾何や三角法を単純化できることは事実ですが、対象とすべき現実世界の物体を正確に表すことはできません。あるが、主キー制約を複合キーからID列に移動しても「対象レコードを正確に指示する」ことはできるので、これは比喩が噛み合っていない。そもそも開発者が複合キーに拒否反応を示す理由は、それをアプリケーションで扱うのが面倒くさいから=余計な工数が発生するからでしょう。実際に工数がかさんでいるのなら、拒否反応を無視してよいわけがない。■結局、アンチパターンの追認になっていないか「自然キーの一意性・不変性があてにならない場合、サロゲートキーを導入してよい」とあるが、「IDリクワイアド」アンチパターンを積極的に使う開発者は、もともと自然キーの一意性・不変性を信用していないのだから(そうですよね?)、このガイドは「IDリクワイアドは常時使用可能」と解釈されるのではないか。■サロゲーキーを使う場合、過去の事実をどうやって復元するのかサロゲートキーを外部キーにして、自然キーを他のテーブルに転記しない場合、過去の事実をどうやって復元するかが問題になる。商品マスタの自然キーである[商品コード]を受注明細に記載せず、代わりにサロゲートキーである[商品ID]を載せた場合、受注時点の商品コードを復元できる保証がなくなる。商品コードの不変性があてにならないと考えて商品IDを導入したのだから、受注明細と商品マスタを結合して商品コードを取得したのでは、前提とやってることが矛盾してしまうからな。付ける)するなどの処置が必要だと思うが、私はサロゲートキーを使わないので、どうするのが常識なのかわからない。著者は「こういう場合はサロゲートキーを使っていい」とは書くが、上記の問題を含めて、サロゲートキー導入時の考慮事項をあまり検討してくれない。個人的には、IDリクワイアドについては「DBMS実装の事情を考えずにID列を必ず主キーにする」点が問題だと思う。主キーなんてあってもなくてもいいものなのだから、わざわざDBMSが困るような形で付与すべきでない。indexにしてしまうDBMS主キーを構成する列でなくてはパーティショニングのキーにできないDBMSなどを使うのであれば、自然キーを主キーにしておかないと、パフォーマンス問題に対する打ち手を失うことになるだろう*1。主キーはあってもなくてもよいと書いたが、これは本書の主張ではない。著者は逆に、すべてのテーブルには主キーが必要である外部キー参照されるものは主キーでなくてはならないと考えている模様。すべてのテーブルには、重複行を防ぎ、個々の行を識別するための主キー制約が必要です。主キーは外部キーから参照されることで、テーブルの関連付けを行います。主キー制約が重要になるのは、次のような必要があるときです。・行の重複を避けたい・クエリで個別の行を参照したい・外部キー参照をサポートしたい大した話ではないが、ここに挙げられているのは「主キーが必要な理由」になっていない(ぜんぶUNIQUEインデックスで実現できるから)とは言わせていただきたい。細けえなぁと思われるかもしれないが、前述の通り主キーにはDBMSによって変な制約が付くことがあるので、ある程度注意して使わなくてはならない。主キー重視派の著者は「このテーブルには主キーは必要ない」という開発者の発言に対して「間違っている」「勘違いしている」と書くが、その人「UNIQUEインデックスがあるから主キーは要らない」と言ってたんじゃないの、とちょっと思った。21:05SQLアンチパターン「IDリクワイアド」の再検討Tweet広告を非表示にするもっと読むコメントを書く2012-11-20年間の生活費を60万円減らす方法life去年3LDKから1DKに引っ越したら家賃が5万円安くなった。前の家では日当たりの悪い2部屋は物置、和室は洗濯物を干す場所にして、居間に万年床を敷き、夫婦二人でほとんど布団の上だけで生活していた。テーブルには読みさしの本や仕事の資料が山積し、ソファは服やバッグの置き場所と化して座ることもできず、布団の上にちゃぶ台を置いて飯を食っていたら、ある時気付いたのだ。俺たち、半分ぐらいの広さの家に住めるんじゃないのかっ...!ちょうど向かいに新築のアパートが建ったのでさっそく内見して寸法を測り、持ち込めそうにないものを狂ったように捨てたり売ったりして引っ越した。1DKに相変わらず万年床を敷いてその上だけで生活している。前の家とまったく変わらない。エアコンが最新型になったのでかえって快適になったぐらいだ。猛烈に捨てたり売ったりしたものは小林よしのりの本とか(妻がコヴァなのだ)15インチのCRTディスプレイとか、2人暮らしなのになぜか3つあるスーツケースとか、誰が買ったのかわからない鉄アレイとか、こんなもののために年間60万払っていたのか俺は...と愕然とする面子だ。努力せずに生活費を下げたいなら、狭い家に引っ越すという手は非常によい。何も節約しないで今まで通りの生活ができるからだ。思い切って面積が半分ぐらいの物件に移っても意外と困らないからやってみるといい。1DKに二人で住むなんてあり得ない、という反応をされることもあるが、そういう時は妻の仕事上の大先生(先生の先生)の話をして認識を改めてもらう。この人は新婚当時三畳一間の物件に夫婦2人で住んで、なおかつ一人居候の男を住まわせていたのだそうだ。この話には感動するポイントが2つあって、それは三畳間に居候を迎える大先生の度量の広さと、もう一つは居候の精神力だ。普通ものすごく居ずらいだろ。新婚夫婦と川の字になって寝る根性が貴方にはあるか。18:28年間の生活費を60万円減らす方法Tweet広告を非表示にするもっと読むコメントを書く2012-09-18「等しい」と「重複している」の違い。それらとUNIQUE制約の関係SQLSQLを使っていると、あたかもNULLがNULLに等しいかのように見える場面が多々ある。複数のNULLが1個に集約されるとか。INTERSECT)でのNULLの扱いとか。見るによれば、NULLとNULLは等しくはないが「重複」はするのだそうだ。列col1とcol2が等しいか、ともにNULLならば、それらは重複していると判定される。BYが複数のNULLを1行に集約するからといって、それは複数のNULLが「等しい」からではない。...ということらしい。SQLの規格は、「等しいこと」と「重複」のどちらに基づいて一意性を判断するよう求めているか。SQL92には、UNIQUE制約は「いかなる2行も、nullではない同じ値を持たないとき」に満足される、と書いてある。「重複」ではなく「等しいこと」に基づいて一意かどうかを判断せよ、ということで、やはり前回見た「NULLがUNIQUE制約に縛られない」というMySQLの挙動は、SQLの規格に適合しているようだ。13:11「等しい」と「重複している」の違い。INDEXを振った列に複数のNULLを投入できること利用して、ユニークであるべきユーザIDの使い回し(=退会したユーザのIDを新規ユーザに開放する)を実現する」という話。アクティブなユーザ名はユニークにしたいけど削除されたユーザの情報は残したい。削除済みユーザテーブルは作りたくないとかいうワガママを発揮したい時にdeleteフラグに使えないかなーなんてだめですかそうですか。なんか他にまともな方法無いですか...。桝原翔市的博客いやーこれはまともな方法じゃないでしょうか。「NULLはNULLに一致しない」のが絶対の原則なのだから、NULLを使ってUNIQUE制約を回避するのは裏技でもwork-aroundでもない、正当なテクニックでしょう。私はTM派なので実表上にnullを発生させる設計はしないが、Nullableな列を使ってもよい流派の方であれば、特に避ける理由はないんじゃなかろうか。「NULLがUNIQUE制約に縛られない」ことに違和感がある方は、NULLについて「対象にはその値があるが、システムはその値を知らない状態」ではなく、単に「値が無い状態」をイメージしているのではないかと推測する。後者のイメージを持っているとNULL+1がNULLになる1=NULLがUnknownになるUNIQUE制約のある列に複数のNULLを設定できるといったSQLの挙動について、なぜそうなるのか理解できないはずだ。実際SUM関数で集約すれば1になるのだから...1=NULLはFalseでいいんじゃないか...「値が無い状態」が「1という値」に一致するわけがないのだから...みたいな疑念が沸くだろうから。NULLは「値がある(が、システムはその値を知らない)状態」だと思えば、これらの疑問は解消する。Server等、複数のNULLがUNIQUE制約にひっかかる実装も存在するらしい。いうDBMSは「NULLはNULLに一致する」「NULLはNULLに一致しない」を同時に言っていることにならないか。矛盾しているのではないか。思ってSQL標準の解説書を読んでみたら、SQLでは「一致」と「重複」が別の概念で、NULLについては「重複するが一致しない」のだとわかった。Server等ということで、後者にも矛盾はないようです。12:29「NULLがUNIQUE制約に縛られないことを利用する」のは、正当なNULLの使い方Tweet広告を非表示にするもっと読むコメントを書く2012-09-10データモデル自体はアジャイルなのだが...モデリング全体に与える影響が極めて大きく、後戻りしにくい「スポンジ層」というのが存在する。そのひとつが渡辺さんの言われているデータモデルである。Way平鍋さんが「(業務システムの)データモデルの変更コストは非常に高い」という意味のことを書かれている。データモデル自体の変更コストは非常に低く、後戻りし放題である。tableするだけだからな。データモデルに手を入れるためには、同時に既存アプリケーションへの影響を調査して、漏れなく修正しなければならない。プロジェクトが進行するほど、データモデル改変の影響範囲は広がり、修正点も増えていくだろう(元記事でデータモデルを「高リスク制約」つまり「プロジェクト開始からの時間が経過するほど変更コストが高まるもの」に分類しているのは、このことを指しているはずだ)。この手間をデータモデルの変更コストとしてカウントすれば、元記事にあるように「データモデルの変更コストは高い」という結論になる。私のようなデータベース中心主義者から見れば、データモデルは非常に俊敏なシステムの構成要素であって、それを参照するアプリケーションこそがアジャイルではない、ということになる。将来の変化に対応しやすいデータモデルをプロジェクトの最初に作成する将来の仕様変更時にスキーマを変更しなくて済むよう、スキーマレスなDBを採用するといったことでは、元記事で言う「データモデルの変更コスト」は下がらない。代わりにデータモデル変更の影響範囲を極力狭めるようアプリケーションをレイヤリングすることが重要(というか、不十分なんだけどそれぐらいしか打ち手がない)、と考える。現状、「データモデルをアプリケーションに極力漏らさないようにする」ことが当たり前になっているとは思えない。メジャーなO/Rマッパーが持つ「クラスをそのまま基底テーブルにする」という発想。これ使ってしまうと「社員と部門の関係を1:NからM:Nに変更」といったデータモデルの変更が、アプリケーションの各レイヤに筒抜けになる。クラスのインスタンスはDAO層でのみ参照する。データを転記したDTOを参照させる」とかすれば既存アプリ内の影響範囲を限定できるが、やらないでしょう。「スキーマレスなDBは、仕様変更があってもいちいちスキーマを変更しなくてよいので、外部環境の変化に強い」みたいな話。スキーマの変更自体は、参照元アプリケーションの調査・変更に比べれば大した作業ではない。スキーマの変更コストをゼロにしたところで、アプリケーションの変更量が変わらないか、逆に増大するならば意味はない。ところが、スキーマレスDBを採用したら「社員と部門の関係はM:Nである」といったデータ間の関係(=要するにデータモデル)はプログラムで表現するしかなくなる。データモデルがアプリケーション全体の中に溶け込んで拡散する。よって論理的なデータモデルの変更が、即アプリケーション全体の調査・修正につながってしまう。元記事で言う「データモデルの変更コスト」を下げるためには、アプリケーションの中でデータモデルを参照する範囲をぎりぎりまで絞り込まなくてはならない。RDBを前提にすれば、「データアクセス層を作ってその中だけでSQLを発行する。データアクセス層から出てくるものはデータモデルを反映しない、単なる二次元の結果表にすればよい」ということで、そんなの当然じゃないかと思われた方はおめでとうございます。他にも打てる手があるという方は教えてください。12:57データモデル自体はアジャイルなのだが...Tweet広告を非表示にするもっと読むコメントを書く2012-02-24プロとアマの境目について語るな。手塚貴晴さんという建築家がいる。一種の天才なのだと思うのだが、5年ほど前、TVで見た光景が忘れられない。部下の設計者が徹夜で作ってきた建築模型を一目見るなり「これはゴミだね」と言って、ガシャリと壊してしまったのだ。僕はそのころ駆け出しコンサルタントになったばかりで、部下の人の痛みがよく分かった。それと同時に、本当にたくさんのことも学んだ。あなたの作ったものはゴミである、あるいはプロとアマの分岐点これをやっていいのは一流だけだ。「私は一流だ」と言い切れない程度の人間は絶対にやってはならない。理由は書かないが、書かなくても同意してもらえるのではないかと思う。「頑張ること」それ自体に価値があるのはアマチュアの世界、価値がないのがプロフェッショナルの世界である。全ての人がプロフェッショナル的に仕事をするべきだ、とは僕は思わない。プロフェッショナルになりたいのであれば、「僕、努力したもん」は封印しなければならない。そんなこと、他の人は知ったこっちゃないのだ。プロとアマの境目について語る、という行為は甘美だ。俺も「プロのSEとは何か」とか若手に向かって思いっきり説教してみたい。してみたいが、絶対の禁忌にしている。「プロとは何か」なんて客観的には自明だからだ。SE稼業で報酬をもらっている人は全員プロのSEだ。腕が無かろうが顧客の評価が低かろうが関係ない。プロ野球選手が全員プロであるのと同じ意味で、金もらって仕事しているSEは全員プロのSEだ。かかわらず、我々が「プロなら最低限この程度できてもらわないと」という主観的なラインを捏造して、レベルの低い同業者を非プロ扱いしたくなるのはなぜか。それは、はっきり言って大変に気持ちよいからです。横軸に人数、縦軸にレベルを取ると、どんな世界もつぶれたピラミッドの形になるが、プロ・アマのラインを恣意的に捏造すれば、自分を一流と同じ高位のグループに、相手を劣位に置くことができて大変気持ちいいのです。プロ論をぶっている人を観察すれば、例外なく(本当に、一人の例外もなく!)自分をプロの側に含めていることに気付くだろう。自分がアマ側に転落しないように、恣意的に線を引いているから、誰でも必ずプロになれる。これは本当に危険な行為で、中年期の職業人の精神を腐らせる元凶みたいなものだ。お勧めはプロ・アマの代わりに一流と二流の境目について考えることだ。するとさっきまで馬鹿にしていた人と自分が同じグループに入ることを発見して、大変恥ずかしくなるわけです。俺にも恣意的な線引きをさせてもらえれば、「一流の(あるいは師の)目から見れば、俺も新人も似たようなものだろうなあ」という自覚が無い中年はダメです。下ばっか見てないで上見ないと。まとめ成果物をゴミ扱いしても、自分が一流なら感謝されるだろうし、大した人間でなければ「何だこいつ」と思われて終わりだ。「何だこいつ」と思われた原因を、自分ではなく相手に求めているうちは一流にはなれないと思うがどうか。21:49プロとアマの境目について語るな。わからないこと縁あって仕事でMySQLを使いそうなので、いまのMySQLがどうなっているのか少しずつ調べている。現在のデフォルトストレージエンジンであるInnoDBの設計思想に困惑している。Join」が使えるの2点だ*2。これらの利点の代償として行長を拡大するような更新が多発するとスキャンが徐々に遅くなっていく主キー値が昇順になるようにinsertしないとスキャンが徐々に遅くなっていくROWIDが存在しないので、セカンダリインデックスを経由するデータアクセスの効率が悪い*3といった問題を受け入れるわけだ。scanや結合の高速化はあまり期待できない。}がInnoDB上にあって、受注IDには整数の連番が設定されているとする。scan)するアプリケーションは多分ないだろう。indexは主キーである受注IDが占有済みなので、受注日にはセカンダリインデックスを張ることしかできない。indexの全件スキャンをするか、どちらかで処理せざるを得ない*4。indexにする仕様だ。Joinだが、マージの前にソートをしていない。scanすればソートなしで即結合できるためだ。Join」は、大型のテーブル同士を結合するための最速の結合方式だ。Join」で行いたい。Join」することができない。Joinほどのパフォーマンスを出すことはできない。indexを有効利用できず、そのコストだけ負担しているシステムが多々あるものと推測する。InnoDBが広く使われている理由は他に選択肢がなかったからで、インデックス/テーブル分離型のMVCCなストレージエンジンの開発が進めば(PBXTとかFalconがそうなのですよね)、InnoDBの出番は減って行くだろう。indexは使わないほうがよいと思っているのだがどうか。indexにしようとするが、あっちの陣営が正しいのか。indexの汎用性に問題はない。indexにするのがベストプラクティスということになる。indexになってしまうInnoDBは用途を限定すべしということになる。indexのおかげで性能問題をクリアできた!という経験をすることはないだろう。そんな経験があったらおめでとうございます*3:これは実装による。Guessと呼ばれるROWID的なものが格納されているのだそうだ*4:逃げ道がないわけではない。scanに誘導できるだろう。ファットモデルになるのか生きているうちに自然キーvsサロゲートキー問題に決着を付けたい(1)事実と真実はどう違うか。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。