[DBデザイン#43] ルックアップを設計に巻き込む

ルックアップの機能はFileMakerではお馴染みでしょう。FileMakerがリレーショナルデータベースである以前のかなり初期の頃からあった機能です。むしろ、大昔はこの機能だけがテーブル間連携だった時代もあります。その後、リレーショナルデータベースの仕組みが組み込まれても、ルックアップの仕組みは残っています。つまり、必要だから残っているとも言えます。そのこともあって、筆者が開発しているフレームワークのINTER-Mediatorでもルックアップはサポートしています。ということで、「さまざまな開発ツールでサポートされているルックアップ」と言いたいところですが、どちらかと言えば、ローコード系でよく実装されているということになるでしょうか。

ルックアップの詳細を説明する前に、まず、テーブル間結合のことをおさらいしましょう。お馴染みの、販売明細と商品の表をまたまた出してきます。サロゲートキーを主キーにしました。そして、それぞれの表の商品IDが照合の手がかりです。この2つの表を結合することで、下のような表が得られるので、ここでの「商品明細」だけではわからなかった商品名や単価が、販売1つ1つのレコードに追加されており、例えば、1行目の商品は800円などとわかるようになります。その結果、納品書の明細に記載ができるデータになったとも言えますし、単価と個数を掛け算して、金額を求めることができるようになったとも言えるわけです。ここで、下の表を見る限りは、商品IDから商品名や単価が決定される、つまり関数従属性があることから、「商品」の表が分離できるということが、正規化の理論でも実証されているので、表を分離して管理するということがデータベースの流儀ということになります。

ここで、商品の価格が変わったとします。例えば、ロボットいか2号が850円になったとします。そこで、商品の中の1レコードの単価フィールドを850に更新しました。更新はこの1箇所だけ行ったとします。その後に、販売明細と商品を結合した下の表を見ると、当然ながら、ロボットいか2号の単価は850円になっていますが、2箇所登場しているいずれも850円になっています。大元が変わったから、それを参照している複数の箇所で結果的に新しい値が得られています。

この「修正」が、「間違っていたから」行ったものであれば、これで、無事に正確なデータを記録できたことになり、それでOKです。納品書を再発行しないと行けないような気はしますが、データベース上の話はこれで終わりかと思います。しかしながら、実は11月10日に、価格改定が行われたとしたらどうでしょうか?つまり、11/9の販売は、800円のまま、11/11の販売は850円にしたかったと言っても商品テーブルを修正した結果、過去の全てのデータに対して更新されてしまった結果になります。

そうならないために、どうすればいいか? もちろん、ルックアップを使うのですが、まず、なぜルックアップなのかということは傍に置いておき、ルックアップを使った結果を示します。同じようなデータですが、販売明細に「商品名」と「単価」のフィールドを用意します。正規化が崩れるじゃないかと思われるかもしれませんが、その件は、後から(次回)に説明します。この新たに作ったフィールドは、商品テーブルに対応する値を複製した結果を残すために用意します。複製をするというのがポイントになります。販売情報としては、販売日〜個数までの4つのフィールドで事は足りているので、まずは新しい2つのフィールドは空欄とします。そして、テーブル結合しても、やはり空欄のままです。

ここで、ルックアップは一般には次のような動作をします。ここでは、商品との関連を商品IDで照合していますので、外部キーにあたる販売明細の商品IDが更新された場合、結合した先にある「商品」の表の「商品名」「単価」を、販売明細の「商品名」と「単価」に複製するということを行います。外部キーをトリガーにして、リレーションシップがある先のレコードから複製するという動作です。ここでの商品IDは、例えば、ポップアップメニューで選択できるようになっていれば、メニューを選択したときに、101などの値がフィールドに設定されるとともに、システムの背後で商品名と単価のコピーが行われます。これがルックアップの動作原理です。単に「取ってくる」ということではなく、どのフィールドの更新がトリガーになっているか、そしてどのテーブルから取り出し、結果、どのフィールドに値を入れるのかということで、把握すべき事態はたくさんあります。ですが、把握すべきことは全部データベースの設計に絡んでいることであるとも言えます。

なお、こうしたルックアップを複数のレコードでまとめて行う機能もありますが、通常は入力に伴って複製されることになり、その場合は1レコードずつの処理になります。通常、複製先にデータが入力されていれば、つまりNULLか””でない場合は、複製を行わないように設定するのが一般的かと思われますが、この動作はオプションになっているので、設計者が適切に選択しなければなりません。このオプションはどっちがいいのかというよりも、ユーザーインタフェースの動作や、あるいはシステムの要求がどうなっているのかに関わることであり一概には言えませんが、安全策としてはデータがあれば複製しないようにしておき、再度のルックアップ処理をするためには一度フィールドの内容をクリアするというような措置を取るようにするのが安全です。ですが、そのことを警告してルックアップはしなかったことを適切にユーザに示すことなどが必要になり、ユーザインタフェースは込み入ってきます。

さて、ここで、商品のある単価が845円になりました。しかしながら、販売明細の単価は複製した値なので、そのまま800円のままになります。これは、すでに別々のフィールドになっているので連動しないのは当然のことです。

そして、別の日に売上明細にレコードが3行追加されました。ルックアップが稼働する前は、商品名と単価は空です。

商品テーブルと結合行い、その結果から、空欄になっている追加した3つのレコードの「商品名」と「単価」を埋めました。すでに入力されている最初の2行はそのままにします。ルックアップをもちろん、そのように動作させるということです。当然ながら、新たに追加した4行目の販売データでは、単価は更新した845円になります。

ルックアップを適用した結果「販売明細」のテーブルでは、このように商品名と単価の空欄部分が埋まりましたが、販売日によって、ロボットいか2号の単価が違うという状況が「正しく」記録されたことになります。

これが一般的なルックアップの機能の紹介です。リレーションシップの上で正しいかどうかということは議論のネタかもしれませんが、その前に、このルックアップは途中で説明した通り、フィールドの更新によって稼働します。つまり、何らかのトリガーがあって、そのトリガーにより、該当する単一のレコードのフィールドが、別のテーブルから取ってきた値で上書きされるという仕組みです。上記の図では図では2、あるいは3レコードをまとめて更新しましたが、FileMakerでは単一のレコードごとに複製されることが一般的ではないかと思われます。こうした複雑な動作を設定することは結構間違えやすいのですが、見通しを良くする方法としては、データベース設計をきちんと行って、何と何が関連性があるのかということを完全に把握しておくことが挙げられます。

このルックアップが開発ツール等に実装されていれば、手軽に使えるのですが、実装がない場合は自分で実装しないといけなくなります。これは仕方ないことになります。

このルックアップはセットアップの大変さはあるものの、ある意味単純な機能ではあります。そして、意外に業務で発生するさまざまな要求をうまく飲み込んでくれる機能でもあります。なんでもルックアップにすればいいということではない一方で、複雑な仕組みを作らなくてもルックアップで実運用に耐えられるような仕組みが作れるということは、すでにFileMakerの長年の実績で証明されていると言えるでしょう。その辺りの理由は次回に説明します。

[DBデザイン#42] サロゲートキーは使うべきか?

一時期、サロゲートキーについての議論が盛り上がったこともありますが、古い本を紐解くと、2006年に初版が出ている「Refactoring Databases: Evolutionary Database Design (Scott W. Ambler and Pramod J. Sadalage)」あたりにそこそこ詳しく書いてあったりもしました。サロゲートキー(surrogate key)とは、人工的に作り出したキーフィールドで、例えば連番の機能を使ってあるフィールドに自動入力するなどして、確実に主キーとなる単独のフィールドをテーブルに用意しておく手法です。最近は、UUIDを文字列型のフィールドに入れるような場合も見られます。連番の場合はバグがや運用ミスがなければ確実にレコードごとに異なる値が振られます。UUIDだと、異なるシステムで生成されたレコードを統合しても、やはり一意な値になっていることが十分に期待できるので、少々のパフォーマンス低下はあるとしても、データベースを分散して運用する場合や、後から統合が必要な複数のデータベースの運用では便利に利用できるでしょう。

しかしながら、サロゲートキーは元々データに存在しない、まさに人工的なキーであって、モデルそのものに存在するのが間違いであるという考え方もできます。人工的に値を追加していいのであれば、なんでもありになってしまい統制が取れません。本来、業務上保存が必要なデータがどんな振る舞いをするのかということを示すのが主キーであり候補キーであるという考え方であり、その主キーの存在を手がかりにしているのが正規化の理論だったりするので、無駄なものを許容しないという見方は分かります。かくして、サロゲートキーは使わないことを「複合キー(Compsite Keys)で運用する」ような呼び方になっているのですが、この場合は複数のキーフィールドであるということではなく、複数のキーフィールドであっても本来のデータにある情報からキーフィールドを選択するべきであるということを言いたい用語です。つまり、英語のCompositeを単に複合と訳すと、複数っぽいニュアンスが垣間見えてしまうというところです。なお、「人工キー(Artificial Keys)」の対比で、「自然キー(Natural Keys)」と呼ぶ場合もあります。

それぞれのメリット、デメリットは順次紹介しますが、概して、複合キーであるべき論は、データモデリングの立場から出ている意見です。これに対して、実装側の意見としては、サロゲートキーあるいは「単独のキーフィールド」というべきかもしれませんが、そういう状態が望ましい場合が多々あるというところでしょうか。どこかで激論があるとしたら、単に立場の違いということだけですが、IT業界の不思議な分業体制がいまだに根付いている感もあるので、やはり「職種による対立」に発展しかねないテーマでもあります。ですが、時代は協調を求めています。相手の立場に立って考えることがなんと言っても必要です。

まず、データモデリングの立場から、サロゲートキーがなくても主キーを決定できる場合は、不要なものがない方がモデルを示す情報としてはより良いという単純な考え方ができます。不要なものは不要だということですね。一方、データモデリングの立場であってもある状況であればサロゲートキーを許すというか、それをモデルとして認識せざるを得ないような場合です。それは、本当にデータに主キーが存在しないような場合です。極端な例では、センサーからの送達データなのか、どこかでエクスポートしたデータなのか、フィールドが {時刻, 場所, 温度} のような場合はどうでしょうか? 普通、時刻あるいは時刻と場所でキーになりそうな感じではありますが、強引ですが、同一時刻、同一場所で測定されていることもあるような場合はもうお手上げです。その場合は、サロゲートキーを付与するしかありません。インポート時に付与するなどの方法があり、キーとしてはサロゲートキーを使うということをモデルに記述する必要が出てきます。このような事例として、データウェアハウスでのデータ管理でサロゲートキーを使うというのは基本的なテクニックとして古いから認識されています。いずれにしても、こういう「どうしようもない場合」以外はサロゲートキーを使う必要はないという立場です。

一方、サロゲートキーは容認、あるいは必要という立場は、まず、実装上の立場から言えます。システムの内部では、「レコードを特定する」という場面がよく発生します。わかりやすいのは、一覧を表示し、「編集」ボタンをクリックすると、そのレコードの編集画面が出るような場合です。ここで、画面には見えていないかもしれませんが、一覧の各行のに対して「どのレコードか」を特定するデータがどこかに隠されています。そして、ボタンをクリックした時、その特定のためのデータをシステムは次のページに引き渡して、そのページでは、示されたデータを表示します。そして、そのページが編集可能なページであるとしたら、あるフィールドを変更した結果をデータベースに伝えるために、どのレコードなのかを覚えておいてそれを利用します。もちろん、そのどのレコードなのかに使えるデータは主キーです。これが {時刻, 場所} のようなキーを使うとの{計測結果ID}のような単一のサロゲートキーを使っている場合とを比較すると、当然ながら、システム開発は後者の方が容易で確実、つまりソフトウエアの品質保証を確保しやすいと言えるのです。もちろん、「プロの開発者ならそれくらい対処しろや」と言いたいかもしれませんが、テーブルごとにキーが違っていると、その都度微妙に違うプログラムを作ることになりますが、全てのテーブルがサロゲートキーを主キーにしているというルールでデータベースが構築されていれば、どのレコードもプログラム上の扱いは均一化されるので、やはりミスや出戻りが少なくなることが期待できます。さらには、現在はフレームワークを使った開発が一般的になり、フレームワークでは様々な処理が自動化されているのですが、そのような場面ではサロゲートキーの利用が必須だったり、あるいはフレームワークが勝手に作ってしまうような場合もあるかもしれません。仮に複合キーもOKとしても、フレームワークの内側で、文字列データの比較などを適切に行っているのかということも気になる部分であhあって、それなりにデバッグはされているとは思いますが、謎の関連付けが発生しかねないという懸念も持ってしまいます。

すごく荒っぽい言い方をすれば、開発する側はサロゲートキーの方が都合がいいのです。単一のレコードの特定に使う場合もありますが、ロジックを組むような場合で、いくつかのレコードの集合を記録しておいて、ある値を求めて、後からそれらのレコードを全部更新するような場合、つまりは主キーを配列に残して処理を進めます。そのような場合でも、複合キーの場合とサロゲートキーの場合では、当たり前ですが、後者の方が容易であることは言うまでもありません。また、連番で整数になっているフィールドの値だと、気軽に検証ができます。UUIDも文字列ではありますが、長さや正規表現のマッチングで検証は可能です。そういうちょっとした安心を増やすことも、サロゲートキーではやりやすいと言えます。

Refactoring Databasesでは、以上のようなことを「複雑さの回避が可能」と表現しています。そして、この書籍でもう1つ重要なことが書いてあって、「ビジネスドメインの変更を受けにくい」と述べています。要するに、仕様が変わって主キーだったと思っていたものが違いました、あるいはもっとフィールドを増やさないと主キーになりませんと言うことがあったとして、サロゲートキーで運用してれば、「主キーの変更」と言う大ごとは起こらないと言うことです。複合キーによる主キーを変更するとなると、おそらくプログラム側の変更は結構大変ですし、変更しきれずバグがダラダラと残りそうな雰囲気がぷんぷんしてきます。

ところで、このような仕様変更の話が出てくると、必ず言われるのが、「それは最初に仕様をまとめたやつが認識できなかったのが悪い」などといった意見です。後でも議論しますが、確かにデータベース設計は初期段階に決めて変更しないようにするという鉄則のようなものはあるのですが、なぜ仕様変更となるのかということを改めて考えてみる必要があります。もちろん、設計者がボーッとしていて気づいていないことで、後から変更が必要になるというのは困ったものですが、それを避ける方法としてはレビューをきちんと行うということに尽きるかと思います。開発者全員が釈然と対処したとしても、システム発注側が認識していないことについては、どうしても、開発途中にその要求が具体化されるということがあり、それによってデータベース設計の変更が伴うことはよくあります。仕様書に従って作っているだけだと開き直るのは、IT業界内での理想論であって顧客ビジネスをしている限りは完全に開き直るのは悪とされます。ともかく、発注側が全てを把握しているとは限らないのです。この前提は忘れてはいけません。発注側が、業務を全て理解して分析可能な知識があるとは誰も保証していません。なので、ある程度動くシステムができたところで、「あっ!」ってなることは十分あり得る話です。一方で、ビジネス環境の変化も早くなりつつあり、開発している間にビジネス環境の変化はあり得ます。それに追随することを顧客は望んでいます。要するに、設計変更を受け入れる余地がないような開発の段取りでは、現在では受け入れてもらえないのではないでしょうか。もちろん、そのために別の機能の実装は諦めるとか、費用な納期を見直すという交渉を行うなどの、技術的なことを超える対処は必要になりますが、現在ではそういう時にどういう問題があってどうすべきかは知識として蓄積されていると言えるかと思います。仕様変更の必要が生じたとき、前向きに取り組むかどうかの問題です。そのような中、設計変更が生じたとしても、仕様作成の担当者がミスったという見方は自分の責任じゃないのだと言いたいのだと思いますが、なんだかブラックな空気を感じます。

実際、筆者でも、「データ内のフィールドを主キーフィールドにしてやばいことになった」ということを何度も経験しています。製造番号や商品番号が重複しているのは、まだ序の口でした。ある案件で、早いうちに製造番号が、なぜか10年経過すると同じ番号がわり当たると言うルールで運用されていて、それは変えないという決定がなされたので、当然ながら、サロゲートキーを割り当てることになります。その案件では、商品番号も怪しいと思っていたら、やっぱり重複があって、というか、1レコードで管理したいものに商品番号はユニークに振られていなかったので、やはりサロゲートキーを設定しました。このような、よくある設計例であるような「◯◯番号」であっても、実データを読み込むと「あー!重複している」ということは結構ありました。ですが、一番印象に残るのは、「◯◯◯の◯◯番号」です。伏せ字ですみません。これを書くと色々不味そうなので、お許しを。この番号は常識的には重複はないと思われるものです。このシステム、ある程度開発された結果を元に作れというのが至上命令だったので、痕跡を残すためにも途中から手を入れ始めました。そして、この番号がキーになっていたんです。その時の開発スタッフ同士で、かなり悩みました。この番号がキーだけど、ほんとにキーにしていいのか? いっそのことサロゲートキーに置き換えるか、そうだとしたら、かなり変更しないといけない。などなど議論して、結局、常識的にこの番号はユニークだろうということで、そのまま使いました。この「常識」って疑うべきことの最優先だと思いながらも、トレードオフの理由として光っていたのです。そして、データを統合したら、なんと重複があったんです。しかも、重複回避のための番号の振り替え等はやらないと決まったらしく、これはきつい状況です。サロゲートキーにしていれば、そこで悩むことはなかったのかもしれません。ちなみに、筆者はそれが顕在化した段階では開発スタッフから外れていたので、その後どう対処したのかは聞いていません。ですが、当然最初の段階で、その番号には絶対に重複はないということを発注側から聞いており、常識的にそうなので信じてしまい、データの確認もせず(というか、できなかった)、ということだったので、最初からサロゲートキーにしておくべき案件だったと後から噛み締めるのでした。

一方、サロゲートキーを設定したとしても、候補キーとしてフィールドの組みがあると確実に言えるような場合は、UNIQUEをつけたインデックスを作り、それらのフィールドをキー制約に入れて、少なくとも、「同じデータの組み合わせ」が存在しないようにすることは必要になります。もちろん、必要に応じてNOT NULLによるNULL制約も設定します。サロゲートキーがあるからとそれだけPRIMARY KEY定義するだけで放置せずに、候補キーがあるとしたら、それはデータベースの設計としてケアする必要があることが一般的と考えるべきでしょう。

そのほか、パフォーマンスの議論などもあるのですが、現在のコンピュータ環境と進化したデータベースの実装では、あまり関係ないこともあるかもしれません。昔はパフォーマンス要因として大きかったものも、時代と共に変化します。もちろん、計算量を見積もって考えるということは基本ではありますので、その時代に応じたパフォーマンスの検討は必要にはなります。ちなみに、郵便事業より全国の郵便番号のデータが配布されていて、10万件を超える文字データです。筆者は大昔からこれを使っているのですが、20年前はインデックスなしではそこそこ検索に時間がかかっていましたが、10年前の段階で、インデックスの有無による検索時間の差はもうほぼないくらいになっていました。ですが、もちろん、念のためにインデックスは付けています。検索が重なる場合やパフォーマンスを落とすような検索ワード(と書きつつどんなのかわかりませんが)があったときの対処ということです。

主キーの話をFileMakerと絡めるのはかえって変な方向に行くかもしれませんが、FileMakerはある時期からテーブルのデフォルトフィールドで「主キー」という名前のフィールドを作り、当初は連番を、そして現在はUUIDを設定するようになっています。つまり、サロゲートキーそのものが既定値で作られるのです。FileMakerには、SQLにあるようなPRIMARY KEYに相当する機能はありませんし、複合フィールドのインデックスを作ることもできません。単一フィールドに対する一意性を確保することは可能です。結果的にサロゲートキーが前提としてそこで一意性を確保し、自然キーがあるとしたら、入力時の検証等をうまく働かせるなどの対処が必要になります。そのように、FileMakerでのリレーショナルデータベースの実装はある意味少し変わっているところもあります。ですが、基本、サロゲートキーを既定値にしているあたりで、サロゲートキー自体は否定されるものではないかということを示しているとも言えます。

モデリングをする側としては、「俺の素晴らしいモデルに余計なものを入れるな」と思われるかもしれませんが、実装して、完成に持ち込まないといけない立場からすると、サロゲートキーに結果的にしなければならない場面が出てくることや、複雑さを持ち込まないというあたりで、積極的に使いたいのがサロゲートキーなのです。別に実装チームはモデリングチームの成果を否定したいわけではなく、実装に都合の良いようにして欲しいだけなのです。

ちなみに、現在の複雑なシステムでは、多くは「レイヤーアーキテクチャ」というアーキテクチャパタンを踏襲した構成になっていると思います。一般にはユーザーインタフェースを最上位層、データベースを最下層として、その間を1層あるいは複数層のコントローラなどで構成し、基本的に「上から1層下への利用」にとどめるという実装を進めるのが特徴です。2層を超えることは、複雑さを回避するためやりません。下から上については、一定の制約を設けて実施できるようにしますが、よくある手法はイベント発生により、あるレイヤーのオブジェクトのメソッドがコールされるような枠組みを作っておいて、複雑な要求に応えられるようにします。このレイヤーアーキテクチャは、上から下へのコールが基本なので、高いレイヤーほど一般には変更しやすくなります。低いレイヤーを変更すると、下手をすると、上位レイヤーまで変更しなければならなくなります。そこで、よく変更が行われるユーザインタフェースやそれを受け取る上位コントローラのレイヤーを「上」に配置します。言い換えれば、データベースやそのモデルなどは「なるべく早い段階で完成させて固定化する」という方策とセットになった手法であるとも言えます。つまり、データベース設計は、後からあまり「変わらない」ことを期待しているのです。しかし、それを拡大解釈しているのかどうか、「変えない」と思っている方もいらっしゃるのかもしれません。データベース設計者を神格化しているのか、決定事項が絶対だと思っているのでしょうか? データベース設計者に特権があるわけではなく、あなたもスタッフの1人です。大体、偉い人、あるいは偉そうな人が担当していることが多いのですが、仕様変更があったらデータベース設計も含めて変更作業に着々と取り組むことが求められています。レイヤーアーキテクチャだとデータベース変更をしてはいけないわけではありません。それよりも、顧客のニーズに応えることが、より高いレベルでのゴールにあるので、変更の必要がある場合には変更すべきなのです。確かに上のレイヤーまで変更が及ぶかもしれませんが、何の整理されていないコード群に比べて。影響の範囲をグラフ(メソッド呼び出しのつながり)として把握できるので、素手で取り組むよりも遥かに効率は高いと言えます。こういったIT業界内の謎のヒエラルキーも崩れがちではありますが、そういう空気感はまだ感じるところでもあります。

正規化の理論は別の記事で詳しく述べてきていますが、意図的にサロゲートキーを使ったサンプルも載せているように、サロゲートキーを使ったからと言って、正規化の理論が崩れることはありません。ただ、主キー以外に候補キーを把握した上でないと議論はできないので、サロゲートキーだけを見ていればいいわけではありません。ですが、サロゲートキー追加しても正規化の理論が崩れないということであれば、それはそれで安心して使える材料でもあると言えるでしょう。

[DBデザイン#41] データベースに与える制約-続き

前回はNULL制約、そして一意制約について説明しました。もう1つ重要な制約は、外部キー制約です。サンプルに出てきた販売明細と商品の間では、外部キーフィールドである販売明細の商品IDフィールドには、主キーである商品の商品IDの値を入れることで、販売明細の1つのレコードにおいてどの商品を販売したのかを記録するということができました。この時、外部キーに、主キーにある値以外のものを入力できなくするのが外部キー制約です。例えば、次のようにテーブル定義の時に、FOREIGN KEYで外部キー制約を定義します。CONSTRAINTキーワードを使った書き方もありますが、省略することが多いと思われます。FREIGN KEYでの記述では、まず、商品IDフィールドが外部キー制約をかけると言うことを指定し、REFERENCES以降で、制約をかけたフィールドに入るべき値が用意されているテーブルのフィールドを記述します。ここでは1つのフィールドだけですが、複数のフィールドを指定することもできます。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID),
  FOREIGN KEY(商品ID) REFERENCES 商品(商品ID)
);

# DROP TABLE IF EXISTS 商品;
CREATE TABLE 商品 (
  商品ID INT AUTO_INCREMENT,
  商品名 VARCHAR(50),
  単価 INT,
  PRIMARY KEY(商品ID),
);

INSERT INTO 商品(商品名, 単価) VALUES('A', 500);
INSERT INTO 商品(商品名, 単価) VALUES('B', 600);

最後のINSERTを実行すると、商品IDが、1と2のレコードが作成されます。その状態で、販売明細に新しいレコードを作る時に、商品IDに4などの商品テーブルに存在しない数値を指定すると、エラーとなって挿入処理はキャンセルされます。こうして、「参照先が存在しない」と言う状況を作らないようにガードしてくれる機能がデータベースにあります。

mysql> insert into 販売明細 (納品書ID, 商品ID) values(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`販売明細`, CONSTRAINT `販売明細_ibfk_1` FOREIGN KEY (`商品ID`) REFERENCES `商品` (`商品ID`))

ただし、ここでNULLの扱いをきちんと把握しなければなりません。外部キー制約に関してはNULLは対象外となっていて、NULLの入力は可能です。NULLはもちろん、参照先で何かのレコードにマッチするわけはなく、いわば「未参照」の状態を記録する値でもあります。もし、NULLになるのがまずいのであれば、NOT NULLをここでの販売明細の商品IDの定義行に追加しないといけません。NOT NULLするかしないか、これはシステム自体の実装や用途、ワークフローに応じて決められることになるでしょう。納品書の明細行を、商品未選択の状態で用意して、そこからポップアップメニューで選択するような感じのUIだと、NOT NULLでない方が都合が良いでしょう。もっとも、その場合でも、NOT NULLを指定しつつ、「商品未選択」と言う特殊な商品を用意しておくという手法もあり得ます。どちらにしても、データベース以外のところでの決め事があって、それに従って進めると言うことになります。

外部キー制約の定義で、ON DELETE CASCADE、あるいはON UPDATE CASCADEと言う記述を追加する場合もあります。ON DELETE CASCADEがあると、主キーの側のレコードが削除されると、それを外部キーで参照している側のレコードも削除されるという動作です。商品マスターと明細では設定することは多分ないでしょう。商品がなくなると、売上の記録も無くなってしまうからです。一般に、商品マスターは販売しなくなっても残しておくものかと思われます。しかしながら、納品書と販売明細ではどうでしょうか。販売明細の納品書IDは外部キーとして納品書テーブルを参照し、これによって1つの納品書レコードと、複数の販売明細のレコードが関連づけられています。納品書を作ったけど、完全に間違えたなどの場合、納品書そのものを破棄したいかもしれません。その時、関連付けられている販売明細のレコードはどうなるかと言うと、単に納品書のレコードを削除しただけなら、販売明細のレコードは残ります。それでも、納品書IDの値を再利用つまり、後日別の納品書に利用するようなことをしない限りは、単なる参照先のないレコードが残るだけです。適切なユーザインタフェースを作ってあれば、それが目に触れることはありませんが、集計等では無視するような作りが必要になります。と言うことで、納品書を消したときに、販売明細のレコードも消えてくれる方が妥当かつ明快であるとも言えます。

ON UPDATE CASCADEは、主キーの側の値を変更したら、参照している外部キーの値も変更されるという設定です。一般に主キーは変更しないという前提があるのですが、データベースにはこのようなキーがあり、ある意味で、主キーの変更までもサポートしています。もし、この設定がないとして、主キーの値を変更したらどんな問題があるでしょう。例えば、商品ID=2を5に変えたとします。主キーなので、この変更後の値は他のレコードに存在しない値であることは保証されています。しかしながら、このテーブルを参照している外部キーはまだ2のままです。そして、外部キーのあるテーブルの該当するフィールドの値を全て、2から5に変更する必要があります。複数のレコードにわたって変更をする必要があります。また、それを複数のテーブルに渡って行う必要があるかもしれません。しかしながら、ON UPDATE CASCADEにより、それを自動的に行なってくれます。

これなら、主キーは変えてもいいじゃないかと言う話もあるかもしれません。もちろん、システム全体にわたって、ON UPDATE CASCADEがきちんと機能するならいいのですが、懸念されることは、通常システムの実装においては、主キーの値をどこかに一時的に保持することはよくあることです。例えば、Webアプリケーションで、あるレコードの値を変更するようなページがあるとしたら、データベースからデータを読み込み、ページを構築はもちろん行います。そして、画面上のデータを変更したときにサーバーに送り込んで更新をデータベースに反映させないといけません。その時、主キーの値を手がかりに、修正したレコードを特定した上で、更新処理を行います。そうなれば、ページを表示して、修正する前での間に、主キーの値が変化してしまうと、更新処理での元レコードの特定ができない、つまり特定してもその値ではなくなってしまうので、多分存在しないレコードを更新しようとして、エラーになってしまうという問題があります。データベースの都合上、確かにON UPDATE CASCADEは便利なのですが、システム全体のことを考えると原則として、主キーは変更しないというルールは守るのが良いでしょう。そして、主キーを変えるような作業を頻繁に行わないようなワークフローとデータベース設計を行い、どうしても変えないといけないような場合は、誰も利用しない時間帯にメンテナンスとして実施するか、あるいはUI上の処理を禁止するような仕組みを組み込んでおくなど、運用上、あるいは実装上の工夫は必要になります。

ユーザインタフェース側で、あり得ない値を設定するような仕組みを排除し、可能な限り検証をしたデータをサーバに送り込んでいるので、外部キー制約は不要であるといった考え方もあります。もちろん、そのような状況においては、外部キー制約に引っかかることは全くないと言えるかもしれません。それでも、制約は設定すべきという意見の方が合理的です。まず、ユーザインタフェースにバグがあった時、そのバグによって整合性が崩れたデータが入力されてしまうよりも、謎のエラーで(できればそうならないのがいいのですが)止まってしまってデータ入力されない方が、データベースとしては好ましいと言えるからです。また、外部からのデータをCSVファイル等で入力するような場合には、結果的に制約の検証は事前に確実に行われているかどうかは怪しいもので、おかしなデータがやってくるなんて言うのはザラにある話です。その場合、データを検証するプログラムを通してから読み込むと言うこともあるかもしれませんが、外部キーのフィールドについては外部キー制約の制限をデータベースに与えておいて、読み込みながら検証する方がより効率的であるのではないでしょうか。と言うことで、参照制約をサボる理由はあまりなく、設定しておくことでのメリットの方が多いので、早い段階から設定はしておくべきです。

2回に渡って制約の話を行いましたが、これらの制約をうまく利用することで、データベース内のデータが整合が取れた状態をキープできます。もっとも、何を持って整合が取れているのかと言うことは、きちんと把握が必要になります。どうなって欲しいのかということを設計では考慮しますが、どうなって欲しくないのかと言うことにも気を配ると、いろいろな解答が得られます。

この場を利用して、小田嶋隆先生がお亡くなりになられたことに対して、お悔やみ申し上げます。先生との関わりは、新卒で入った日経BPでの原稿の発注先でした。いくつかの会社に発注していたのですが、後楽園のマンションにあるオフィスに緊張しながら初めて伺ったのが懐かしい思い出です。その後、移転、さらにご自宅での執筆になるくらいまでお仕事をお願いしていたのですが、原稿できるまで張り付くと言うことが常になっていて、明け方に奥様の運転で自宅に送っていただいたき、途中で焼き肉を奢ってもらったことも懐かしい思い出です。その後、私は原稿を発注していた別の会社に転職することになり私もライター業を主業務にしていたこともあったのですが、編集者として張り付いていた時に、ライターとしての働きぶりを、かなりリアルに見せていただいたことが自分にとっての大きな糧になっていました。先生ありがとうございます、安らかにお眠りください。(その頃に原稿をお願いしていた人たちが、もう何人も鬼籍に入っているなぁ。)

[DBデザイン#40] データベースに与える制約

データベースを稼働させるには、色々な前提があるということはこれまでにも説明してきました。例えば、主キーという存在が必要であるということは、主キーとして認識しているフィールドのセットに対して、それらが重複するレコードが存在しないことが前提となります。これを、「後はよろしく」と言われても、実装が込み入るとか、バグがあったらどうするなど懸念点ばかりが思いつきます。しかしながら、データベースには、前提が必ず満たされているということを保証するための制約の機能があります。この機能を利用することで、前提が崩れるデータはデータベースには存在しないと保証できると思っていただいていいでしょう。データベースにバグはないのと思われるかもしれませんが、今時のデータベースソフトは長年の開発によって、こうした「前提の前提」のような発生しては大いに困ってしまうような箇所のバグはまずないと思ってOKです。

前提を満たさない状況にしようとしたときにエラーを出してデータの書き込みをしないというのが一般的な動作になりますが、システム側はそうしたデータベースのエラーを適切に処理する必要があります。システムが止まってはいけません。そしておそらく書き込もうとしたデータが正しくないわけで、そのことがわかるようなユーザインタフェースを作る必要があることも言うまでもありません。結果的に、そうしたユーザインタフェースをまともに作るとなると、「あらかじめ前提を満たすかどうかをチェックする」と言うことに究極的にはなってしまって、それじゃあ別にデータベースに制約機能なくてもいいじゃないかと思ってしまうかもしれません。このことはむしろ、データベースとUI側の両方に制約をかけることで、より確実にシステムが動作すると言うことで前向きに捉えるべきでしょう。いずれにしても、データベースが制約をかけているから「大丈夫」と放置するのではなく、きちんとシステム側の特にUIにもきちんとした対処は必要であることは認識すべき事柄です。

データベースのフィールドは、NULLと言う特殊な値を取ることができますが、まず、「NULLにはならない」と言う制約が付与できます。CREATE TABLE文のフィールド定義で、NOT NULLと言うキーワードを付与するのが一般的です。NULLの書き込みは意図的なことをしない限りは通常はなく、NOT NULLはフィールドの初期値に関して考えておくことになるのではないでしょうか。一般にはフィールドの初期値がNULLになりますが、この制約をつけても初期値はNULLのままです。つまり、既定値は許されない状態になるので、必ず値を与えるか、フィールドの定義部分で「個数 INT NOT NULL DEFAULT 1,」などとして、DEFAULTに続いて値を指定するか、何らかの対処が必要になります。

そして、主キーフィールド等で利用できる「一意制約」の機能もあります。この機能はインデックスの仕組みを利用するのが一般的です。インデックスは、フィールドのデータがどのレコードにあるのかを特定できる情報をデータベース側で自動的に作ってくれる機能です。その実現方法も興味深いところですが、完全にブラックボックス的に動いてくれるので、要するに「検索結果を早く提供してくれる機能」と割り切って考えれば良いでしょう。例えば、文字列を検索する場合、インデックスがないとしたら、全データを最初から最後までチェックしないといけません。インデックスがあると、その文字列があるレコードは、これとこれと…と特定してくれるので、当然ながら検索は速くなります。現在のコンピュータ環境では、ちょっとしたデータであればあまりインデックスの効果はないと思っている方もいらっしゃると思いますが、長いデータや大量のデータでは十分にインデックスによるスピードアップは期待できます。少量のデータだからといってインデックスがあれば遅くなるようなことはないので、検索に使うフィールドにはインデックスを作っておくのは基本中の基本になります。仮にインデックスを作らなくてもサクサク動いているとしても、インデックスのない検索はデータに比例して時間がかかるようになるため、いつか耐えられないくらいになってしまう可能性を秘めています。問題がないと思っても将来に備えてインデックスを作っておくべきです。

このインデックスは、値の重複を許す場合と、許さない場合があり、インデックス作成時に指定をします。もちろん、前者は検索の高速化が主な用途ですが、後者は主キーに設定するために利用できます。そして、フィールドは、1つだけではなく、フィールドの組み合わせに対してもインデックスは作成できます。例えば、{商品名, 顧客名, 個数}に対して重複を許さないインデックスを作成すると、この3つのフィールドが全部同じレコードは作成できなくなります。商品名が同じものがいくつもあるのは構いません。ちなみに、レコードを追加したりフィールドを変更した時に、インデックスは更新されるのですが、インデックスが正しく更新されないと、追加や変更の処理もキャンセルされてしまいます。インデックスはテーブルのデータと連携をしていて、「制約」として動作すると言うわけです。もちろん、データベースからはエラーがレポートされるので、適切に処理をしなければなりません。

MySQLを例にとって説明しますが、例えば、次のようなテーブル定義があったとします。とりあえず、販売明細IDを主キーとし、連番を入力することにします。記述方法の1つとして、( ) 内の最後にPRIMARY KEYとしてどのフィールドが主キーかを指定しています。これにより、自動的に重複を許さないインデックスが作成され、フィールド「販売明細ID」の値と連携することになります。もちろん、ある数値を与えれば、どのレコードなのかと言うことを瞬時に返すインデックスの意味も持ちますが、販売明細IDに既に入力してある値を新たに挿入しようとしてもエラーを返す返す一意制約のためのインデックとしても機能します。ちなみに、これによりPRIMARYと言う名前のインデックスが作成されます。インデックスは、テーブル名とフィールド名の情報を持ち、テーブルごとに管理されるので、結果的にデータベース内にPRIMARYと言う名前のインデックスがいくつも作られることになるでしょう。

# DROP TABLE IF EXISTS 販売明細;
CREATE TABLE 販売明細 (
  販売明細ID INT AUTO_INCREMENT,
  納品書ID INT NOT NULL,
  商品ID INT,
  個数 INT NOT NULL DEFAULT 1,
  PRIMARY KEY(販売明細ID)
);

MySQLでは主キーを設定すると自動的にインデックスが作られるので、例えば、「CREATE UNIQUE INDEX 販売明細_販売明細ID ON 販売明細 (販売明細ID);」のようなSQL文で新たにインデックスを作成する必要はありません。ちなみに、重複を許さないという特性は、UNIQUEと言うキーワードで指定をします。INDEXの後はインデックス名、そして、ONの後に「テーブル名(フィールド名)」と言う記述を行います。

なお、ここで、モデルの上で、同一の納品書の明細には、1つの商品は1行だけ登場するか、全く登場しないかどのどちらかであるという制約があったとします。そうすると、次のような重複を許さないインデックスを作成しておくことで、主キーの制約をかけることができます。おそらく、納品書のUIで、明細行を追加し(その時には既に納品書IDは確定しているので入力済みになる)、商品を選択したときに既に同じ商品がその納品書の明細に存在していれば、制約違反のエラーが出てきます。それを避けるために、商品の選択肢に既に入力してある商品が出てこないようにするのが1つの方法ですが、そこまでの対処をUIで行うと、UI側とデータベース側の2重対処をしたことになります。そのことが冗長なのか、それとも確実な動作を支える実装なのかは意見が別れるところですが、設計とはそういう場合にどうするのかと言うこことを決める作業に他なりません。そのようなことを早い段階から検討できるようになりましょう。

CREATE UNIQUE INDEX 販売明細_主キー ON 販売明細 (納品書ID, 商品ID);

なお、UNIQUEなインデックスを設定するだけだと、NULLは許可されている状態になります。前述のようなユーザインタフェースだと、商品IDがNULLの期間が発生するので、NULL許可はワークフロー上は必要であるとも言えます。ですが、いずれにしても、NULLにならないようにしたいということであれば、テーブル定義にNOT NULLを指定します。MySQLのUNIQUE INDEXは、例えばこの場合だと、納品書=1、商品ID=NULLというレコードは複数存在できるようになっています。つまり、NULLの値がある場合には、一意性の評価はしないと見ることができます。これは、NULL = NULLはFALSEであると評価できるところから来ており、NULLは「どのレコードも異なる」と判定されてしまいます。つまり、主キーフィールドの1つでもNULLがあれば、見かけは同一のレコードも作成できてしまいます。もちろん、主キーフィールドはNULLにならないと言うのが「原則」という考え方もできるのですが、ワークフロー上NULLである期間を許すとなると、落とし所は難しくなります。もちろん、現実の案件では、色々な要求に対する実現手法や実現度合いを見計らいトレードオフを行なって最終決定するしかないでしょう。

さらに「外部キー制約」のこともあるのですが、ちょっと長くなったので、これは次回に説明しましょう。

[DBデザイン#39] クラス図でビューを記述する方法

ER図やクラス図で、テーブル設計を記述することはよく行われています。その結果をもとに、CREATE TABLE文を駆使して、テーブル定義のためのSQL文を記述し、データベースに読み込ませて使えるようにします。文法的な間違いは随時直すとして、設計の変更などが発生してフィールドを追加するなど、データベースの変更は開発中には付き物です。ということで、モデルとテーブル定義のSQL文が分離した瞬間、両方をメンテナンスするということになり、変更を適用するための記述はSQL側になるので、やはりモデルの修正はおろそかになってしまいます。常に、モデルから生成できればいいのですが、データベース特有の記述を毎回手で治すような事態だと、別々にしているのと手間は変わりません。ただ、最近のWebアプリケーションフレームワークは、モデルをコードで記述することで、自動的にデータベースの生成や、あるいは設計が変わった時のデータの移行なども含めてやってくれるので、その意味ではモデルベースではありますが、ERやクラス図と、モデルのコードの変換や対応付というまた同じ問題に直面します。この辺り、なかなか良い方法はないというか、労力をかけないで、ドキュメントをメンテナンスするということへの工夫をとにかく頑張って凝らさないといけないところではあります。ERやクラス図を最初に作り、その後、SQLやコードなど実装が進むと、ER/クラス図が放置されるのが常かもしれませんが、開発が落ち着いたら、実情と合うようにモデルを修正しておくようにしたいところです。

実際に開発を始めると、テーブルだけでなく、ビューも使います。テーブルのやりとりだけだと、何度もアクセスが必要になります。そこで、SELECT文でいくつかのテーブルを結合した結果が欲しくなるのですが、ソースコードにSELECT文を展開する場合もあるかもしれませんが、ビューを定義して、あたかも「1つのテーブル」のように扱える仕組みは便利です。1つのビューをいくつかの目的に使い回しもできます。ビューを最初から用意するというのが1つの理想的な状況ですが、現実には開発をしながらビューを追加することが多いと思われます。つまり、実際にユーザインタフェースのコードを記述するときに、「このページにはこのフィールドがいるけど…」と検討を始めて、「このテーブルと、あのテーブルと、そちらのテーブルを結合しないといけないな」などを考えて、CREATE VIEW AS SELECT …などと書いていることが多いと思われます。実際にUIのコードと対峙しないと、必要なビューというのが見えてこないというか、気合を入れて考える気がしないのではないでしょうか。そして、フィールドが足りないとビューの定義を書き直して、DROP VIEWしてまたCREATE VIEWをするという手順でメンテナンスを繰り返します。テーブルを変更するときはALTER TABLEコマンドを使わないと、DROP TABLEしたらテーブルが消えてしまいます。ですが、ビューは、一時的にはどこかに結果はあるとしても、設計情報だけが必須のものなので、DROPしてCREATEできる点では気軽に変更できます。

気軽に作成した結果、何が起こるかというと、ありがちなのは似たようなビューをいくつも作っていたり、名前の付け方が統一されていなかったりといったことが起こりがちです。動いていればいいという考え方もありますが、できれば、後からのメンテナンスをやりやすいように構成されていたいところです。ということで、ビューも、テーブル設計に続いて設計内容として記述できればと思ってしまいます。ER図作成ソフトの中にはER図にビューという要素をエンティティの1つとして配置できるものもありますが、結果的に構造がテーブルと同じということで、テーブルの代わりに置けなくもないという感じの機能です。むしろ、ER図ではビューは記述しないのが一般的と言えるかと思われますが、実のところ、この理由を記載した記事などはみたことがありません。一番考えられるのは、ビューの設計は、SQL文でテキストで記述してしまうので、それを確認するのがとにかく確実で早いということがあるからではないかということです。当然、そこにはビューの設計情報が全部あり、テキストなので検索も確実に引っ掛かります。しかしながら、テキストファイルに大量にビューの定義があると、前に説明したように、同じような別のビューを定義してしまうということにもつながります。開発で頭が爆発しそうな時でも、冷静に既存のビューを参照できるようにするために、クラス図にビューも記述するのが良いのではないかと考えて若干、実践をしてみましたが、ただ、やり始めてあまり時間が経過していないので、効果の程はまだ未知数です。前回に出てきたテーブル設計のクラス図が上半分の6つのクラスにあるものと同様です。新たに、ビューとして2つ定義したことを書き加えました。ビューは下の2つです。まず、ビューの名前には、<<view>>というステレオタイプを記述し、通常のテーブルと区別できるようにしました。ついでに色分けもしてあります。

ビューが定義されている状態の何を知りたいのかということですが、まずは、どのテーブルあるいはビューをもとにしているのかという情報かと思います。つまり、FROM句に並ぶテーブルです。販売明細テーブルの内容に、商品の情報をくっつけた「販売情報_商品付」というビューが定義されています。このビューは、販売情報と商品の2つのテーブルをもとに作られていることが分かります。左側のビューとも繋がっていますが、この線は、左側のビューが「販売情報_商品付」をもとに作られているものですので、ビューとビューを結ぶ線はどちらが大元なのかをチェックする必要があります。「販売情報_商品付」は、基本的に「販売明細」にあるレコードが、そのままビューの側にも存在するということを期待しています。つまり販売明細テーブルの拡張版なのです。そこで、黒いダイア付きの線、コンポジションを利用して、「販売情報_商品付」が販売明細と同一のレコード構造、つまり、販売明細の1レコードが、「販売情報_商品付」の1レコードになることを示唆しています。コンポジションの意味から若干外れるのですが、販売明細の1つ1つのレコードをパーツとして、それをそのまま組み立てているというイメージで、当たらずとも遠からずかと思われます。そして、商品からの線は、「販売情報_商品付」ビューが商品とも結合されていることを意味してますが、通常の結合でない場合、ここでは左結合の場合は、<<left-join>>というステレオタイプをつけておきます。ここで、コンポジションに当たる販売管理に対して、商品が左結合するということになります。そして、属性のところには存在するフィールド名を記述します。主キーを明示したい場合は、ステレオタイプの<<PK>>などを記述しても良いでしょう。そして、最後の「金額」フィールドは計算フィールドです。計算フィールドの名前の前に / が付けられていますが、派生と呼ばれる記号で、他のフィールドの値から導出可能であることを示しています。もちろん、この場合はさらに同一レコードの値から導出可能であるので、派生の記号のまさに使い所です。式はどうするか、ここではメモに記述していますが、左側のビューのように、制約として記述する方法もあります。長くなると、メモの方がみやすくなるかもしれません。最も、ここに式をあまりに細々と記述してみづらくなるのであれば、式はいっそのこと省略して、CREATE VIEWを参照するということでも良いでしょう。

「納品書_合計付」ビューは納品書の1レコードが、ビューの1レコードになることを期待しているので、納品書テーブルからの線はコンポジションとなります。そして、顧客名などを取り出したいので、顧客テーブルを左結合します。しかしながら、ここで、明細の合計を取りたいのですが、「販売情報_商品付」ビューから値を取ってきて合計を取ります。まさに、「販売情報_商品付」ビューの結果を集約するので、白いダイアマークのアグリゲーションが適切です。ただ、この時、どのフィールドでグループ化するのかもステレオタイプ<<group-by>>で示しておきます。そして、合計金額の派生フィールドが、SUM関数を使った式を持っているので、そこに関連した明細の「金額」フィールドの合計の値が求められることになります。

以上のビューを実際のSQL文で記述するとこんな感じです。なお、このままのSQL文は通らないかもしれません。アグリゲーションがある場合、アグリゲーションに関係ないフィールドをSELECT句に入れてはいけないというルールで運用されている場合が最近は増えています。

CREATE VIEW 販売情報_商品付 AS
SELECT 販売明細ID, 納品書ID, 商品ID, 商品名, 単価, 個数,
  単価 * 個数 AS 個数
FROM 販売明細
  LEFT JOIN 商品 ON 販売明細.商品ID = 商品.商品ID

CREATE VIEW 納品書_合計付 AS
SELECT 納品書ID, 顧客ID, 顧客名, 販売日,
  SUM(金額) AS 合計金額,
  SUM(金額) * 0.1 AS 消費税額,
  SUM(金額) * 1.1 AS 総計
FROM 納品書
  LEFT JOIN 顧客 ON 納品書.顧客ID = 顧客.顧客ID
  INNER JOIN 販売情報_商品付 ON 納品書.納品書ID = 販売情報_商品付.納品書ID
GROUP-BY 納品書.納品書ID

ビューとビューの間の線が、コンポジションでもアグリゲーションでもない場合は、どちらが元になっているのか分かりにくいので、この例では記述がありませんが、もとになっている側に矢印の矢尻、つまり、導出可能の記号を付けておくのが良いでしょう。最も、矢尻はコンポジションやアグリゲーションの記号の反対側なので、常につけるでもいいような気がします。

図に、テーブル間結合の式は必要かというと、その式は、大まかにはすでにテーブル間結合の図で記述されています。どのテーブルを持ってくるのかということから、関連のための条件式はほぼ決まります。最も、リレーションシップを検索のように使う場合もあるので、そのようなテーブル間の関係性として定義していないリレーションシップでの結合を行う場合は、結合の線に対してメモで条件を記述すると良いでしょう。

実際案件で、ビューも一緒に記述してみました。やはりというか、線だらけになってしまいました。なので、ビューへの結合線は薄い色にするなど、本来のテーブル間の関連付けの線を目立つようにしています。ですが、ほとんど同じビューが2つあって(これは意図的なんですが)、そういった定義でちょっとだけ違うのがどこなのかは一目瞭然になります。ただ、やはりというか、1つ1つのビューの用途については、箱の存在だけではピンとは来ません。そこで、メモでビューの用途や意図を記述をする必要性はあります。最も、これはテーブルについても同様かもしれません。

クラス図にビューを記述することは、前回紹介したAmbler先生のページにもありますが、今回紹介した記述方法はいくつかルールを追加しています。ビューも設計のうちですし、管理したいということもあるかと思いますが、テーブルの設計と併せて記述するのはあまり支持してもらえないかもしれません。みづらくなるだけだという感想を持たれるかもしれません。一方で、離れていた開発から戻った時や、メンテナンス開発の時などは状況の把握には有効でしょう。結局、その案件の中では、テーブルとビューを両方記述しましたが、両方記述した図と、テーブルだけを記述した図の2通りの出力を出すことにしました。

[DBデザイン#39] クラス図で設計を書く

UMLのクラス図は、ER図とよく似ています。ER図ではテーブルを1つのボックスで記述しますが、テーブルをクラスであるとみなせば、UMLのクラス図でのクラスでの記述も可能です。クラス図の方が汎用的であるので、いろいろな書きようがあるため、クラス図で記述する場合のルールは自分自身やあるいはチームとして決めておく必要があります。クラス図での書き方の提案としては、早い時期からあるものとしては、書籍の「
Refactoring Databases: Evolutionary Database Design」の筆者の一人による「A UML Profile for Data Modeling (Scott W. Ambler)」があります。かなり広範囲にわたってルールは作られているのですが、逆に言えば、どこまでを自分で利用するときに記述として取り入れるかを取捨選択するガイドになると思われます。

ところで、astha*professionalは、クラス図とER図を相互に変換することができます。まず、その機能を見てみましょう。前回に示したER図からクラス図を生成すると次のようになりました。

まず、テーブル名がクラス名になります。そして、フィールド定義が、クラスの属性になります。クラスの中の操作については、全部空欄になります。ER図ではクラスの操作に対応する情報はないので、当然かと思いますが、見栄えの上からは操作区画は非表示にするのが良いでしょう。属性の型については非表示になっていますが、定義した結果は背後で記録しています。ただ、図の上で型は見えてなくてもいいのであれば、全部intのままでもいいかもしれません。

そして、属性の中に、<<PK>> というステレオタイプが設定されたものがあります。もちろん、これが主キーを示します。ただ、これだと外部キーの情報が欠けるので、その対処は後で説明しましょう。テーブル間の関連は線で引かれていますが、非依存の関連は<<Non-Indentifying>>、依存は<<Indentifying>>というステレオタイプがついています。そして、非依存は単なる線であって一般的な関連、依存は黒いダイアがあってコンポジションとなっています。依存と非依存の区別が2重な感じがありますが、明確にしたかったのでしょう。納品書の構成要素として販売明細の集合は必須であるということから、確かに、依存関係があれば、コンポジションで表現することは妥当です。

そして、カラスの足跡は残念ながらクラス図では記述ルールにないので、それぞれ、線の端に、「1」や「0..*」などで、個数を示します。つまり、「顧客」と「納品書」の関係は、1対多であり、1つの顧客のレコードに対して、納品書の数は0〜複数個を取り得るということを示しています。この記述は「多重度」と呼ばれます。ここで、線の横に「-顧客」と記載されたものは、「納品書クラスで、顧客を参照できる」ということを意味しています。この外に出た「顧客」がクラス図の定義からは属性の1つになるので(astha*では属性とは別のプロパティになる)ボックスの中に、外部キーが登場しないという状況になります。これはER図上で明示されていたものが消えてしまうというようにしか見えないですし、結合処理の時になんというフィールドを照合するのかという具体性がやや欠けています。もちろん、主キー名と外部キー名を同じにするというルールがあれば、納品書テーブルに顧客IDフィールドがあるのだろうなというのはわかりますが、それは記述をした方が良いのではないかと考えます。

そこで、元々あった外部キーのフィールドも、属性としてボックスの中に定義して、<<FK>>というステレオタイプを設定することで、ボックス内に定義したフィールドが見えることになります。依存と非依存の情報は一旦ここでは消してありますが、黒いダイアのマークのコンポジションは、テーブルのライフサイクルを考える上では重要な情報になるので、必要な箇所にはつけておくべきです。ただ、関連の線の上に<<Indentifying>>というステレオタイプを記述するのは、図がちょっとうるさい感じになるので書かなくてもいいのではと思います。以下のように、クラス図を変更してみました。

ここで、「顧客」と「納品書」の間の線を見てください。関連付けの線を引いた場合、「0」や「0..*」などの多重度を間違いなく記述するのは必須です。この情報が欠けると設計情報としては未完成と判断されます。ここでは納品書と販売明細の関係において1..*すなわち、販売明細がないということはないという設計情報があります。多くの場合0..*ではあってもなくてもいいということですが、1..*だとそちら側に対応するレコードが必ず1個以上あるようにしないといけません。つまり、納品書のレコードを作ったときに、販売明細のレコードは少なくとも1つは作っておく必要があるということを設計上示唆しているとも言えますし、明細のない納品書はあり得ないということも示唆しています。

そして、照合するフィールドを、線の横に「-顧客ID」などと記述をします。この時、納品書側にある「顧客ID」は、顧客テーブルに定義した主キーの顧客IDと同じものを意味します。実は、ここで、同じものを2つ定義しているのですが、ここからテーブル定義のSQLを自動生成するのでなければ問題はないかと思われます。クラス図では、照合するフィールドを、線の横に書くのですが、「相手側に書く」のがルールです。ちょっとわかりにくいというか、最初は慣れないかと思いますが、ルールだとして気をつけていればすぐに慣れます。なお、このように、双方とも「顧客ID」フィールドであるような場合は、例えば、納品書と販売明細のように、一方だけ書いておくことでも判別はできます。あるいは、PKとFKのステレオタイプから、どのフィールドで照合しているかは明確であるとも言えるので、線の横に照合するフィールド名を記述することを省略するのもあり得るでしょう。その辺りは、好みや設計の複雑さなどと折り合いをつけて自分でルールを決めてしまっていいでしょう。

なお、複雑なリレーションシップがあるような場合にどう記述するのかと言えば、線の隣のフィールド記述だけではわからないので、自由記述が可能なメモを書いて、線の部分を参照しておくと良いでしょう。クラス図にそういう機能がないのかといえば、実はあります。関連の線に対して「制約」という記述が書けます。原則はOCLという言語による式を書くのですが、作図だけならコンパイルするわけではないので、その意味では自由に記述できます。その記述結果は、線の上に { } で囲われて表示されます。もちろんこの方法でも良いのですが、メモで書く方が手軽ということもあって、追加情報はメモをともかく活用するというのがポイントになります。

キーフィールドについては、PKとFKだけを書いていますが、ここでサロゲートキー、つまり連番の自動入力を使うなどして入力したデータとは直接関係ない主キーフィールドを使う場合も考慮します。サロゲートキーは、参照制約などの説明をしてから改めて議論しますが、この例だと例えば、販売明細の販売明細IDは、サロゲートキーで、本来のモデル上の主キーあるいは候補キーとしては {納品書ID, 商品ID, 個数} であったとします。この時、納品書IDと商品IDは、<<FK>>だけでなく、<<CK>>というステレオタイプを付与し、個数にも<<CK>>をステレオタイプに追加します。ステレオタイプは複数設定でき、属性名の前に併記されます。この記述があれば、モデル上の主キーは<<CK>>がついたものだけど、サロゲートキーによる主キーが<<PK>>で示されているということがわかります。もっとも、Ambler先生のサイトでは、サロゲートキーについては<<sarrogate>>というステレオタイプをつけるということを提唱していますので、明示したい場合にはこのステレオタイプをつけておくと良いでしょう。

クラス図で記述した場合のメリットは、次の図のようなオブジェクト図を記述できることです。クラス図は抽象度が高い図であり、コンパクトに設計情報はまとまっていますが、一方で具体的にきちんと正しいデータが格納されるのかということは頭の中で追っていくことになります。ここで例で出しているような納品書などの例ではそれもそんなに難しくはありませんが、もっと複雑なデータ構造の場合は、データがどのように格納され、変化するのかを具体的に記述してみて、確認する必要が出てきます。

オブジェクト図は、クラスに定義した結果をもとに1つのボックスを1つのオブジェクトとして記述するものです。データベースの場合、1つのボックスが1つのレコードとなります。もちろん、今までに主張してきたように「表にする」でもいいのですが、表だとレコード間の関連付けをビジュアル化しづらいこともあります。レコード間の関連を明示するのはオブジェクト図の方が適しています。一方、オブジェクト図は場所を取ります。ある程度まとまった数のレコードを作っていくとかなり大きな図になり、それはそれでみづらいです。ですが、例えば、ある種のワークフローがスタートして、途中、いろいろとデータが変わって、最後に終わるというところまでを、具体的なデータを当てはめて検証するということができるので、設計の妥当性を確認するツールとしては役に立つものです。

次回は、ビューをモデル化するのかという議論と、SQLのテーブル定義やビュー定義と設計の関係を説明しましょう。

[DBデザイン#38] ER図のニーズ

今までは、「ともかく表にしよう」ということで、表を基礎にして、データがどのように振る舞うかを見てきました。表は非常に便利で、まず、データを掲示できる点で、具体性があります。さらに、どんなフィールドがあるかということも記述できるので設計情報も記録できます。完全ではないとしても、これで十分と思ってしまうかもしれません。しかしながら、実際の開発で使うような表(正確には「テーブル」です)にあるフィールドは非常に大量にあります。最近は大画面ディスプレイが一般的なので、少々フィールド数が多くてもそれなりに一覧性はありますが、やはり横スクロールが多くなると見落としがちですし、当然ながら俯瞰して見ることはできません。また、実データを集める必要はあるものの、たくさんのデータを集めると、それはそれで縦スクロールが必要になりやはり俯瞰性は落ちるということになります。

システムの開発に携わる人たちは、もっとコンパクトに、どんなテーブルが用意されているのかということを知りたいわけです。そのために、設計結果をモデルとして記述することになります。代表的な図はERですが、その前に、今までにどういう設計情報が出てきたでしょうか? まとめると次のようになります。

  • 表を特定するための表の名前
  • どんな名前のフィールドがあるのか
  • どのフィールドが主キーなのか
  • どのフィールドが外部キーなのか
  • 表同士の関連性はあるのかないのか
  • あるとしたら、どのフィールドを照合するのか
  • 1対多の関係はどこにあるのか

こうした情報をコンパクトに示せれば、モデルとして有益になります。例えば、架空の会社の例をER図に記載するとしたら、次のようになります。なお、ER図にもいろいろなルールがありますが、ここではIE(Information Engineering)記法と言われるものを紹介します。

ボックス1つ1つが表、すなわちテーブルになります。ボックスの上に記載されたのがテーブル名となります。そして、ボックス内部は線で区切られた上部と下部に分かれていて、上部には主キー、下部には残りのキーを記載します。外部キーとなるものはフィールド名の右に(FK)と書かれています。つまり、箱にフィールド名を記載することで、表の骨格を示すことができています。もちろん、実データはありませんが、このER図を読み解く時には、ボックスを表としてみなして、記載されていませんけど、いろんなデータがそこにずらずらとレコードとして存在するということを想像するのは基本です。なお、データベースではフィールドの型を指定する必要がある場合が一般的なのですが、記載可能なER図もあるものの、コンパクトに表現したい場合は省略されている方が都合が良いので、図の中には記載されない場合が多いでしょう。

そして、テーブル間に線が引かれていて、独特のデザインになっています。まず、線があるということは「関連性がある」ということを意味しています。もう少し詰めた言い方をすれば、「直接的な関連性がある」ということです。ともかく、ある表の内容と、別の表の内容が関連している場合には、線を引きます。販売明細と商品が関連していますが、販売明細側ではどの商品への明細なのかを記録するためには商品IDフィールドの値だけを覚えています。ここで、商品名や、図にはありませんが、単価など商品にまつわるざまざまな情報を明細で必要になった場合、販売明細のテーブルと、商品のテーブルを合成、すなわち結合をして、この場合は販売明細に対して商品の情報を商品テーブルから供給するような動作ができたことを思い出してください。要するに「商品マスター」なのですが、いずれにしても、商品名などの実データは、販売明細には存在せず、商品テーブルから取ってくることができるということを示しているのが関連です。

関連の線の一方は単なる線ですが、全てがもう一方の端は3つに分かれています。この形から「カラスの足跡」のような言い方がされるのですが、これは、1対多の関係であることを示しています。この表記が、非常に分かりやすく直感的であることが、ER図を利用する理由の1つであります。いろいろな表を作った時、どのテーブルと、どのテーブルが関連性があり、1対多の関係がどうなっているのか(つまり、どっちが多なのか)ということが明確にわかるからです。関連の線にはそれ以外に、線に垂直の短い棒と、◯があります。この短い棒は数字の1を示していて、◯は0を意味しています。ですが、1と0と読むのではなく、◯は対応するレコードとして、0個〜複数個あり得るということを記載しています。また、1個以上や、0ないしは1個のような書き方もできる場合があり、個数自体をきちんと記述しようとする仕組みも持っています。例えば、関連性の双方が、1対1以上のような表記の場合、多の側に対応するレコードがないということはあり得ないということを設計上想定している可能性が高く、データベースの設計というよりもプログラム等の実装においてそのことを考慮して、開発を進めないといけない場合も出てきます。このことに限らず、データベースの設計は単にデータモデルということだけでなく、システム全体がどのように動くのかを期待する場面も含めて記述しているとも言えます。

そして、ER図を見ながら、実際に納品書を作って、製造部門が製造をして…といったデータの発生や変更などを頭の中に巡らせるのがまずはこの図を読み解く基本です。その時、あるデータを別のテーブルに保存するとしたら、おそらく、1対多、つまり、あるデータに対して、複数の別のテーブルのレコードが関連して、そのためには、外部キーのこのフィールドに、この値を入れて…ということが想像できるということが期待されています。そして、忘れている1対多の関係がないかとか、ここは1対多ではなく、多対多ではないかなど、テーブル間の関連性の検証を一生懸命行います。結果的に各テーブルでの1レコードが何に相当するのかということの把握も行うことになり、1レコードという素なデータの存在意味を加味した上で、関連付けによって現実の世界の表現に至っているかという点をこの図を見ながら検証するのです。

あと、関連性の線に点線と実践があります。前者は非依存、後者は依存と呼ばれています。関連が依存の場合、依存性がある方のテーブルは、角丸のデザインで表記されます。点線で記述する非依存の関連は、関連のないレコードがあっても良いということを意味しています。一方、依存するとは、関連性のないレコードは存在しないということを意味しています。関連づけは、主キーと外部キーで行なっています。つまり、納品書テーブルの納品書IDと、販売明細テーブルの納品書IDで関連づけを行なっています。これらが同一のデータが、納品書とおそらく複数個存在しそうな明細のレコードの関連づけの手がかりになっています。ここで、単に関連づけを行うだけなら、外部キーであれば良く、販売明細テーブルの納品書IDのフィールドがNULLであれば、対応する納品書はないレコードになります。多分、そういうレコードは意味がない、あるいはない方が望ましいということを考えれば、依存があるようにしておくことで、そういうレコードの作成時にエラーが出て作成されなくなります。つまり、データベース自身が設計に反する状態にならないように排除してくれているのです。外部キーを主キーに入れることによって、その値がNULLでないことが期待されます。そして、通常は対応する主キーに存在する値、つまり外部キーに入力して関連づけが可能な値を、外部キーに入れることになるでしょう。その結果、依存関係が確実になりということで、設計時に依存か非依存かをきちんと把握するという意味で、ER図は設計を記述できるということになります。ただ、この依存性と外部キーが主キーになってしまう点については異論がある方もいらっしゃるかと思いますが、ER図ではともかくそういうルールで記述されます。外部キーが主キーになるのを嫌う場合には、全ての関連を非依存で記述して、自分でキーフィールドの定義などを管理すれば良いでしょう。もちろん、依存がある場合にはそれなりの対処はされると思います。

こうしてER図を書けば、実はデータベースに理解可能なテーブル定義のためのSQL文を作成可能です。言い換えれば、テーブル作成のSQL文を、図として表現したのがER図でもあると言えるでしょう。ツールでSQL文を生成できるのは一般的になっています。もちろん、特定のデータベースソフト向けのオプションなどは自分で手で直すことになりますが、ともかくフィールドを書き並べるといった単純作業を一気にやってくれる機能と思えば便利です。また、論理設計と物理設計という2面的な機能があって、画面では日本語などの日常会話で使っている単語で「商品名」などと示し、実際のデータベース上では「prod_name」みたいな変数名のようなキーワードを使うという仕組みも持っているのが一般的です。

ということで、ER図を書きましょうということになるかというと、不便な場面もあります。まず、実際の開発で出てくる表にはフィールドが大量にあって、そのままでは縦に長い表が作られてしまいます。最も、私が使っているastha*professionalだと、フィールドのエリアを非表示にできるので、テーブル名と主キーだけを表示した図にしておくことも可能です。仕様書に記載する時にはフィールド名と主キーだけを表示したボックスにして、フィールドの定義は表にするくらいがちょうど見やすいです。やはり関連図でもあるERは一覧性が欲しいです。全フィールドを収めるとかなりでかい紙に印刷しないといけなくなりますが、フィールドを主キーだけにすると、比較的巨大な設計でも、A41枚に収まってくれるかもしれません。よく、仕様書にフィールドの表があっても無意味という話もありますが、ER図でフィールドを省略したら、やはりあったほうが良く、フィールドの存在意図や使用方法についての注釈など、ワープロの表に記載する方がよほど自由に記載できて便利です。そういう仕様書の書き方をすれば、フィールドの表には意味があります。

ER図を否定する訳ではありませんが、次回はクラス図を使ってモデル記述する方法について紹介してみたいと思います。

[DBデザイン#37] NULLについて

この話はもう少し前にしたほうがよかったですね。何かと話題のNULLについてです。

まず、NULLあるいはそれに近い概念はプログラミング言語で扱われていて、それはそれで必要だから実装されていることもあって是非の議論はされていませんが、データベースでは是非論は検索すればいろいろ出てくるかと思います。

ですが、そのコアな議論の前に、日本での発音についてまずは指摘したいです。Wikipediaによると、「ヌル」あるいは「ナル」と呼ばれているとされていますが、実際にはヌルと呼ぶ人が多いです。アメリカ人の発音を聞くと、「ナラ」って感じで、ラは正確にはLの発音だけでからナのAが影響してラとも聞こえます。ただし、日本語的に妥協するとラよりルですね。nu*は、nutsとか、numberとか、大体ナではないかと思います。なのに、日本人だけローマ字読みしてヌと言ってしまっている。もちろん、日本人は正確には発音できないからなんでもいいということも言えなくもないのですが、ヌルはちょっと遠すぎませんか、せめてナルかナラあたりで発音しておくのがいいかと思います。

データベースでは、フィールドを定義するとき、型を指定するのが一般的ですが、どんな型であっても、その型にはまった値以外にNULLという値を設定できるようになっています。また、一般には初期値はNULLになっています。ここで、NULLという値は取らないという設定、そして実際にNULLを入力しようとするとエラーを出して入力できないようにするという機能もあり、設計者が自由にNULLに対する挙動はセットアップできるのが今時のデータベースです。ただ、メンテナンス開発などでは、「ここはNULLにならないようにした!」と思っても、データベースの設計変更はしてはならないという掟があって、そのままの状態でデータベースを使って改変しないといけないかもしれあません。

NULLは論理式の中に存在すると、普通のデータとは挙動が異なります。式の中に1つでもNULLがあると、結果はNULLになると考えれば良いでしょう。NULL自体は論理値として判定するとFALSEになります。すると、FALSE = NULL という論理式の結果はNULLが入っているので自動的にNULLになります。右辺はNULLだからFALSEと思ってしまうと、FALSE = FALSEの結果がFALSEになってしまって、ちょっとおかしいなということになります。簡単な式なので、まあ普通はわかるだろうと思いがちですが、複雑な式の一部だとなかなかわかりにくいです。このように、式の判定にNULLが絡むと思った通りの結果にならないということもあって、ある値がNULLかどうか、NULLでないかを判定するための演算子があります。「field IS NULL」「field IS NOT NULL」のように記述します。右端のNULLが値のように見えますが、正確にはIS NULLあるいはIS NOT NULLという演算子ということになっています。理想的にはNULLかどうかの判定はこれらの演算子で行い、NULLでない値だけで論理式を組み立てているくらいの対応が望まれます。別の人が作った論理式を理解しないといけないような場合で、NULLを加味しないといけない場合と、しなくていい場合を考えれば、後者の方がよほど楽で把握にミスが入りづらいと言えます。

そういうことで、まず、重要なことは、データベースシステムに関わる人は、NULLという特別な値になる可能性があるということを知っておく必要があります。フィールドの定義を見て、NULLにならない設定をしているフィールドとしていないフィールドを把握しましょう。文字列でも、数値でもそうなりますが、論理値でもNULLがあります。その結果、論理値なのに、TRUE/FALSE/NULLという3通りの値を取るということになってしまって、後で説明しますが、それも混乱しやすいところです。ちなみに、NULLは基本的に値がないことを示すものですが、「値がない」をNULLとする方法と、「値がないことを示す値」を使うを使う方法があり、後者だとNULLにならなくても良いとも言えます。ディレクトリサービスの世界では、値に対するNULLという概念がなく、値がないことを示すために非常に古い時代に「99」を使った経緯があります。システムによって「値がない」ことを示す数値や文字列は違いますが、どんな値を選択しても、その値がデータだったら…という危険性は常に付き纏います。そこで、NULLを使わないで値の有無を管理したい場合は、データのフィールドと、そのデータの有無を判定する論理値のフィールドの、2つのフィールドを使うのが正しい設計になります。そうなると、値の編集では2つのフィールドを処理しないといけなくなり、複雑さは増します。

データベースとのやりとりをプログラミング言語等で実装する場合は、フィールドの値がNULLだった場合の挙動がどうなるかをしっかり確認します。もちろん、それによって表示を切り替える必要があるかもしれません。NULLかどうかの判定は、どの言語もいろいろ事情があるので、安易な条件判定で失敗しがちです。ともかく、NULLに対峙するには、言語上でのNULLの扱いについて、まずはしっかり確認しましょう。文字列データは、比較的安直でいいかもしれません。画面上に「NULL」とか「null」と出てしまうのは言語道断としても(と言っても、時々見ますね)、NULLだと””にするあたりで大体解決するかもしれません。要するに、NULLと””を同じだと考えるということをシステム全体に渡って進めてしまうと、NULLを許可してもそんなに混乱はないと思われます。数値だと、NULLだと0でもいいのかもしれませんが、未入力であることを示すために意図的にNULLを使うということもあるでしょう。そうなると、やはりNULLであれば””と見るというような対処は必要です。いずれにしても、未入力という状態を扱うのかどうかということが、きちんと仕様に記載されていることが期待されます。一方、書き込みの場合がいろいろややこしいです。文字列や数値が確定していればそれを書き込めばいいのですが、逆にどういう場合にNULLにしないといけないのかということがきちんと仕様になっている必要があります。この辺り、きちんと仕様が記述されている必要があることながら、実際の開発ではかなり大雑把に進むことが多いという印象があります。おそらく多くの方は「当たり前」と判断しているのでしょうけど、解釈に若干の揺らぎがあるような領域では、当たり前では通じない可能性もあります。ですので、その「当たり前」を明文化するのが仕様書であることは言うまでもありません。いずれにしても、実データ以外にNULLを書き込む必要がある場合には、そのための処理は記述が必要になります。

概して、NULLが不要なら、NULLの保存ができないようにフィールド定義で指定するのが良いと言えます。例えば、あるフィールドはチェックボックスの処理と連動しているとしたら、論理値にしてNULLは許可しないようにします。そうしないと、TRUEはオンだけど、FALSEとNULLはオフのような解釈になって、何のために論理値を型として選んでいるのか意味がなくなってしまいます。ただ、このオンがTUREだけなものはまだ良くて、文字列フィールドをチェックボックスに割り当ててしまい、ある画面ではオン/オフが1/0、別の画面ではYES/NULLのようなことになってしまうこともあり得ます。もちろん、バグなのですが、メンテナンス開発ではそれを直せないとしたら、この状態で、機能を組み込まないといけないかもしれません。そうなると、オンかオフかの単純な判定であっても、「field = 0 OR field IS NULL」のような複合条件になってしまいます。それに、そんな不一致があるシステムは2通りで済んでいるかどうかは怪しく、さらに調査して、判定式の変更も必要になるかもしれません。結果的に論理値で済むフィールドは、最初からNULLを禁止した論理値のフィールドとして定義しておくことで、メンテナンス性も高まるはずです。

照合するフィールドについて、外部キーに相当する方のフィールドについても、NULLの挙動を検討する必要があります。外部キーフィールドに「必ず対応する主キーのフィールドの値がなくてはいけない」という制限を付けることもでき、その場合はテーブル間の関連付けが常に何かのレコードに対して設定されていることを期待しているものです。その場合は、NULLでなく、加えて主キーの値のどれかが入っているということになります。一方、外部キーにNULLを許すと、対応するレコードがまだ未確定であるということを示すことができます。もちろん、未確定なのが困るということもあるかもしれませんが、ワークフロー上未確定になってしまう場合もあるので、一概に「必ず値を入れなければならない」ということでは済まされません。未確定で一定の期間過ぎてから、何か対応する値が設定されるというようなワークフローであれば、外部キーはNULLであっても良いというか、NULLの特徴をうまく利用できているとも言えます。

ここからはFileMakerの話です。FileMakerにはNULLはありません。その一方で、事実上””をNULLと見做すような実装がなされています。しかしながら、””は空文字列というデータである場合もあります。””は検索や、>等での比較はできますが、唯一、リレーションシップでの=の処理において、”” = “” の結果はfalseになります。つまり、照合データとしては””は使えないということです。何か、数値なり文字列なりが入ったフィールド同士でないと照合できません。言い換えれば、照合されないようにするには””を利用するという意味で、この場面だけNULL相当のデータが存在しています。

NULLの話は尽きないですが、とりあえずこの辺りで今日は終わりましょう。

[DBデザイン#36] 素なデータを見つける:履修登録の設計はどうする?

前回は、第五正規形の例として、履修登録を示しましたが、表に分割はできるものの、更新処理が複雑怪奇になりそうということで、本当にそういう表分割をするのかどうかという根源的な問題が出てきてしまいました。例えば、商品マスターを分離するというのは、そのメリットはわかりやすいと思われます。あっちこっちにある商品単価が、一元的に管理されているので、単価が変わったら1箇所の修正で、あちらこちらの商品単価が自動的に更新されるというあたりです。ところが、履修登録を3つの表に分離したら、ちょっと様子が変わりました。まず、こんな履修登録があるとします。教員と科目は多対多の関係にあり、1人の教員が複数の科目を持つこともあれば、1科目を複数の教員が担当することもあります。

ここで、データそのものと、関係性を分離して考えるために、教員、科目、学生は、まずそれぞれ単独の表に記載します。そして、ID番号をここでは重複しないように適当に降ります。いずれもID以外にフィールドは1つだけですが、実際のデータベースでは、それぞれ、たくさんのフィールドを持つことになるのではないかと思われます。そして、その関係性を表に分離したのが後半の3つです。前回に、第五正規形の例として示した表と根本的には同じですが、実際の運用に近い感じにして見ました。つまり、合計6つの表で、履修登録の管理をしようということになります。

ここで、新たに新規登録がどんどん進む場面は、そんなにややこしくないでしょう。学生がある科目を履修したいとしたら、「学生履修科目」にまずは追加し、そして教務の担当者が、教員担当科目を参照しながら担当教員を適当に割り当てて、教員担当学生に行を追加します。おっと、ここで、「担当教員を適当に割り当てて」という人手が入ってしまっていますが、そういうワークフローにしましょう。

そして、たくさんの学生の履修登録が終わった後、教員の割り当てに変化が発生したとします。例えば、教員担当科目の4行目、X先生の科目Pの担当がなくなったとします。しかし、それは学生の履修がなくなるのならまだしも、通常は無くならないと思うので、X先生のクラスで科目Pを取ろうとしていた学生をZ先生など別のクラスに振り分けないといけません。教員担当学生はどう処理しましょう? これは元の「履修登録」の表に戻って考えないといけません。X先生に関するレコードは3つありますが、最初の二つは科目Mなので関係ありません。最後の科目Pのものについて担当をZ先生に切り替えたいわけです。ここで「教員担当科目」は単に、{101, 202} が消えるだけになります。「教員担当学生」はどうなるかといえば、{302, 101} を {302, 103} にしてしまうと、この学生が、X先生の科目Mを履修しているという情報がなくなります。結果的に、{302, 101}はそのままに{302, 103}を追加するという処理になります。ややこしいですね。自由に編集ができそうな気がしません。

このような問題がある場合は、現実のワークフローをよくチェックして、頻繁にあることは容易に進められるようにするのが、通常は考えることになります。そして、あり得ないことに対する対処は諦めます。滅多にないことは、何か対策を考えます。バッチ処理などということもあるかもしれません。

履修関係となると、一般にどの学校も、まずどんな科目があるかを決めて、それは履修登録開始時には確定していて、よほどのことがない限りその科目の存在は無くなったり増えたりはしないのが一般的です。同様に、担当する教員についても同様です。どんな科目があって、誰が担当するのか、つまり「教員担当科目」には、履修登録が開始されると変更はないという仮定があると考えられます。その上で、次のような表で構成することを考えました。履修登録は元の通りですが、履修登録前はレコードがないので、すでに決まっている科目と担当教員の情報を記録するために「教員担当科目」は必要になります。冗長ではあるかと思いますが、ユーザインタフェースを考えると、このような構成が良いと考えられます。

ある学生Aが科目Mを登録したいと希望があった場合、まず、履修登録に{201, 101, null} を追加します。締め切り後にCSVファイルを読み込むなどの方法だとそのような状態になると思われます。そして、教員IDについては、ポップアップメニュー等で、選択できるようにすれば、1人1人は手作業での指定になりますが、確定はできます。ポップアップメニューは「教員担当科目」の情報を利用することで、科目が、MならX、PならX, Y, Zが選択肢として登場するようにすればいいでしょう。また、履修登録と「教員」の表を結合して「教員名」を参照すると、決まっていない履修登録は、教員名がnullのものという判断ができるので、「履修登録一覧表_教員未選択」はこうしたルールを利用すれば作成が可能です。「履修登録」を直接作ってしまう方が、データの変更などの処理がやりやすくなるのであれば、もちろんワークフローの確認は必要だとしても、テーブルを分けない方法も選択肢としては有力だということです。

教員担当科目のレコードが1つなくなるような事態になった場合でも、履修登録をいじらなければ、その学生がその科目を履修しているという情報は保持されます。しかしながら、教員IDは対応する科目IDがないままになります。この場合、「教員担当科目」と「履修登録」のテーブル結合を、教員IDと科目IDの両方のフィールドを照合して行います。その時、「履修登録」のレコードは必ず残る照合を行います。その時、「履修登録」「教員担当科目」の順に記述し、前の方だけを残すので、「左結合」と呼ばれています。そして、右側の表にあるフィールドを参照すると、nullになっているものが、「教員ID」を正しく選択していないレコードというように判定が可能です。この方法で、教員IDを選択していないフィールドの判定も可能です。そうすれば、X先生が科目Pの担当を外れた後、教員IDを振り直す履修登録のレコードが絞り込まれるので、それらについて、地道にポップアップメニューを選択してもらうというワークフローが確立します。

どちらの設計が良いのかは、なかなか議論は収束しないかもしれませんが、結果的には、ユーザの要求が全ての出発点であり、実現しなければならないワークフローをきちんと考えて、そのワークフローが無理なく進められる設計に収束させる必要があるということです。開発作業自体が大変なのは仕方ないとしても、よりシンプルに開発できる選択肢があるのなら、常にそちらを目指すのが基本です。複雑なものよりシンプルなものの方が、ソフトウエアの品質が高くなる可能性が高いからです。もちろん、シンプルにするとワークフローが成り立たないとなれば、複雑化は免れないのですが、ワークフローを変えられないのかをユーザと折衝するなどして、開発は進むことになります。折り合いをつけるのは難しいですし、結局、何かこれから検討すべきことのコミットを求められるなど、厳しい局面もあるかもしれませんが、現在のソフトウェア技術とビジネス環境では、完成品を見ないと判断できないユーザが多いのも事実で、仕方ない面もあります。結果的に、プロジェクトをうまく進めるという能力に高いお金が払われるということにもなっています。

正規化の話は一旦ここで区切りとします。補足したいこともあれこれあるのですが、この先は少しランダムな感じでいくつか話題を振りたいと思います。

[DBデザイン#35] 素なデータを見つける:第五正規形と3つの関係

第四正規形の説明をしたときの表を改めて示しながら、第五正規形を説明しましょう。履修科目登録は3つのフィールドがあり、それぞれ何かのルールに従って入力されているような感じでした。

6行目を追加する前の状態で、かつ、{教員} → {科目} という関数従属性、つまりは、教員が決まれば科目は一意に決まる。つまり、ある先生は1つの科目しか持たないというルールを入れれば、「教員担当科目」が単独の表として存在でき、その表を引き算した結果は、残るフィールド「学生」と照合可能な教員フィールドを持つ「教員担当学生」の表の、これらの2つの表を結合すれば元の表が得られました。しかしながら、主キーとは関係のない関数従属性があるということで、すでにボイス-コッド正規形ではないということになりました。

ここで、この関数従属はないものとして、「教員担当科目」は、先生と科目の対応表というように仕組みを拡張したとします。その結果、前のように、X先生はMにもPにも受け持つということになります。表として分解した結果は以下の通りです。「教員担当学生」に {B, X} が追加されていないのは元々その情報が表に存在していたからです。

前回はこのうち、2つの表を結合していました。2つを結合すれば、少なくともフィールドは3つ揃うのでうまく元の表が合成できないかを考えたのですが、関数従属があるという前提で、その関数従属に絡んだ分割でないと元には戻りませんでした。

そこで、3つの表を全て結合してみます。前から順に行きましょう。まず、以下のように表Tを求めました。もちろん、元の表よりもかなり多くなっています。もちろん、科目同士を照合します。

このTに残る表を結合すると、なんと、元の表に戻ります。ここではそれぞれの表の {学生, 教員} を手がかりに照合します。Tの1行目は手がかりが {A, X} なので、教員担当学生にある {A, X} を探すとあります。もちろん、ここでフィールドを合体してもいいのですが、教員担当学生によって新たなフィールドは登場しないので、「照合可能なレコードが存在する」というのは、言い換えれば結果の表にそのまま追加されるということです。ちなみに、Tの4行目{Y, P, C}に対して、照合するフィールド{C, Y}のレコードが教員担当学生の表にあるかというとありません。ということで、照合可能なレコードがない場合は照合されるものがなかったということで消えてしまって、 Tの4行目{Y, P, C}を元にしたレコードは結果には存在しないということになります。これが結合の処理のルールです。ちょうど、「担当教員学生」の表によってあたかもフィルターのような効果が見られるということになります。

ということで、実は3つのフィールドある場合、3つのテーブルへの分割は、どうやら可能だということがわかったわけです。ざっくりと言えば、この分割をした結果が第五正規形です。もちろん、要求事項によっては前回に見たように2つで事足りかもしれません。3フィールドだから必ず3つということではありません。

第五正規形を正しく議論するとき、多値従属性をさらに拡張した結合従属性という考え方を基礎にすることになるのですが、かなり難しくなるようで、詳細が説明されていない場合が多いです。ただ、Abiteboul先生の「Foundations of Databases」での第四正規形の見出しは「無関係な情報を同一の表に入れるんじゃない」というような趣旨になっていて、言い得て妙なのではないでしょうか。第四、第五正規形のサンプルで最初に出した、3つのフィールドのある表は、ワークしそうな気はしますが、実は3つのフィールドから2つのフィールドを取り出した場合、もちろん3通りの組み合わせがあるのですが、それぞれに意味のある情報であったということになります。つまり、3つの関係性に関わる情報が、当初は1つの表に込められていたけど、それらの関係は分離できるということを第四、第五正規形は言っているのです。

3つの表に分解した結果を改めて見てみましょう。{教員, 科目}のフィールドを持つ「教員担当科目」は、教員と科目の関係なので、例えば、教員1人ずつを見ると、その先生が持つ科目の一覧になりますし、科目1つに絞り込めばその科目を担当する先生の一覧になります。つまり、教員と科目について対応関係があるかどうかということを記述している表になります。同様に「学生履修科目」は、学生がどの科目を履修しているかを表現する表です。そして、「教員担当学生」はどの先生のクラスに学生が属しているかという表になります。3つのフィールドはそれぞれ関連性がありますが、一方で、3種類の関連性があってそれらはバラバラにすることができるということになります。「教員担当学生」だけが単独で存在するのは少し違和感があるかもしれませんが、「教員担当科目」と「学生履修科目」だけでは、情報が少ないのです。つまり、この2つの対応関係だけだと、学生がどの先生のクラスなのかが確定しないということになります。

更新整合性についても検討しましょう。新たにクラスに追加するということを行うとき、「学生履修科目」と「教員担当学生」にレコードを追加することになります。これは、学生がどの科目を履修するのかという情報と、どの先生のクラスで履修するのかという2つの事実を記録するためであって、2つのレコードを記述するのは、2つの新たな関係性を記録するという意味では不整合はないと言えます。ここで、科目は自由に選べるとしても、担当する先生は「教員担当科目」によって規定された範囲でなければなりません。例えば、新たな学生が科目Mを選択するときに、その学生がZ先生であってはいけません。言い換えれば、「教員担当学生」に追加可能なレコードは、「教員担当科目」によって制限されるということになります。新たにクラスを作るとしたら、「教員担当科目」にレコードを追加しなければなりません。これは、実用上は単純なことではないでしょう。その制約をどこで解決するか、つまりUIでうまく制限するなり処理途中で確認をするなどの対処は設計上検討が必要です。

また、この表では学生Bは、2つの教科ともX先生のクラスですが、科目PはZ先生のクラスにするということになりました。では、「教員担当学生」の{B, X}を{B, Z}にするかというとそうではありません。この場合、たまたま2科目分の情報が同一だったので1レコードになったのですが、それぞれ違う先生のクラスになるということは、{B, X}はそのままにして、{B, Z}を追加する必要があります。そうすれば、表の結合で所望の表は得られるのですが、1項目の変更なのに、追加をするという必要が出てきます。つまり、クラス変更については、単に更新処理だけでは済まないということになり、ここもシステム構築の複雑さを増すことになります。つまり、データによって更新処理が異なるというのはある意味で不整合と言えるでしょう。この点は正規形によって解決できる箇所ではなく、むしろ発生した新たな問題と言えます。

このような表が欲しい場合、設計はどうすればいいでしょうか? 次回はそのことを考えてみたいのですが、少し間が開くと思います。