2012-06-05

『売上猫くん on MySQL』開発日記 - 番外24 - MySQL 5.5は使用できるのか?

FileMakerとMySQL 5.0/5.1で開発してきた人へ:

『売上猫くん on MySQL R5.0β』は、FileMaker 11(FM11)/MySQL 5.1で開発した。 そのDBダンプをMySQL5.5上 にリストアして、FM11フロントエンド(NekoApp.fp7)からアクセスしようとすると、以下のような状況となり、アクセスできない。



このNekoApp.fp7をFileMaker 12(FM12)でNekoApp.fmp12に変換し、MySQL5.5にアクセスするとどうか?



と、さらに悲惨なこととなる。


NekoApp.fp7のリレーションシップ画面を開いてみると、下図のように一見すると正しくテーブルは認識されているようだが、TOをダブルクリックしてみるとTOとテーブルのリンクが消失していて、テーブルを指定し直す必要がある。


また、その指定し直しの過程でリレーションシップが飛んでしまったりもする。 TOとリレーションシップをすべて再設定・確認するだけども大変な作業になるし、検証作業も含めると… 膨大な作業になる。


注:
  • 使用しているODBCのバージョンは、5.1.11。 
  • FM社が公式サポートするMySQLは現時点でMy SQL 5.1 Community Editionのみ。
  • MySQL5.1を介さず、最初からFM11/12とMySQL5.5でリレーションシップ(TO)を作成しておけば、とりあえずは動作するようである(要検証)。

2012-06-01

簡単? FileMakerで在庫管理(2) ―― 繰越処理の考え方

 今回は、繰越処理に伴う在庫算出について考えていきましょう。 在庫算出の基本については、以下の記事をご覧ください。

 簡単? FileMakerで在庫管理(1) ―― 在庫算出の基本


繰越処理の考え方


 入出庫のレコードが増えてくると、在庫算出時に合計する入出庫明細レコードが膨大になり、処理速度が低下します。
 そこで、在庫数の繰越処理を行うことによって、在庫算出処理を行うことにより、在庫数算出を高速化する必要があります。
 
 たとえば、商品Aの2011年6月~2012年5月の入出庫明細データが10万件あったとします。
 2012年5月31日時点の商品Aの在庫を算出しようとして、入出庫明細レコードを10万件を対象に、Sum(入庫明細::入庫数)-Sum(出庫明細::出庫数) をやっていては、処理時間が膨大になってしまいますが、繰越処理を実行することによって2012年4月末時点の商品Aの在庫数を商品テーブルに保管しておき、5月1日~5/31の入出庫明細レコードのみをSumの対象となるようにリレーションを組めば、Sumの対象となるデータは、単純試算では10分の1以下となります。

 次に在庫算出の計算式を、以下のように繰越処理を行った場合と、行ってない場合とでは、在庫算出の計算式が違ってきます。

A式: 導入時在庫数+(4/25以前の入庫計)-(4/25以前の出庫計)

B式: 繰越処理日≦在庫基準日の場合の在庫算出式
繰越在庫数+(4/1~4/25の入庫計)-(4/1~4/25の出庫計)


C式: 繰越処理日<在庫基準日の場合の在庫算出式
  導入時在庫数+(2/28以前の入庫計)-(2/28以前の出庫計)

 要はA、B、Cの式をCase分けして、ひとつにまとめる必要があるわけです。



繰越処理と制御


 さて、繰越処理で考えておかなければいけない重要なことが一つあります。それは、繰越処理日(上記例では3/31)以前のデータ、在庫に関していえば、3/31以前の入出庫レコードの出庫日と入庫日、出庫数と入庫数を変更不可にしておく必要があります。また、3/31以前の同レコードは削除させてはならないし、繰越処理日以前の入出庫日を持つ同レコードは作成させてもいけません。 

 その理由は、[繰越在庫数]との整合性がなくなってしまうからです。

やがて訪れるデータ削除の時期について:
 長期間使用するシステムでは、繰越処理を行ってもレコードはどんどん蓄積されていきます。当社が開発・保守を担うシステムでは百数十万件の入出明細データを持つテーブルがありますが、ほぼ定期的にデータ削除を行っています。小社では、FileMaker のテーブルの最大レコード蓄積数は200~300百万件を一つの目安としていますが、データの削除は、システム仕様、実行速度要求、保持データ数要求、バックアップポリシー等の諸事情を勘案し、システムが安定動作することを大前提に実行してください。一般ユーザがデータ削除を行う場合は、一括データ削除機能も必要になります。
 また、削除したデータを照会したい、といった要望も必ずといっていいほどユーザより提示されるので、できれば過去データの照会についても予め考慮してシステム設計を行います。


