ルックアップの機能はFileMakerではお馴染みでしょう。FileMakerがリレーショナルデータベースである以前のかなり初期の頃からあった機能です。むしろ、大昔はこの機能だけがテーブル間連携だった時代もあります。その後、リレーショナルデータベースの仕組みが組み込まれても、ルックアップの仕組みは残っています。つまり、必要だから残っているとも言えます。そのこともあって、筆者が開発しているフレームワークのINTER-Mediatorでもルックアップはサポートしています。ということで、「さまざまな開発ツールでサポートされているルックアップ」と言いたいところですが、どちらかと言えば、ローコード系でよく実装されているということになるでしょうか。
ルックアップの詳細を説明する前に、まず、テーブル間結合のことをおさらいしましょう。お馴染みの、販売明細と商品の表をまたまた出してきます。サロゲートキーを主キーにしました。そして、それぞれの表の商品IDが照合の手がかりです。この2つの表を結合することで、下のような表が得られるので、ここでの「商品明細」だけではわからなかった商品名や単価が、販売１つ１つのレコードに追加されており、例えば、1行目の商品は800円などとわかるようになります。その結果、納品書の明細に記載ができるデータになったとも言えますし、単価と個数を掛け算して、金額を求めることができるようになったとも言えるわけです。ここで、下の表を見る限りは、商品IDから商品名や単価が決定される、つまり関数従属性があることから、「商品」の表が分離できるということが、正規化の理論でも実証されているので、表を分離して管理するということがデータベースの流儀ということになります。
ここで、商品の価格が変わったとします。例えば、ロボットいか2号が850円になったとします。そこで、商品の中の１レコードの単価フィールドを850に更新しました。更新はこの１箇所だけ行ったとします。その後に、販売明細と商品を結合した下の表を見ると、当然ながら、ロボットいか2号の単価は850円になっていますが、２箇所登場しているいずれも850円になっています。大元が変わったから、それを参照している複数の箇所で結果的に新しい値が得られています。
この「修正」が、「間違っていたから」行ったものであれば、これで、無事に正確なデータを記録できたことになり、それでOKです。納品書を再発行しないと行けないような気はしますが、データベース上の話はこれで終わりかと思います。しかしながら、実は11月10日に、価格改定が行われたとしたらどうでしょうか？つまり、11/9の販売は、800円のまま、11/11の販売は850円にしたかったと言っても商品テーブルを修正した結果、過去の全てのデータに対して更新されてしまった結果になります。
そうならないために、どうすればいいか？ もちろん、ルックアップを使うのですが、まず、なぜルックアップなのかということは傍に置いておき、ルックアップを使った結果を示します。同じようなデータですが、販売明細に「商品名」と「単価」のフィールドを用意します。正規化が崩れるじゃないかと思われるかもしれませんが、その件は、後から（次回）に説明します。この新たに作ったフィールドは、商品テーブルに対応する値を複製した結果を残すために用意します。複製をするというのがポイントになります。販売情報としては、販売日〜個数までの4つのフィールドで事は足りているので、まずは新しい2つのフィールドは空欄とします。そして、テーブル結合しても、やはり空欄のままです。
ここで、ルックアップは一般には次のような動作をします。ここでは、商品との関連を商品IDで照合していますので、外部キーにあたる販売明細の商品IDが更新された場合、結合した先にある「商品」の表の「商品名」「単価」を、販売明細の「商品名」と「単価」に複製するということを行います。外部キーをトリガーにして、リレーションシップがある先のレコードから複製するという動作です。ここでの商品IDは、例えば、ポップアップメニューで選択できるようになっていれば、メニューを選択したときに、101などの値がフィールドに設定されるとともに、システムの背後で商品名と単価のコピーが行われます。これがルックアップの動作原理です。単に「取ってくる」ということではなく、どのフィールドの更新がトリガーになっているか、そしてどのテーブルから取り出し、結果、どのフィールドに値を入れるのかということで、把握すべき事態はたくさんあります。ですが、把握すべきことは全部データベースの設計に絡んでいることであるとも言えます。
なお、こうしたルックアップを複数のレコードでまとめて行う機能もありますが、通常は入力に伴って複製されることになり、その場合は１レコードずつの処理になります。通常、複製先にデータが入力されていれば、つまりNULLか””でない場合は、複製を行わないように設定するのが一般的かと思われますが、この動作はオプションになっているので、設計者が適切に選択しなければなりません。このオプションはどっちがいいのかというよりも、ユーザーインタフェースの動作や、あるいはシステムの要求がどうなっているのかに関わることであり一概には言えませんが、安全策としてはデータがあれば複製しないようにしておき、再度のルックアップ処理をするためには一度フィールドの内容をクリアするというような措置を取るようにするのが安全です。ですが、そのことを警告してルックアップはしなかったことを適切にユーザに示すことなどが必要になり、ユーザインタフェースは込み入ってきます。
さて、ここで、商品のある単価が845円になりました。しかしながら、販売明細の単価は複製した値なので、そのまま800円のままになります。これは、すでに別々のフィールドになっているので連動しないのは当然のことです。
そして、別の日に売上明細にレコードが3行追加されました。ルックアップが稼働する前は、商品名と単価は空です。
商品テーブルと結合行い、その結果から、空欄になっている追加した３つのレコードの「商品名」と「単価」を埋めました。すでに入力されている最初の２行はそのままにします。ルックアップをもちろん、そのように動作させるということです。当然ながら、新たに追加した４行目の販売データでは、単価は更新した845円になります。
ルックアップを適用した結果「販売明細」のテーブルでは、このように商品名と単価の空欄部分が埋まりましたが、販売日によって、ロボットいか2号の単価が違うという状況が「正しく」記録されたことになります。
これが一般的なルックアップの機能の紹介です。リレーションシップの上で正しいかどうかということは議論のネタかもしれませんが、その前に、このルックアップは途中で説明した通り、フィールドの更新によって稼働します。つまり、何らかのトリガーがあって、そのトリガーにより、該当する単一のレコードのフィールドが、別のテーブルから取ってきた値で上書きされるという仕組みです。上記の図では図では２、あるいは３レコードをまとめて更新しましたが、FileMakerでは単一のレコードごとに複製されることが一般的ではないかと思われます。こうした複雑な動作を設定することは結構間違えやすいのですが、見通しを良くする方法としては、データベース設計をきちんと行って、何と何が関連性があるのかということを完全に把握しておくことが挙げられます。
このルックアップが開発ツール等に実装されていれば、手軽に使えるのですが、実装がない場合は自分で実装しないといけなくなります。これは仕方ないことになります。
このルックアップはセットアップの大変さはあるものの、ある意味単純な機能ではあります。そして、意外に業務で発生するさまざまな要求をうまく飲み込んでくれる機能でもあります。なんでもルックアップにすればいいということではない一方で、複雑な仕組みを作らなくてもルックアップで実運用に耐えられるような仕組みが作れるということは、すでにFileMakerの長年の実績で証明されていると言えるでしょう。その辺りの理由は次回に説明します。