[DBデザイン#20] 実例から考える: 概念が増えた2

販売とは別に出荷管理をしているという前提では、データの保持だけはなく、出荷残数を求める方法も確立しておかないといけないということで、前回は銀行口座方式を紹介しました。もちろん、それはそれでうまく行くでしょうけど、データ編集にロジックが絡む点が、複雑さを発生させることも紹介しました。今回は、この応用で、一定期間ごとに残数のキャッシュを作る方法を紹介しましょう。前回の手法の応用なのですが、いくつか利点が増えます。

早速スキーマと、表を示します。スキーマの基本的な形は前回と同じです。顧客と商品に紐づいた受注出荷集計という新たな表が加わりました。初期状態はレコードなしです。ここでは1つの期を1ヶ月とします。1ヶ月に納品書2枚ということはないでしょうけど、考えやすくするために小規模にデータ発生を考えます。2枚の納品書、3回の出荷予定があった場合、その気の受注数、出荷数、そしてここでは出荷残数もフィールドに求めています。

この状態で次の月になって、新たに納品書と出荷が作られ、月末になって集計するとします。とりあえず、受注出荷集計には、月末にレコードを追加する、つまり、期ごと、商品ごと、顧客ごと、という3つの分類軸で受注数、出荷数、出荷残を求めることにします。

こうなると、例えば、12月の途中では、11月までの集計結果を受注出荷集計から取り出し、12月1日以降の納品書、出荷予定の明細分の増減を行うことで、ある時点での出荷残が得られます。当初は出荷残を求めるために、明細をデータの蓄積を始めた最初の段階からチェックすることになってしまいましたが、期を決めて、期末以降をチェックすれば良いという方法であれば、データが増えてもその月のデータを舐めるだけで済むのので、年々線形的にパフォーマンスが悪くなることもまずはないと思われます。ただ、SQLのビュー等で残数を得られるようにすることになるでしょうけど、ビューの定義はちょっと込み入りそうです。

そして、銀行口座の残高のように、明細が発生するたびに残高を更新する方法だと、競合の問題や、ロジックを実施しないとデータが正しくなくなるなどの問題がありましたが、この方法だと、販売明細や出荷明細を作る上での追加のロジックはなく、いずれもレコードを作るだけで処理対象に加わります。もちろん、帳票を作成途中の場合はどうするというワークフローに関わる問題はありますが、編集に関わるロジックはかなりシンプルです。お客さんが勝手にレイアウトを作っても、きちんと出荷残の計算には明細が絡んでくるでしょう。

受注出荷集計のレコード追加は、期末あるいは期首に、バッチ処理を動かすことになります。もちろん、バッチ処理が正しく動くこと、そして、バッチ処理が失敗した時の対処など、バッチ処理の開発は大変ですが、ともかく、利用者が簡単にさわれないところにロジックがあるのはある意味、開発側からすると管理しやすいとも言えます。

受注出荷集計が多数のレコードになって重くないかという心配もあるかもしれませんが、過去に渡ってデータを舐めることはないので、あまり心配はいりません。どうしても心配なら、最新の期のデータ以外は削除するという方針でもいいでしょう。現状の出荷残を求めるということだけのためなら、過去の期のデータは不要になります。

棚卸し調整は、バッチ処理を動かした直後に、フィールド値を手作業で直すというのが1つの方法です。棚卸しについては、前回と手法的には同じになります。なお、期は一定である方がいいかもしれませんが、仕組み上は受注出荷集計のレコードは、気が向いた時に作ってもいいくらいのものです。要するに、明細の全部を舐めることを避けるための手法ということになります。

前回の「受注出荷数」は、出荷残のフィールドがなく、計算で求めることにしました。しかしながら、今回の「受注出荷集計」には出荷残フィールドがあります。これも、それぞれのロジックを作るときに「おそらくこう考えるだろうな」という意図が込められています。前回の銀行口座方式の場合、直接に出荷残のフィールドを増減する方法も考えられるのですが、競合する確率が上がることなどがあるので、増と減をそれぞれ別々のフィールドにしておきました。一方、今回の場合はバッチ処理で求めているので、残数はバッチ処理内で簡単に求められます。結果、残数が必要なので、それをフィールドとして置いておき、むしろ受注数や出荷数は不要になります。ですが、説明上ややこしくなるので、受注数と出荷数のフィールドは置いておきました。変に細かく考えているとも言えるかもしれませんが、残数の出所の違いでこうした変化が発生しうるということでもあります。

この方法は、期の概念の導入と、バッチ処理などのロジックの発生をどう評価するかです。ロジックは発生しますが、銀行口座方式のように、単一のフィールドを複数のユーザで更新するような仕組みよりも、バラバラにレコードを作っておいて必要な時に集計する方が、システムの動作上は安定することが期待できます。どちらが良いのかという問題は、実際の要求に照らし合わせないと結論は出ませんが、少なくとも、いろいろな実現方法があることは考慮すべきでしょう。

ということで、一旦、あるロボットおもちゃメーカーを題材にしたシリーズはここまでとします。しばらくは、データベースを理解するのに必要な概念を深掘りしていこうと思います。更新頻度は落ちるかな〜

MySQLのインポート制限解除をmysqlimportコマンドで行う

MySQLでCSVファイルのインポート作業をしようとすると、次のようなエラーが出てしまった。

mysqlimport: Error: 3948, Loading local data is disabled; this must be enabled on both the client and server sides, when using table: pcode

ということで、このまま検索すると、対処方法が書いてあるサイトが見つかる。

だけど、mysqlimportコマンドを使ってインポートしている場合のクライアントのローカルファイル許可の方法が分からない。MySQLのマニュアルにちゃんと書いてあった。–localというオプションをmysqlimportコマンドに追加すれば上手くいった。

[DBデザイン#19] 実例から考える: 概念が増えた1

ここまでのところで、納品日が増えたという要求を紐解くと、結果的に出荷という仕組みが必要ということになり、出荷伝票を作ろうという感じでスキーマを変更してきました。納品書(受注受書)と対応を取らないで出荷をするとなると、現状での出荷残数を求めたくなります。それを、販売明細と出荷明細から処理するというのは、データが大量になった場合を考えると、あまり効率良くはありませんし、FileMakerのような一桁以上は遅い感触のプラットフォームでは、さらにパフォーマンス低下の体感が数年後に来そうでやばい(普通の意味で、若者言葉ではなく)です。

そこで、まずは、現状の出荷残をデータベースに記録する方法を考えます。前回に出てきた「銀行口座方式」です。受注すれば増やし、出荷すれば減らすという意味で、イメージはそんなに難しくないと思います。実は実装上はいろんな問題がありますが、後ほど説明します。まず、そうした出荷残データを残す場合、「何に対する出荷残」なのかをしっかり考えます。モデルにしている会社の場合、とりあえず登場しているエンティティは、商品、顧客です。言い換えれば、商品あるいは顧客が違うと、それは「別口座」つまり別のレコードを用意する必要があります。商品として「ロボットいか1号」があるので、それに対する受注残を管理すればいいということではありません。「トイザラシ」の「ロボットいか1号」と、「Amezon」の「ロボットいか1号」は、同じ商品でも、カウントする対象が別なので、異なるレコードで出荷残を管理しないといけなくなります。まずは、スキーマを見てみましょう。ERと表で示します。

新しいエンティティとして「受注出荷数」が追加されました。この表で、受注数と出荷数をカウントして、残数は引き算で求めることにします。この「引き算する」というのが設計に明示されていませんが、これは実はERなりクラス図を作るときには色々と工夫が必要な箇所ですが、ここでは説明でとどめます。実際、受注数は受注明細の個数と関係があるので、受注明細と受注出荷数は関係あるという見方もできます。正確には、受注明細の集約(Aggrigation)を利用してそれらのボックスを線で結び、コメント等で計算方法を示すことも可能ですが、作図テクニック的に寄りそうなので、ここではまず、主要な関係を示すにとどめたいと思います。

いずれにしても、この受注出荷数は、「顧客と関連している」「商品と関連している」というのがカウント対象を検討するときに出てきたことを設計上に落とし込んだ言い方になります。よって、顧客や商品と線を引きますし、表には関連付けできるように、商品IDと顧客IDのフィールドを持たせる必要があります。ここで、1顧客に対して複数の商品があるので、顧客と受注出荷数は1対多の関係になります。同様に、商品と受注出荷数も1対多の関係になります。そのためには、関連づけるためのフィールド、商品IDと顧客IDはいずれも、受注出荷数の表に確保します。

そして、「初期状態」を見てください。ここでは簡単のために、商品は3、顧客は2ですが、「受注出荷数」の表には、3 x 2=6個のレコードが必要です。つまり、「口座は6つ用意する」ということが必要になります。この初期状態を仮定した上で、納品書や出荷予定が作られた後の作業が可能になります。ここで、受注出荷数のCRUDを考慮しておく必要があります。商品が増えた、あるいは顧客が増えた場合を考えてください。商品が増えた場合に、それが全ての顧客に販売する可能性があれば、受注出荷数の表には、顧客の数だけレコードが増えます。つまり、「口座」を作っておかないとカウントできません。もっともここで、うまく設計すれば、商品の出荷可否的な処理も組み込めそうですが、ややこしいので、一旦忘れましょう。同様に、顧客が増えれば、その1顧客に対する全商品の記録を残すので、商品の数だけレコードが増えます。商品や顧客に行を増やすと同時に、受注出荷数の表の行を増やす作業を組み込む必要があります。商品や顧客を削除するときはどうしましょう。本当に削除するのか、記録としては残しつつ受注や出荷がなくなるだけなのかなど細かい動作の検討は必要ですが、一番雑な方法は「放置」であり、まじめな方法は「対応するレコードの削除」となります。これも、単に商品や顧客の削除や無効化だけでなく、他の仕事もしなければなりません。このように、生成と削除時に処理をかませないと全体的な整合性が取れないという状況であり、このような状況を「ロジックが含まれる」というような言い方をします。

受注出荷数のUPDATEはどうでしょうか。図の残りを見てください。販売明細が増えると対応する顧客及び商品に対する受注数が増え、出荷明細が作られると対応する出荷数が増えます。何度も言いますが、理解できない場合は、このように、小さなデータの表を実際に書いてみます。このようにもう書いてみてあるものを眺めてもいいのですが、自分で行を増やしてみたときに、正しく他の表も更新できるかは是非とも試してみてください。設計が正しくできる人は、それができるのです。最終的に出荷残があるのは、商品ID=102、顧客=201(つまり、ロボットいか2号でトイザラシ)に対して2であって、他の商品/顧客に対しては残数は0になっています。

ここで考えることは、販売明細や出荷明細の数値が確定したときに、受注出荷数のレコードを更新します。更新と言いながらも、現実的には現状の値を取り出し、明細の数値を加えて、元のフィールドに書き戻すということを行う必要があります。これが基本ロジックです。そして、この処理は、複数のユーザが同時にこのレコードを修正するということを考慮しなければなりません。ロジックの作り方によっては競合が発生するので、ロックや同期などを実装しなければなりません。実はこれがなかなか難しい場合もあります。ただ、絶対にロックが必要かというと、例えば1人しか事務員がいないならおそらくは大丈夫など、どこまで実装するかは要求と実態から詳細に検討することになります。

ここで難しいのは、ユーザエクスペリエンスを考慮したときの設計です。テキストフィールドで数量を受け付けるとして、それが更新される度に受注出荷数のレコードを更新するのでしょうか?もちろんそれは可能ですが、単に「3個受注した」という処理だけでなく、「5個を2個にした」という処理も実装する必要があり、またまたシステムは複雑化しUIも込み入ってくるかもしれません。出荷予定についても同様ですが、3個出荷可能なのでそのような明細を記載したら、同時に誰かが同じ顧客と商品に対する出荷予定を作ってしまっていた場合どうするか? この辺りはまずはどうするかを決める要求が定まらないということも多く、要望に対する実装が結構難しくなることもあります。いろいろな複雑さを解消する方法としてよくあるのは「納品書」「出荷予定」の「確定処理」です。ボタンを押してもらいます。そのときに受注出荷数を調整しますが、別の人が出荷処理していた場合の対処などのさまざまなロジックを確定処理として実装します。このステップがワークフロー内に存在すれば、おそらくは前述の「5個を2個にした」といった対処は不要になると思われます。確定したら、編集できないくらいの対処もいいかもしれません。確定した日時をタイムスタンプで記録して、そのフィールドが空欄なら未確定というnull利用もスムーズにいきそうです。確定処理というと新しいワークフローを入れろというように受け取られるかもしれませんが、実はこの確定処理は「承認処理」にうまく組み入れることができるかもしれません。このように、実装上、複雑化しないような仕組みをうまく取り入れるということも設計の重要な要素であることは言うまでもありませんが、前回に説明した通り、ここまでのワークフローとの擦り合わせをした結果がスキーマでなければならないのです。

ちなみに、個数を変更したら受注出荷数の更新をするという処理をFileMakerで実装する場合、トリガー使えばできるじゃんと思うかもしれませんが、前述のように、単なる入力だけでなく変更に対処するということも忘れないようにしなければなりません。ですが、もっと重要なことはトリガーの設定がなされていないと受注数や出庫数の更新ができないことです。自分はFileMakerに自信があり、そういうレイアウト開発のマネジメントはきちんとやっている、あるいは、チームマネジメントはバッチリなので大丈夫!などと思っていませんか? このような複雑さシステムに持ち込むと思いがけないことが起こります。FileMakerのソリューションはお客さんが自分で変えてしまうこともあり得ます。全てのレイアウトにトリガーを完全に仕込んだとしても、お客さんが自分で新たに作ってしまったレイアウトにトリガーが仕込まれているとは限りません。かくして、「ときどき出荷数がおかしくなる」というこれまた曖昧なバグレポートが上がってくることになります。こうしたリスクまではなかなか管理しきれません。お客さんにはレイアウトを追加してもいいけども、運用前に必ず見せてもらうなどの確約を取るなど、コミュニケーションマネジメントが必要になるということです。複雑な仕組みの実装は必要であり、もちろん、それができるのが素晴らしいことなのですが、単に実装に時間がかかるだけでなく、その後のメンテナンスのことも考えておく必要があります。

さらに、棚卸しも考えないといけませんが、もっともシンプルな棚卸しは、受注出荷数の数値のフィールドをいきなり変えてしまうことです。変更した記録などは取っておきたいところですが、本質的にはその数値を変更すれば現状に戻ります。ただ、顧客ごとというのはかなり細かいですね。倉庫だと、単に商品の数を数えるくらいの棚卸ししかでないかもしれません。ちなみに、倉庫の残は、倉庫への入庫数が関わり、現状のシステムではそのための仕組みが組み込まれていませんのでご注意ください。もう少し丁寧な方法は、出庫予定に残数の誤差を調整するレコードを作ることです。特別な顧客(例えば「棚卸さん」みたいな)を用意しておくことで、通常の営業活動と区別できるかと思われますが、特定の顧客の特定の商品の場合、どのようにリアルな出荷と区別するのかは難しい問題です。このような運用でカバーはなるべく避けたいところかもしれません。いずれにしても、残数との誤差の把握が結構大変そうです。顧客から「まだ3個届いていない」というクレームがあったとして、残数が0であったような場合の調整くらいになるというところでしょうか。現実に発生する棚卸しの必要性をもとに考えを巡らせましょう。

残数を求めるだけなのに結構複雑になります。残数を求めるためのスキーマも単純そうに見えて、それを成り立たせるための戦術を考えると結構複雑になります。その戦術を実現可能とするスキーマでないといけないわけで、結果的に設計全般を考えることになります。次回は、銀行口座方式とは異なる方法を紹介しましょう。

[DBデザイン#18] 実例から考える: 要求をさらに拡大する

納品書に出荷日を入れるという簡単そうな要求でも、細かくチェックすると色々と複雑な問題が出てきました。そして、「出荷」という概念というか、それはむしろ新たな活動というべき事象が社内にはあって、以前には顕在化していなかったものが、ちょっとしたきっかけでどうやら見えてきました。出荷は、受注(ここでは「納品書」として扱っている)に関係するのは明白ですが、販売明細を出荷に紐づけるか、納品書に紐づけるかという選択肢があって、それぞれでできること、できないことがあると示しました。なお、途中には、販売明細に出荷日のフィールドを設け、納品書と販売明細の関係はそのままで、いわば、販売明細と1対1の出荷のエンティティを設けるようなことも検討しました。それらの分析途中でも指摘しましたが、納品書の明細1行がまとまって出荷するとは限らないかもしれないということもあり、今回から数回に分けて、その場合にどのようなスキーマになるのかということを検討してみましょう。

ちなみに、要求探索の意味での問題点がここで露わになってきており、なぜそんなことが発生するのかをここで考えてみましょう。出荷のことがどうやら要求を検討する段階ではおざなりになっていたというのは明白です。なぜでしょう? それは最初の方で、「いろいろな部署の意見を聞いた」とありますが、出荷部門の意見は聞いていなかったからと言えるでしょう。この会社、もしかしたら、倉庫部門が営業の中の一部で、営業の担当者は気にも止めてなかったのかもしれません。そういうことを防ぐには、ワークフローをしっかりと「記述する」ことでかなりの解決になります。記述方法は色々ありますが、ダイアグラムを記述するのが理想的です。そうすれば、出荷することは忘れないでしょう。ですが、社内の複数の部署をまたがってのフロー図を書き切るのはかなり大変です。みずほ銀行は度重なるシステムトラブルに見舞われていましたが、本格的なシステム統合に先立って何年もかけて社内業務のデータフロー図を作成したことは有名です(『みずほ銀行システム統合、苦闘の19年史』日経BP社)。これが要求を洗い出すための最善の方法になります。しかしながら、現実にそこまでの労力はかけられないとおっしゃるお客さんが多分ほとんどでしょう。そうなると、設計する側でどこまで見えない要求の糸口に気付くかに依存してしまいます。ただ、少なくとも、顧客側の経営者は、業務の要素を把握しておくべきであって、実際にフローに登場しながらも要求に組み込まれていないことを把握するのは管理者の責任であるというのが一般的な考え方だと思います。ともかく、いろいろな立場の人と対話を行うしかありません。現場の人の声を聞くのも大切だけど、それと同様に管理的な立場の人との対話も重要です。そして、少なくとも設計者はわかる範囲でワークフローを何らかの方法で記述をすべきです。書けば、繋がりの不自然なところなどが見えてきて、何か足りないのか、あるいは余計なことをやっているのかなどが顕在化してくるものです。

出荷について考慮が足りなかったのは、社内ではその部署や業務が軽視されていた可能性も高いのですが、他にも要因は考えられます。出荷業務は別途独立してシステムが確立していた可能性があり、社内的にはもう別に考えなくてもいいんだ的な空気があったのかもしれません。そのような場合、「考えなくてOK、以上」で済ませがちなのですが、そこでシステム間連携が雑だと、結局のところ最低限の業務効率化も果たせない可能性もありますし、より作業が複雑化してシステム化のメリットは全くなかったということにもなりかねません。その場合は、既存システムも調査して適合性を高めるのはもちろん、そちらの改変やリプレースを提案できるくらいの設計力は欲しいところです。

まだまだ別の可能性があります。流通を完全に外注している場合もあるでしょう。このモデルになっている会社はどうやら製造部門はあるようですが、そこから倉庫会社にひたすら製品を送り、営業は伝票を送り続けると、自動的に顧客に指定した個数が届いているのかもしれません。この場合も、根本的には別システムです。別システムでかつ別会社なので、さらに柔軟性は損なわれますが、前述の通り、適合性を高めるための工夫を設計に織り込む必要は出てくるでしょう。

ということで、「納品書と出荷の明細は対応していない」前提の設計に入りますが、まずは基本的な考え方をまとめます。以下のER図と、サンプルデータの表のような構成になります。納品書と販売明細、出荷予定と出荷明細、これらが1対多の関係にあります。納品書はもちろん伝票をもとに検討した結果ですが、出荷予定をもとにすれば、正しい意味での「納品書」つまり出荷に対応した商品明細が記載された帳票が作成できそうです。なお、納品書と出荷予定の対応は、これだと記録されていませんが、ここではそれ良しとします。ちなみに、ここでの納品書と出荷予定のそれぞれの明細が対応を取る必要がある案件をこなしたことがありますが、もう少しエンティティが増えて複雑にはなるもののちゃんと動いているのでそういう設計も可能です。

ここで検討したいのは、「ある顧客のある商品に対して、まだ出荷していない個数はいくつか」を知りたいということです。つまり、出荷残数の把握です。出荷予定の生成を考えれば、出荷残があるから行うわけで、出荷残を参照しながら出荷明細を作っていくというシナリオが自然かと思われます。ここでも表のCRUDをワークフローに乗せて考えれば具体的な方針が出てきます。発注数は納品書とその販売明細が元データとして集計すれば得られます。一方、出荷数は、出荷に対する出荷明細を元データとして集計すれば得られます。その差が、まだ出荷していない数になります。この出荷残というデータの存在は、上記のERや表ではまだ顕在化していません。

出荷残を求めることは、データベースの設計的には単純と思われるかもしれませんが、この方法には大きく2つの問題が発生する可能性があります。1つはパフォーマスの問題、もう1つは誤差の問題です。パフォーマンスの問題は、こうしたデータを何年も積み上げていることを想定すれば自ずと見えてきます。最初はそれほどのデータ量でもないものが、数年も使っていると線形的に増えます。ナイーブにな考え方では受注数と出荷数を得るために、何年も前からのデータを全部合計する必要があります。また、線形的にデータが増えるため、「年々システムが遅くなる」可能性は大です。特に、FileMakerで扱い量が多い会社では、まあ2,3年でやばくなるという感じでしょうか? SQLだとかなり多くなってもそれなりには動くと思いますが、いずれにしても効率良い方法ではありません。これに対する対応策は、何らかのキャッシュを自分で構築する方法があります。そのキャッシュをデータベースとして設計しなければなりません。別の対応策は、「現在の出荷残を記録する」方法です。販売明細や出荷明細は、在庫の出入りです。販売明細に「3個」と入力されれば3だけ増加し、2個出荷されれば2を引くということで出荷残数が1になっているようなデータを持つことです。これは、営業と出荷をそれぞれ入と出と見なせば、ちょうど銀行口座のような動作をさせるようなものと考えられます。お金ではなく、商品の個数の入と出があって、残高は出庫残となるものです。いずれにしても、こうした工夫は最初からやっておいてパフォーマンスの問題が出ないようにすべきです。

もう1つの問題は「誤差」です。在庫数や現金残高など、本来は正しく記録していれば現状と帳簿の数値は一致するのですが、これがいろんな理由で一致しません。店舗のようなところだと、一致しない理由は多岐に渡るのですが(万引き、欠品、破損、事務処理を忘れる、など)、一致しないことがいわば常識であって、定期的に「棚卸し」をするのが一般的です。つまり「現状の在庫数」を把握して、帳簿に増減をかけます。この仕組みをなしに、こうした在庫数などの処理を進めるのはおそらくかなり大変なことになります。データが正しければ棚卸しは必要はないというのはその通りですが、棚卸しが不要な現場は実のところ見たことはありません。実際の個数と帳簿上の個数が違うとき、「何で1だけ在庫が違うのか」を血眼になって検証する労力やコストを考えれば、単に調整をしてしまった方が効率的なことが普通なのです。過去の情報をどの程度振り返るのかということも関係がありますが、多くの業務は「その時点での情報」の管理で概ね方がつくという感じではないかと思います。万引きや欠品等での損失は別途考えるとして、通常の業務を回すシステムは誤差の解消を棚卸しという手法で処理をします。企業会計上、数値の調整による損失計上は一定の範囲では認められています。ただ、棚卸しによる調整をどうするのかは、その会社での業務にかなり依存します。今後に紹介するスキーマ変更では、棚卸しについては方針を示すのに止めることにします。

続きは次回に回すとして、実はこのパフォーマンスの問題を解決するのは、スキーマだけでなく、ロジックが発生します。実はスキーマは単なるデータベースの設計ではなく、ロジックのベースにもなり結果的にロジックの設計も行った結果がスキーマだったりして、システム全体に渡る方針を記述したものになるという側面が見えてきます。

[DBデザイン#17] 実例から考える: 変更ポイントを考える

一連のデータベースデザインのお話は、仕様変更のことに入っていますが、当然ながら、設計の初期段階で「今まで聞いてなかった仕組みを実装しないといけない」というような場合でも通じる話です。その場合、まだ、実装をしていないという場合には、かなり自由に、そして理想的な状態での機能追加ができるのですが、前回説明したように実装をしてしまうと品質低下と戦うことになります。

さて、納品書に出荷日が必要であり、出荷が複数になる場合もあることがわかりました。ここで、「出荷」というエンティティは明らかに存在するとして、納品書、販売明細と出荷がどんな関係になるのかということを決めなければなりません。だいたいでいいということはなく、決めないといけません。

ここでまず、表としてデータを展開してみます。その後に「出荷」の概念の分離をしたいと思います。最初の案は、出荷日にカンマ区切りで記載されていたので、そのような出荷日フィールドを用意することです。1フィールドの追加なので、さほど大きな変更ではなさそうです。もちろん、第一正規形を満たさないので分割するという王道な意味での設計原則はすぐに思いつくことです。具体的には、特定の出荷日の納品書を集めるというのが、複雑な検索条件になることや、出荷日がDATE型ではなく文字列になるため、日付として正しい形式でデータを追加したり分離したりといった処理が増えるといったデメリットがあります。しかしながら、見えづらいデメリットですが、販売明細の1行1行について、いつ出荷したのかはわかりません。それを知らなくてもいいのかどうかもわかりません。いずれにしても、これは「見かけを整える」だけであって、その後の処理にはあまり良い方法ではなさそうです。

もう少し、出荷日について、DATE型のフィールドを用意した状態で進めるとしたら、フィールドを複数用意することになるでしょう。FileMakerの繰り返しフィールドも本質的にはこの方法になります。まず、これで見てわかることは、3回以上の出荷があったら記録できないなど、一見して問題点がわかります。もちろん、前者の問題点は同様にあります。

明細に対して、いつ出荷するかを記録してみる方法はどうでしょう?次のように「出荷日」を販売明細側に持たせます。このデータでは顕在化していませんが、同一日に別々に出荷があっても、1回でまとめて出荷した場合と区別がつきません。それでいいのかもしれませんが、ダメかもしれません。また、納品書側に表示する方法も若干SQLが複雑になります。ですが、納品書の表に出荷日を持たせるよりも、柔軟に対応できるのかもしれません。

さて、ここで実は大きな問題が見えて来ます。そもそも、出荷はどうやっているのか? 出荷はおそらくいろんな商品をまとめて送るでしょう。納品書通りの場合もあるでしょうけど、在庫がない場合にはあるものだけを送ったりするのが一般的かと思います。その時、販売明細の1行に忠実に出庫するかどうかは、その会社のワークフローそのものに関わることです。仮にその仮定が正しいとすると、販売明細は、出荷の明細としても利用できて、システム的にはシンプルになります。しかしながら、人間がそういった処理をするときには、販売明細とはあまり関係なく、ある顧客に対するある商品の未発送数を把握して、その未発送数ないしはそれより少ない数を1つの出荷明細として管理するようになるのではないでしょうか? 紙の台帳のような仕組みでそういうことを倉庫部門で管理している可能性もあるのです。

そこまで考えるとさらにややこしくなって来ました。このような複雑な問題は、やはり分割して考えます。まず、「納品書と出荷の明細は対応している」という状態を考えた上で、その後に「対応してない」という状況を考えることにしましょう。実際に仕様を考えるとき、疑問が発生したら即座にそれに答えてくれると言ったような状況にはならず、お客さんに問い合わせて、場合によっては検討の時間が必要になるかもしれません。では待つのかというと、そうではありません。設計するときには、結果的にあらゆる可能性を考えることになります。問題点や、利点、その後の開発作業等を、いろんな可能性に対して思い浮かべ、記述します。なので、確認作業をしながらも、可能性の追求をし続けます。おそらく、そうした想定のどれかに落ち着くものです。

「納品書と出荷の明細は対応している」前提での新たな表の導入をしてみます。おそらくは、2つのアイデアのどちらかになります。1つは以下のように、既存の納品書と販売明細の関係を崩さないように、納品書と出荷予定が1対多の関係になるようにすることです。新たに「出荷予定」という表を用意します。若干、具体的に「予定」という言葉を補うことにします。納品書、販売明細に変更はありません。おそらく、改変のための作業は少ないと見込めるでしょう。出荷予定の表は、1回の出荷が1レコードとなります。出荷IDはここでは便宜上のもので、納品書IDが記録されているところがポイントです。これにより、ある1つの出荷は特定の納品書に対するものとなります。もちろん、納品書と出荷が対応していないとなると、話は崩れるので、まずは、シンプルな状況を考えることにします。なお、この設計だと、販売明細の1行1行に対して、それがどの出荷予定なのかはわかりません。単に出荷の日付を記録するというコンセプトのものであって、納品書に追加した出荷日フィールドを第一正規形に従うように表の分離をしたものとも言えます。

ここでの出荷予定の表にある実質的なフィールドは出荷日だけです。現実の出荷にはもっとフィールドが必要かもしれませんが、出荷日に対して1対1になる存在が「出荷予定」とも言えるでしょう。この仕様変更の1つの目的には「複数ある出荷日を記録する」ということであり、それだけでいいのなら、このスキーマで十分です。ポイントは、出荷日が納品書ではなく、別の表にあるということです。何度も説明していますが、これがリレーショナルデータベースの設計の1つの重要な考え方になり、その導入には1対多の関係を抽出するということなのです。

一方、別の考え方として、納品書はいくつかの出荷に分離され、その出荷に対してどの商品をいくつ送るのかという明細情報があるという考え方です。ここで、販売明細からは納品書IDはなくてもいいので、一旦消してあります。その代わり、どの出荷に対応するものなのかを示す出荷IDのフィールドがあります。出荷が1回でも、あえて出荷の表に行を追加しなければなりません。大まかに言えば、すでに開発が始まっているとしたら、変更箇所は多そうです。要するに「出戻りが〜」と言われるような改変です。

ここで、設計段階で考えることに、開発可能で理解可能なユーザインタフェースが作れるのかということがあります。ER図やクラス図ではその考え方は一般には明示的にはなっていないので、設計者が気づかないといけません。これらのダイアグラムは一般には静的、つまりシステムの処理が一段落しているときに、データが正しく存在しているかを示すのであって、そこに至るまでの問題点は記述されていないのが一般的です。

そのことをどう考えるのか? それは、表に対するCRUD、つまりCREATE(新規作成)、READ(読み出し)、UPDATE(更新)、DELETE(削除)がそれぞれスムーズにできるためにはどんな前提があって、どんなユーザインタフェースが適しているかということを考えます。もちろん、検討のポイントはREADを除く3つの更新系処理です。当初の設計は、納品書と販売明細がありましたが、この2つだけです。2つだけだと、結構話は早く、納品書の行が先に作られて、そこに販売明細が順次いくつか作られるというCREATEの段取りはすぐにわかります。DELETEは各行で考えればよく、カスケード削除にするかどうか、論理削除(ソフトデリート:実際にデータは削除しないでフラグ等で削除したことにする手法)にするかくらいの検討になります。ちなみに、1対多の場合、1が先に作られるという前提があると、そんなに複雑にはなりません。

しかしながら、最後の図のように、1対多の連鎖があると、実はかなり難しくなります。最初に納品書の行を作るとき、自動的に1つ目の出荷予定を作るという工夫はまずは思いつきます。そして、その後、販売明細が追加されていくのですが、そのとき、出荷予定と紐づく形で販売明細が作られます。これはボタンを押して画面遷移をさせれば作れるのですが、おそらくかなり面倒なUIになりそうです。画面遷移をあまりせず、それでいて出荷予定とうまく紐づくという意味で、販売明細のCREATEやUPDATEを実現するUIはどうすればいいでしょうか? 適当に作ると、きっとダメ出しされてしまいます。プロトタイプを作って検討するなど、出戻りが少ない作業の段取りを考えるべきです。ここで、出荷予定が変わった、出荷が後から無くなった場合はどうしましょう?このように3つのエンティティが関わると、その途中がなくなるということも発生します。削除する出荷予定に紐づく販売明細は、そこで繋がりが切れるので、別の出荷予定に付け替えないといけません。もちろん、明細を持つ出荷は削除できないという仕組みは必要でしょうけど、いずれにしても、明細を作って削除するだけでなく、どの明細に紐づくのかという結合先のUPDATE機能、つまりちょっと特殊なUIが必要になります。

結果的に、全ての表に対してのC_UD(Rを除く。ですが、現実にはUPDATEとREADは密接に関連します)の必要性を考え、それがスムーズにできるのかどうかを考えた上で、最終的なスキーマに落ち着くでしょう。スキーマだけの問題ではないのです。ロジックやUIを含めて考えるのが設計になります。

仕様変更があるとこのようにして、UIは込み入り、お客さんが「こうして欲しい」というのが実現できないので代替案を考えて交渉するなど、一気に複雑さが増します。データベースのスキーマ変更は、システム全体に波及することがあるので、なるべくそうならないようにしたいとも考えるところですが、やはり要求実現という考え方からするとどうしてもやらないといけなくなるかもしれません。ちなみに、出荷予定を導入した2つのスキーマのうち、後者のような運用をしているところはあまりないと思われますが、UIが複雑化する絶好の例だったので紹介してみました。単に出荷日を記録するだけで良いという要求であれば、前者のスキーマにするか、販売明細側にフィールドを作るかというところで、なるべく改変箇所が少なくなるように考えるのが一般的であると思われます。販売明細側にフィールドを作る方法も、「出荷日を変更する」というUIが込み入りそうですので、具体的に考えてみるのもトレーニングになりそうです。

次回は、「納品書と出荷の明細は対応していない」前提でスキーマ変更を考えてみましょう。

[DBデザイン#16] 実例から考える: 変更内容を探る

設計変更についての議論が前回はちょっと大雑把だったのですが、変更とはみたいな話も長くなりますので、仕様変更の流れで具体的な変更方法と、一般的な話を並行的に進めてみたいなと思っています。

開発途中での仕様変更が嫌われる最大の理由、言い換えれば、それをすべきではないという根本的な理由は、システムの品質が低下する可能性が高いからです。品質低下とは、バグが発生するとか、メンテナンス作業がやりにくい、処理速度が低下するなど、いろいろな指標があります。ある種のシステムに対する変更は、その部分に直接関係する部分を多くの場合は調整する必要が出てきます。そのため、単に何かを追加したり削除したりということで済みません。ということで、変更箇所が波及するのです。結果的に間接的にも含めたいろいろな部分の変更が必要となります。それらを変更すべきところ以外の箇所は以前の状態を留め、変更箇所が変更されているというのが理想なのですが、それが難しいのです。人間がプランして、人間が実施するので、漏れや失敗等が発生します。最近はそういうヒューマンエラーを最大限になくす方法はテスト手法を含めていろいろ開発されていますが、単にシステム改変以外に、改変による品質低下を防ぐための作業が、改変そのものよりも多くなるのが一般的です。仕様変更は、少なくともシステムに何か手を入れます。その結果、より良くなる、例えば気付いてなかったバグが見つかったなどの利点もあるのも確かですが、平均的には品質が落ちると言っていいのではないでしょうか。要するに、システムに要素が増えればバグの可能性も増えるということです。

仕様変更を嫌う別の見方は、これは業者目線ではありますが、コストが増えることです。時間が増加し、プランがずれることも、結果的にはコスト要因です。開発の早い段階でなら、仕様変更のコストはあまり増えず、「もうちょっと頑張れ」的な判断になってしまいますが、開発終盤や開発終了後となると、現実的にはコストはそんなに気楽な数字ではなくなります。いずれにしても、作業者は辛いです。理屈の上では、品質低下を防ぐということに尽きるのですが、例えば、機能が実装されて本格的な統合テスト(例えば人間が手順を見ながら確認するようなタイプのもの)をする前と後で変更のコストが変わってきます。テスト後だと、再度、本格的なテストをやり直すことが多いと思われます。

仕様変更を受け入れないと、顧客が望むシステムから遠ざかることになり、これはこれで難しい問題です。品質確保のためのコストは、システムに深く関わっているエンジニアでも判断が難しいですし、また実際の変更作業の負担がのしかかってくるという点では避けたいところでしょう。一方、エンジニアではない人たちは、どんな変更なら容易で、どんな変更なら困難なのかはよくわかりません。なるべく最初の仕様検討時に必要な情報は出してもらうようにしてもらう必要がありますが、ともかく、後からだと「無理かもしれない」ということをちらつかせて、最初の段階で仕様を出し切ってもらうしかありません。それでも仕様変更があった場合、納期をずらす、費用を上積みするなど、交渉をするしかありません。もちろん、開発に入る前にそうした交渉が可能な契約にしておく必要があります。業務範囲を開発前に記述するのはどうせ曖昧になるのだからと言って、契約自体を重視しない人もそこそこいらっしゃいますが、それは良くないです。完全でなくてもいいので、業務範囲を定義して、そこから外れた場合には交渉の余地を残す契約に力を注ぐべきです。交渉の結果、仕様変更は見送るということができるとしたら、それは建設的な関係が構築できたと考え、見送った仕様を別の機会に解決するようなことを共同で進められれば理想的です。

前回、納品書に出荷日がないという指摘がありました。そういう仕様変更があったときも、最終的な目標となるレイアウトの変更などとともに、表で考えるのが基本です。納品書に単に出荷日を追加したのが以下の左側のレイアウトです。まあ、こういうことだろうなとまずは思いたいですね。

この納品書で問題がないとすれば、どこに出荷日を記録すればいいでしょうか?ここでは、納品書と出荷日が1対1であることに目をつけます。販売明細と出荷日は、左のレイアウトで見る限りは別々のように見えます。つまり、出荷日は販売日と同じように扱われているという見方もできるでしょう。もちろん、顧客や商品と出荷日は関係がなさそうです。販売明細から見れば、納品書を通じて出荷日を求めることができるのですが、いわば間接的な関係になります。つまり、出荷日というデータは、納品書との1対1の関係が最も強いものとなるということで、納品書に「出荷日」フィールドを用意して、日付を1つ記録できるようにすれば、左の納品書を構成可能なデータを持つことができると言えるでしょう。

フィールドを1個増やすだけでですね。簡単ですね。FileMakerだと、数分で終わりそうです。最も、このシステムは納品書だけではないと思うので、他との調整も必要かもしれませんが、実はフィールドを1つ増やすだけというのは、そんなに大変なことでもありません。おおむね、定義の追加と、レイアウト上のオブジェクトを増やすだけです。ただ、プログラムがいろいろあって、そこにロジックがある場合はそれらを全部見直す必要がありますが、ここでは納品書という表にタッチしている部分をチェックすれば良いので、品質低下を発生しそうな要因は比較的少ないと言えます。

ですが、こうした仕様変更をしていると、またまたさらに別の担当者から、商品ごとに異なる日に出荷する場合もあるということが出てきました。それじゃあ、出荷日をカンマで区切って記述しましょうか?

これでいいのでしょうか? 同一日に2回出荷したらどうするのでしょう? そもそも、受注と出荷の関係はここまでには何の情報もありません。ここからは、業務、つまりワークフローを詳細に検討しないと、仕様の理解に大きく失敗する可能性がある箇所になります。「別の日に出荷されることもある」という事実は、少なくとも、1つの納品書は、複数の出荷になる場合があるということを示していると言えます。これは、見方を変えただけです。「どんな場合があるのか」をありとあらゆる角度で考えれば、結論が見えてきませんでしょうか? この場合、1つの納品書に出荷は最低1、多い場合は2、3…と増えていくという現実が想定できるということです。

だとしたら、その納品書は何やねんということになりますが、おそらくは営業部門が単に、受注内容を記録しただけのものということになります。納品書というよりも、受注請書の方がより実態に近いと思われますが、こういった世間と違う意味なのにその社内では流通してしまっているユビキタス言語は、開発に携わる人はよくぶち当たって混乱させられた経験があるかと思います。つまり、納品書の実態は「受注」なのですが、名前を途中で変えるとこれまたややこしいことになるので、しばらくは「納品書」で行きます。

ここで、「出荷」という言葉が出てきました。そして、すでに記載したように、納品書と出荷は1対多の関係になります。そうですね、すでに説明した通り、出荷という新しい表が必要になるということをこの分析は示唆しています。つまり、このシステムには「出荷」という存在が必要になります。このような「納品書」や「商品」といったある意味まとまった1つの概念のことを「エンティティ」と呼ぶのが一般的です。まとまり方はエンティティによって微妙にあるいはドラスティックに違うとも言えます。ですが、おおむね、エンティティはシステム内の重要な概念であることが多いです。ER図的に記述すると、仕様変更により、左のものに、「出荷」のボックスを追加して右のような図になりそうだというあたりがまずは導かれたと考えます。

「出荷」と何が線で引かれるのか? 新たに出てきた出荷をどのように扱うのか? ということは、実際に表に展開しながらの話になります。次回に回しましょう。

[DBデザイン#15] 実例から考える: 設計の変更が発生した

難しいとされるデータベースの設計ですが、それができる人はいとも簡単にやってしまうことを目の当たりにして、何かコツはあるのだろうなとは思うところでしょう。そのコツは実は言語化しづらいものではあるものの、それを実例を通じてなんとか言語化してみようというのが一連の記事の目標でもあります。以前にも説明したように、数学的な意味で基礎から積み上げた理屈は完全に正しいものです。しかしながら、現実のデータベース設計を全て数学ではできません。なぜなら、要求がベースにあり、要求が数学的な意味で正しく定義できるような場面は現実にはないからです。要求は曖昧なところだらけなのです。なので、実際のデータベース設計では、表という基本的なデータ構造を書いたりあるいは思い浮かべたりしながら、矛盾のないデータ構成を作り上げるということを行います。

ここまでは、部署ごとにどんな表が必要かということを出発点にしました。もちろん、これは単一部署で使うようなシステムでも、ともかく業務で発生するデータを表の形にまとめてみて、そこから検討を行うという方法を取りました。もちろん、共通の情報は共通化する、つまりよく言われるマスター化するということを行うのですが、「商品」という同一名称のものが部署によって扱いが異なると、それは同じ商品でいいのかどうかということを考えなければなりません。ここまでの記事は一例であり、実際の案件ではもっといろんな制約や要求が出てくると思われます。こうして、いくつかの表が登場した時、その表の関係性を見ると、理想的には、1対多になっているということです。表に分けるというのは、実は1対多の関係を洗い出していることに他なりません。一方、1対1の関係は、同一の表に存在することが多くなりますが、これはちょっと言い訳がましい説明です。ともかく、1対多の関係を見つけるということが重要になります。

ここまで、営業部門での単純な納品書を考えてきました。そして、システム開発ではよくある仕様変更です。「仕様変更はしてはならない」ということは今では言えないことになっています。仕様変更を受け入れないと、顧客が望むシステムに到達できないからです。顧客が自身の業務を知っているのは当然と思うかもしれませんが、実は全くそんなことはありません。業務ができるというのと知っている、そしてそれを説明できるというのは全く異なります。まず、業務ができるとは言っても、1人が全部把握していることはほぼありえず、結局は複数の従業員にノウハウが分散しているのは一般的です。そして、それら業務の関連を理解していてワークフローに展開できるかというとそれができる人はまた限られますし、下手をすると社内では誰もそれができないこともあります(そういう事例にも当たったこともあります)。説明可能性はもういうまでもないですね。ズバリ言えば、お客さんの頭の中はファンタジーでいっぱいで、お客さんが話す内容はポエムなのです。私たちエンジニアは、ポエムで記述されたファンタジーを、実際に稼働するシステムへと展開する役割を持つ非常に重要な立場にいるのですが、過剰にポジティブに考えると余計凹むかもしれませんね。

ともかく、システム構築をしているとこんな話が出てきたとしましょう。納品書のレイアウトができてきて、テストをし始めると、ある担当者が口走ったとしましょう。

仕様検討する段階で持ち込んだ納品書のサンプルには、なぜか出荷日がなかったのか、どうだったのか、ともかく、出荷日が必要だそうです。これは、レイアウトに必要なのか、あるいは記録として必要なのか、どっちなのかもよくわかりませんが、そういうファンタジーだと諦めましょう。

次回より、この「出荷日が必要」という要求がどんな世界に発展するかをじっくりと追いかけることにします。

[DBデザイン#14] 実例から考える: さらに関係を探す

前の記事までに、納品書と販売明細が別々の表として記録するという設計方針を説明しました。その時に示した図を再度掲載しますが、ここではさらに「商品」と「顧客」も別の表にしています。

商品を別の表にすることはすでに説明した通りではありますが、この販売管理の枠内でも商品を別にする理由は出てきます。販売においては、1つの商品を、いろいろな会社にいろいろな時期に出荷します。1回の販売と出荷を納品書が表すとすれば、商品と納品書は、1対多の関係になるからです。この時の「1商品」が何者なのかは非常に表現がしずらいです。箱1つ1つではありませんが、かといって大まかな意味での会社の商品でもありません。販売明細の1行に対して、1つの商品が結びつくことになるので、「ロボットいか2号を5個」といったような、個数とセットにした出荷時の内訳の1つを示すデータの構成要素がここでは商品になります。概念として説明は結構大変ではありますが、仕事をしている上ではそんなことに悩んでいる場合ではないので、おそらく多くの方はスルーしているところかと思います。なお、1対多になるのは、商品と納品書ではありません。よく見ると、商品が登場するのは販売明細です。ここは実際のデータがどこにあるかを見極めて、関係のある表が何なのかを判断しないといけません。すなわち、商品と販売明細が1対多になります。販売明細は、1つの納品書では数行ですが、実際に仕事を行うと多数の納品書ができるので、販売明細はそれらの全ての納品書に所属するものが行として追加されたものになります。それが1つの表になります。その表には、1つの商品が多数現れるので、1対多になります。

一見すると、商品と納品書が1対多と思ってしまうところですが、ここまでのところで、商品明細と納品書が多対1であることを分析しています。ということは、1つの商品明細は1つの納品書と結びつくので、商品>商品明細>納品書という2段階の関連を考えれば、関係性は1対多と1対1になります。つまり、商品から納品書を見れば1対多であるのですが、それはすでに構築された商品名サイト納品書の関係は無視できない、つまり、この関係性を崩すと、納品書は成り立たないので前提として存在すると考えないといけません。その点でも、感覚的には商品と納品書の直接の関係はありそうではありますが、精密にデータを記録するという意味では直接の関係は考えず、2つの関係があるので、結果的に関係はあるのだけど、設計上の注目点ではないということになります。言い換えれば、2つの関係を保つことで、明細の実現、商品マスターの実現ということができるということです。

なお、図では商品に「単価」も入れました。もし、全ての納品書で、商品が決まれば単価も決まるということなら、このように商品テーブルで単価を記録します。そして、必要になれば参照することで例えば単価と個数の掛け算ができます。一方、商品が決まっても単価は決まらないということなら、単価はむしろ販売明細のフィールドになります。同じ商品でも、700だったり690だったりするという状況です。ただ、現実の案件は、これらの極端な場合とは限らず、その中間だったりします。もっとも、「商品が決まれば単価が決まる」というルールを厳密に行う場合もあったりするので、前者に寄ることはあるかもしれません。このような、商品にあるべきか販売明細にあるべきかという議論は技術的な意味で決まるものではなく、結果的には要求次第ということでもあります。この辺りは別途議論しましょう。

図では「顧客」という表を作りました。これはもうお分かりの通り、1つの顧客に対して、頻繁に販売をするのが普通だから複数の納品書が作られます。つまり、顧客と納品書の関係は1対多となります。また、納品書に複数の会社名が入る、つまり1つの納品書で複数の顧客に出荷するようなことは多分ないでしょうから、やはりそうした制約も1対多の関係の基礎となるでしょう。

さて、ここまでで、販売管理は4つの表に分割しました。前の図で見ている右半分はER図と呼ばれます。こうして設計した内容を表として記述するのはわかりやすいですが、現実世界のシステムでは、多数のフィールドが登場するため、表が横に長くなりすぎて、一覧性は低くなります。なので、「どんなフィールドがあるか」をボックスにまとめて書き、ボックスが1つの表であると表現します。そして、表と表の関連を線を引いて表現します。ER図等はまた改めて検討しますが(なんか、宿題だらけ〜笑)、こういう図を記述することで、設計をコンパクトに示して、全体像を把握しやすくします。もちろん、設計者はこの図を見て、頭の中で表に展開して、データを意図した通りに保持できるかということを常に考えます。それができないと、この図の作図はもちろん、読み取ることもできません。

このように、ER図は、表と表の関係、そして個別の表の内容を定義した極めて集約度の高い設計図なのです。現実の開発では、いきなりこの図がアーキテクトから出てくるかもしれません。実装者はこれを参考にして、間違いなく機能を実装することを求められるということになります。いずれにしても、具体的かつ全体を示すという意味でのモデルとしては非常に役に立つダイアグラムとして認識されています。

ここまでで、表に分解して考えるシリーズを一旦終わらせます。同じように「製造管理」も考えてもいいのですが、引き続いて、「仕様変更」ということを表で考えて、設計に持ち込むことを考えていきます。

[DBデザイン#13] 実例から考える: 関係と意味

前回の記事では、納品書をもとに、そこからそこから1対多の関係見つけました。何度も出ていますが、1対多の関係、あるいは1対1の関係を見つけるというのが設計での大きな目標でもあります。この関係は、双方向で見たときに1対多なのか1対1なのかを判断します。そして、多くの場合は、1対多の関係は、異なる表で表現することが可能です。言い換えれば、異なる表で表現する方が効率が良い場合はもちろんですが、異なる表に表現しないと記述ができないような場合もあります。1対1の場合は、同一の表にまとめられるということが一般的です。つまり、関係を導くことで、データベースの設計として記述可能な関係性が得られるのです。

ここで、販売明細はいきなりこれは表だからという理由をつけましたが、もう少し詳細に考えてみると、例えば、商品名は「いろいろな商品がある」のだから、納品書に対して商品名は、1対多の関係になります。また、同様に単価もそうです。単価の場合、偶然異なる製品で同一価格ということもあるかもしれませんが、商品ごとに単価は異なるという原則を考えれば、納品書と単価も1対多です。そして個数はどうでしょうか?個数は整数なので、全部1ということもあるかもしれませんが、同一の商品を考えてもその時々、つまり納品書によって個数が違うことを考えれば納品書に対して1対多です。前回の図の1つを再掲しましょう。この図は次回もきっと登場します。

ただ、販売明細を1対多で考えるのはちょっと分かりづらいのですが、ここで重要なことは、商品名・単価・個数という情報が1塊となっている点です。この3つの組み合わせに、納品書がどれかという情報を付け加えると、原則として、システムに2つとないセットになります。これは、通常は納品書の明細に商品が重複して登場しないことを原則としています。実はこの考え方は集合論の定義でもあるのですが、それはさておいて、商品名・単価・個数は一塊なので、その塊を表の1行として、つまり1レコードとして把握して、納品書と販売明細は1対多の関係にあるということを導き出します。その1対多の関係を実現するために、「納品書ID」という方法で、販売明細の各行がどの納品書に結合するのかを記録するという方法が一般的です。

このように、表になってしまったものを個別で考えるとかえって分かりにくいかもしれません。「表として整っている」ということをまずはヒントとして使い、その中で、各データがどんなふうに振る舞うのかを考えることがポイントになります。

販売明細の金額は、「単価×個数」で求められます。ということであれば、金額は、販売明細の1レコード内の情報から求められる「計算フィールド」ということになります。SQLでは、ビューを使う方法が一般的です。FileMakerだと計算フィールドという機能があります。つまり、金額は、販売明細の一員として扱えるということが成り立ちそうなので、販売明細のフィールドとします。ただし、SQLの世界では、計算フィールドという考え方がないので、設計上は式を考えておくのが理想的ではありますが、SQLでのテーブル定義には計算フィールドは登場しないので、ER図に明示しづらいとも言えます。いずれにしても、商品名や個数のような、実データに関わるフィールドではないということを意識できるようにしておくのが設計上は重要です。一般には途中の段階でどこに計算式を実装するかを検討することになるからです。

一方、合計や消費税などはどうでしょうか? これらは納品書に1つなので、納品書と1対1に対応することから、合計は、納品書に存在するフィールドと考えられます。しかしながら、これも計算で求められそうです。販売明細の金額の合計で求められるので、計算フィールドであると言えるでしょう。ただし、この場合は、納品書の側にあるにもかかわらず、販売明細のデータを使って計算が必要です。ここで2つの表が関係していないと、販売明細のどのデータを使って計算すればいいかが分かりません。逆に、納品書から見て関連している商品明細、つまり納品書の明細リストにあるものが取り出せなければなりません。もちろん、これは、「納品書ID」が同一のものを検索することで取り出すことができます。リレーショナルデータベースではこのような関連を利用して、別のテーブルのデータを取り出し、計算結果を示すということも可能です。こうした機能があるので、「合計」は納品書のフィールドとして存在可能ということになります。ただし、関連する表との関連が確実に取れないといけないということになります。その前提の上で、式を記述することができるのです。

今回は細かいことをあれこれ書いていますが、実際の設計では、慣れた方はこのような内容はおそらく反射神経的に理解はしているところです。むしろそこをすっ飛ばさないと細かいところに気も時間も取られて前に進めないと思われるところでしょう。ですが、1つ1つの事象が何を意味しているのかをしっかり吟味しないと、やはり見落としが出てきます。そうした作業の中で、システムが扱う世界の1対多の関係を発見していくという作業が、データベースの設計においては重要なことになるのです。

[DBデザイン#12] 実例から考える: データの関係を解きほぐす

実際に納品書を見ながら何を考えればいいのかを紹介しましょう。再掲になりますが、こういう納品書があるとします。典型的な営業で使われるような書類です。

まず、下半分は都合よく表になっています。表になっているものは、1つのまとまった単位とします。この表には名前がついていないので、ここでは「販売明細」と名前をつけましょう。表になっているので、もちろん、行が複数ありますが、多分、1行以上、レイアウトが許す多数の行数があり得るでしょう。まさに、この行はレコードになりそうです。そうなると、商品名、単価、個数、金額という4つのフィールドは、販売明細のフィールドと扱えるということになります。このように、すでに表として形成されているものは、すでにデータの関係性をそこに表現しているとも言えます。

ところが、表の下の方に、合計、消費税、請求額とあります。表の中だから「販売明細」の仲間でしょうか? 確かに、これらの金額は、販売明細の表の中にある数値、つまり、金額等の合計で求められるので、確かに関係はありそうです。ですが、データベースの設計をするときに考えるのは、それらのデータの存在そのものが、他のデータとどのように関連があるかです。ここで、合計などの数値は、1つの納品書について1つ存在します。納品書ごとにおそらくは違うでしょうけど、1つの納品書に2つの合計値があるということは理屈の上では成り立ちません。つまり、合計、消費税、請求額は、納品書と1対1の関係にあります。一方、合計などと販売明細の関係は、1対多の関係になります。販売明細に存在する複数の行と、その合計の間では、前者は1つかもしれないし、50個かもしれません。それらから、1つの合計という数値が得られるので、多対1の関係になるのです。

このように、データの存在あるいは成り立ちが、他のデータとどのように関係するのかを考えます。納品書の日付は、納品書に対して1つだけなので、納品書と日付は1対1の関係にあります。

一方、顧客名が見えています。これも納品書に対して1つだけ書かれているので、納品書と1対1の関係かと思うところですが、ここでさらに、図にあるような納品書が現実には多数作られるということを考えます。つまり、納品書が1枚作成できるのはもちろん基本機能としては必要ですが、要求を満たすには、納品書は多数、そして内容が異なるものを作らないといけなくなります。そうすると、当然考えられる一つの結論としては、1つの顧客に対して何度も出荷することになるのですから、実は顧客名と納品書は1対多の関係になります。つまり、1社に対して長い年月を経ることで、多数の納品書を作成するということになります。

ここで、多数の納品書を考えたとき、納品書と販売明細の関係が1対多ではなく、多対多になぜならないのかという疑問もあると思います。だんだん説明が込み入ってきますが、頑張りましょう。まず、結論を言えば、1対多の関係が1つの納品書で完結しているので、多対多の関係にはなりません。ここでは「販売明細」がどんな性質のものかを考えて、その結論が導き出されます。販売明細は、商品、単価、価格、金額を保持している表です。ここで、ある会社に「ロボットいか2号、800円を5個、即ち4,000円の出荷をした」という情報が1行書かれているとします。しかしながら、現実には、こうした営業活動を多数行うので、販売明細の表には別の会社に対して同様にロボットいか2号を5個出荷という一見すると全く同様な情報が登場しそうです。これはいいのでしょうか? 実際に、こうした販売明細を表に記録、つまりデータベースに記録するときには、データ上は同一の出荷情報であっても、異なる納品書に書かれたものは別々のものとみなします。言い換えれば、販売明細の各行は、実物では顕在化していなかったとも言える「どの納品書にその明細が記録されているのか」という情報を付加することで、1枚1枚の納品書で1対多の関係が完結するようになっているので、それらが集まっても、1対多の関係であるとみなすのです。

ここで商品についても同じように考えたいのですが、それは次回としましょう。まずは、「納品書」と「販売明細」の2つの表に分解し、それらの結合を明示するために、納品書IDというフィールドを割り当てます。これも、以前に説明したものと同じですが、ここではまず、納品書側の納品書IDに301, 302…と先に番号を振ることとします。そして、その納品書に所属する販売明細の行について、すでに降った納品書IDの値を記入します。つまり、以下の図では、販売明細の最初の2行が、納品書の最初の1行に対応し、販売明細の3, 4, 5行目が納品書の2行目に対応します。つまり、同じ納品書IDのもの同士が組み合わされて、1枚の納品書になるという状況を作ります。

顧客や商品についての議論も必要でし、「販売明細の金額は計算で導き出せるよね」みたいな話もしなければなりませんね。また、右の方にER図なんかが見えていますが、これも次回あるいは次回以降に説明します。