在庫管理は簡単か?


 残念ながら答えは NO です
 システム的には、繰越処理とそれに伴う伝票制御、旧データ削除時の処理を考慮せざるを得ず、在庫管理システムを作るのはやはり簡単ではない、というのが結論になります。

 また、繰越処理関連を適正に実装できたとしても、1カ月に同一商品の取引が数百回以上あるような業務では在庫算出処理の遅延も予想され、別の在庫算出手法(拙稿の「FileMaker V10による在庫管理、一考」の123を参照)の採用も選択肢に入れなければならないでしょう。



土屋

2012-05-27

簡単? FileMakerで在庫管理(1) ―― 在庫算出の基本

 本稿は2012年5月に投稿されたものです。2018年2月現在、最新の FileMaker のバージョンは 16 となっていますが、本稿及び続稿で記述した在庫に関するの設計手法は現在も有効と思われます。
 ただ、iBeacon/EddyStone の登場等や、記事に気になる点があったため、今回、加筆修正することにしました。また、SQLを使った在庫の繰越処理などについても加筆したいと思っているのですが、これについては別の機会にしたいと思います。
(土屋企画)

 本稿では FileMaker を使用した在庫算出の方法について考えます。ただ本論に入る前に、在庫算出の3つの手法を大雑把に見てみます。それぞれの手法に便宜的に名前を付けていますが、これらは筆者が勝手に名付けたもので、社会、業界で一般的に利用されている名称ではありませんので、他所で「入出庫小計差分法を採用したいのですが…」とか言っても、相手の方は「???」となるでしょう。

在庫算出3つの手法

入出庫小計差分法

  この手法は当方の様々な在庫管理システムで使用している方法で、FileMaker で在庫算出する際の一般的な手法と思われます。いつの時点の在庫を知りたいのか(以下[在庫基準日])を決め、その時点までの入庫数計と出庫数計を計算、その差分が在庫となります。下図の4/30時点の在庫は、4/30迄の入庫計=20と出庫計=6の差分で[在庫]=14となります。
 Excelでは、出庫数の横に在庫列を作成して「D4+B5-C5」などとすれば簡単に在庫が求められますが、リレーショナルデータベースではそうした計算はできません。なぜならリレーションの無い行同士の演算はできないからです。無理に外部キーを振ったり、スクリプトのループで加減演算しても、結局は破綻します。
この方法は比較的単純ですが、特定の商品が頻繁に取引されるようなシステムでは、在庫算出処理に想定外の時間を要する可能性があります。

在庫マスタ逐次書換法

本手法は下図のように入出庫に変動があった場合、在庫マスタの[在庫]フィールドを即時更新する、というものです。下図を例に説明すると、3/31に[入庫数]=10を入力してコミットすると、在庫マスタテーブルの商品Aの[在庫]は即刻10に更新され、4/1に[出庫数]=1を入力すると[在庫]は9に即更新されます。
この方法は、入出庫伝票更新時に在庫マスタを即更新するため、その後発生する在庫照会や在庫表印刷の処理では各商品の在庫算出を行うことはなく、高速です。ただ、実装方法が難しいこと、上述の在庫基準日を指定できないこと、入庫日と出庫日には未来の日付を入力できないことなど、デメリットは少なからずあります。 ただ、同一商品の入出庫が頻繁に発生し、リアルタイムに在庫を把握する必要がある業態では、この手法を採用することが多いでしょう。実は2009年に本手法に関する投稿とサンプルファイル(現在リンク切れ)を公開しています。興味があるかたはこちらをどうぞ。

ビーコン信号カウント法

近年リリースされた iBeacon/EddyStone(以下、ビーコン) は少ない電力(電池)でユニークな値(UUID/UID)を含む信号を数カ月~数年、0m~100mほどの範囲で発信する装置です。
土屋企画で使用するビーコン ― 左の小さいタグ型ビーコンは 40mm×28mm 
このビーコンを製品に取り付け、その信号を取得・処理することでほぼリアルタイムに商品の在庫を把握することができます。以下のシステム図は 、Raspberry Pi と呼ばれる超小型Linux機(価格は約3000円/台)を広い倉庫内に万遍なく配置、ビーコン信号を漏れなく取得し、データベースの在庫テーブルを更新するというものです。
 入出庫システムからは独立して、在庫数の把握が可能となります。

本手法はビーコンが高価(500円~数千円~)であること、取り付けが不能・不向きな製品があること、電池に寿命があり交換が必要なこと等、ビーコン特有の問題があります。ビーコンについてはこちらを参照してください。


在庫算出の基本

さて、それでは上述の「入出庫小計差分法」について以下そのしくみを見ていきます。

 「2012年6月20日時点の在庫」というような特定時点(在庫基準日)の在庫数を算出するために、以下のようなTOG(テーブル・オカレンス・グループ)を作成してみます。


 商品マスタと出庫明細と入庫明細をリレートするだけですのでシンプルですね。
実際アプリケーションでは、出庫明細や入庫明細を格納するための出庫と入庫のテーブルが必要ですが、ここでは省略します。
 以下は上記リレーション |×| で示された部分のリレーション編集イメージです。


 [g在庫基準日]は日付のグローバルフィールドという特殊なフィールドで、詳しくは FileMaker のマニュアルを参照してください。ユーザはこのフィールドで在庫算出の起点となる日付 ― 在庫基準日 を指定します。

 そして、こちらが在庫算出用フィールド=[c在庫数]の計算式です。

繰越在庫数- Sum(出庫明細_商品::数量) + Sum(入庫明細_商品::数量)


 システム導入時、[繰越在庫数]には各商品の導入時点での在庫数を入力します。導入時、在庫が無い商品については「0」を入力しておきます。
 上記の計算式もシンプルで、[繰越在庫数](=ここでは導入時点の在庫数)から[g在庫基準日]以前に出庫した商品数量を差し引いて、さらに[g在庫基準日]以前に入庫した商品数量を加え、在庫を算出しているだけです。
 [g在庫基準日]より後の日付の入出庫が[c在庫数]の計算式の対象とならないのは、上図「在庫算出TOG」のリレーションで「≧」によりそのように定義されているからです。
注:
  1. 本稿読者で「出庫明細/入庫明細テーブルに[出庫日]/[入庫日]があるのはおかしいではないか。  本来、この二つのフィールドは出庫ヘッダ/入庫ヘッダテーブルにあるべきで、冗長で正規化されていないではないか?」と考えた方 、 御尤もです。ただ、FileMaker 内蔵DBを使用し、在庫算出基準日を任意に指定可という仕様を前提にした場合、正規化を崩しても明細に日付を持たせるのが良い、というのが小社の知見です。
  2. 但し、ExecuteSQL 関数によるSQLクエリを使用して出庫と出庫明細、入庫と入庫明細を結合(JOIN)すれば、[出庫日]/[入庫日]がそれぞれの明細テーブルに存在しなくても、在庫数を取得することができます。ただ、FileMaker の結合は実行速度が非常に遅くなることがあるので、留意が必要です。この手法についてはこちらを参考にしてください。
  3. 上記1の通りに設計する場合、出庫ヘッダ/入庫ヘッダの[出庫日]/[入庫日]と、出庫明細/入庫明細テーブルの[出庫日]/[入庫日]は常に一致するようにシステム的に保証しなければなりません。ユーザがヘッダ部の[出庫日]/[入庫日]を変更することは頻繁にあるので、スクリプトトリガを使用して、うまく制御してください。

 上記の注2 の部分が少し面倒ですが、ここまでは比較的簡単にアプリを作成できると思います。

 ただ、入出庫レコードが数千、数万と増えるに従い、在庫(c在庫数)の表示には時間がかかるようになります。在庫算出処理の時間を短くするためには、繰越処理を行い、[c在庫数]の計算対象となる入出庫レコードを減らす必要があります。 繰越処理とそれに伴う処理については、 簡単? FileMakerで在庫管理(2)―― 繰越処理の考え方 をご覧ください。

TOG(テーブル・オカレンス・グループ) とは?

FileMaker Pro 7 以降に導入されたリレーションシップグラフでは、テーブルを別名、または同一名で定義して配置できます。
 たとえば「商品」というテーブルは、リレーションシップグラフ上では「商品Table1」、「商品テーブル」、「商品」など自由に名前をつけて配置したり、同一のテーブルを別名で複数配置したりできます。

 リレーションシップシップウィンドウに配置されたテーブルは、単に”テーブル”と呼ばれたり、テーブルのエイリアスあるいはTO(テーブル・オカレンス)と呼ばれることもあります。

 FileMaker界隈 では TO という呼び名が一般的になってきているようです。また、処理別やレイアウト別に TO をグループ化したものを TOG (テーブル・オカレンス・グループ)と呼んでいます。


 以下の動画では『FMEasy在庫 IWP/WD 1.5』という製品を使用し、在庫算出に関して説明しています。




 その他の在庫関連記事を読む

(土屋)