大がかりなシステムをいきなり作るのではなく、まずは「Excelで入力し、CSVに一時保存し、最終的にデータベースサーバーへ登録する」という、現実的で身の丈に合った構成を軸に検討を進めた。この記事では、その検討過程で出てきた論点と、それぞれにどう対処することにしたかをまとめておく。
基本構成
大枠はシンプルで、次の4ステップになる。
- 現場のPCでExcelシートに製造記録を入力する
- 保存ボタンを押すとCSVファイルとして一時保存される
- その製品の製造が完了したら、登録ボタン一つでデータベースサーバーに登録される
- CSVファイルは1つの製品番号ごとに1ファイルとする
いきなりデータベースサーバーに直接書き込む方式も考えたが、まずはCSVという中間形式を経由させることで、DBサーバーとの接続が不安定な現場でも入力作業自体は止まらない、という利点がある。この段階ではSQLサーバーのようなデータベースサーバーへの登録は見送り、CSVでの一時保存にとどめることにした。
CSVという中間ファイルにまつわる懸念
この構成を考えたときにまず気になったのは、CSVという「確定していないデータ」を一時的に扱うことの危うさである。具体的には次のような点が問題になり得る。
- 登録ボタンを押す前にPCがフリーズしたり停電したりすると、記録が丸ごと失われる
- ExcelでCSVを保存する際の文字コードの扱いを誤ると、日本語が文字化けする
- 製造記録は監査やトレーサビリティの対象になりやすく、「誰が・いつ入力したか」「登録前に改ざんされていないか」という証跡が求められる可能性がある
これらへの対応として、CSVを一時ファイル名で書き込んでから完了後にリネームする「アトミックリネーム方式」を採用することにした。書き込みが一瞬で完結するリネームを最後に行うことで、中途半端な状態のファイルが正式なファイル名で存在することがなくなる。
トランザクションの問題をどう考えるか
複数人が同じCSVファイルに同時アクセスする可能性についても検討した。ただし今回の業務フローでは、1つの製品は1つの工程を通る際に1台のPCからしかアクセスされないという前提があるため、同時書き込み競合そのものは基本的に発生しない。
とはいえ、単独アクセスの前提であっても、次のような「もう1つのアクセス」が意図せず発生する可能性は残る。
- 誰かが確認のためにそのCSVをExcelで開いてしまい、ロックがかかる
- OneDriveやウイルス対策ソフトの同期・スキャンが裏でファイルを一時的に掴む
これに対しては、VBAで排他モードでのOpenを試し、失敗したら少し待ってリトライする、という仕組みを保存処理に組み込むことにした。事前にファイルが使用中かどうかをチェックする関数も作れるが、チェックした直後に別プロセスがファイルを掴む可能性(TOCTOU問題)があるため、実際の保存処理自体をリトライ付きにする方が確実である。
この仕組みにより、DBのトランザクションほど厳密ではないにせよ、実務上十分な程度の排他制御は実現できると考えている。1回の保存操作が単一レコード分のシンプルな書き込みであれば、途中で壊れるリスクも小さい。
さまざまな記録紙への汎用化
製造現場には製造記録以外にもさまざまな種類の作業記録が存在し、それぞれに個別のプログラムを作るのは現実的ではない。そこで、記録紙ごとに異なるExcelシートの入力セル配置を吸収する、汎用的な仕組みを考えることにした。
鍵になるのは「名前付き範囲」である。マクロが直接セル番地を参照するのをやめ、Excelの「名前の定義」機能で入力セルにフィールド名を割り当て、マクロ側はブック内に定義されている名前を列挙して値を取得する、という形にする。これにより、記録紙が増えてもマクロ側のコードは変更不要になり、新しい記録紙を作る際はテンプレート側で名前を定義するだけで済む。
共通処理の配布方法としては、Excelアドイン(.xlam)を使う方法も検討したが、今回はアドインを使わず、クラスモジュールを共有フォルダに置き、各ブックを開いたタイミングでVBComponents.Importを使って最新版を取り込む方式を採用する方向で考えている。ブックにブートストラップ用の小さなマクロだけを仕込んでおけば、共通ロジックの修正は共有フォルダ上のファイル1つを更新するだけで全記録紙に反映できる。
CSVのデータ構造 ― 縦持ちという選択
記録紙ごとに項目数や項目名が異なるため、CSVを「1行1レコード、列がフィールド」という横持ちの形式にすると、記録紙が変わるたびに列構成も変わってしまい汎用化しづらい。そこで、CSVを次のような「縦持ち(key-value形式)」にすることにした。
これなら記録紙が変わってもCSVの構造自体は「フィールド名,値」を軸にした形で統一でき、VBAはOpen/Print/Line Inputだけで読み書きでき、外部のJSONライブラリなどを導入する必要もない。1つの製品が複数の工程を通るたびに、担当工程がこの同じCSVファイルに行を追記していく運用にする。同じ工程を再検査などでやり直した場合に区別がつくよう、工程コードに加えて工程実行回や記録日時も持たせている。
DB登録はUPSERT、履歴はCSVに任せる
DBへの登録処理は、単純なINSERTの繰り返しではなく、同じキー(製品番号・工程コード・フィールド名)の組み合わせであれば上書きするUPSERT方式にする。これにより、DB側は常に「その製品の最新状態」を保持するテーブルになる。
一方で、上書きしてしまうと過去の記録(たとえば最初は不合格だった検査が、再検査で合格になった経緯)が失われるのではないか、という懸念もあった。しかしよく考えると、CSVはすでに工程ごとに追記していく設計になっているため、CSV自体がそのまま変更履歴を保持していることに気づいた。つまり、
- CSV:誰が・いつ・どの工程で・何を記録したかが追記順にすべて残る、履歴の正
- DB:そこから作られる「今この製品はどういう状態か」を示す最新状態のスナップショット
という役割分担にすることで、DB側の設計をシンプルに保ったまま、履歴も別途失われずに残せる。これはいわゆるイベントソーシング的な考え方に近い。この前提を守るため、完了フォルダに移動したCSVは読み取り専用にし、上書き・削除しない運用を徹底する必要がある。
なお、この方式は「特定の製品の履歴を追う」用途には強いが、「不合格になった記録を全製品横断で探す」といった横断検索には向かない。ただし今回の要件は主に「この製品はどうだったか」を後から追うことが中心であり、大きな問題にはならないと考えている。将来的に横断的な傾向分析が必要になれば、その時点でDB側に履歴テーブルを追加すればよい。
ファイルの置き場所と、製品番号からの探し方
CSVファイルは製品番号をファイル名にしているため、何か問題が起きたときはまず製品番号からファイルを探しに行くことになる。ただし、すべてのCSVを1つのフォルダに置くとファイル数が増えたときの速度面が心配になるため、保存日付ごとにフォルダを分けることにした。
そうなると今度は「どの日付フォルダにその製品番号のファイルがあるか」が分からなくなる、という新しい問題が出てくる。この解決策として、大掛かりな仕組みは使わず、製品番号とフォルダ名のペアだけを記録したシンプルな索引用CSVファイルを1つ用意することにした。新しい製品の製造が始まった時点でこの索引ファイルに1行追記しておけば、後から製品番号で検索するだけで該当ファイルの場所にたどり着ける。
この索引ファイルは、これまでの製品ごとのCSVとは異なり「どの工程・どのラインからでも新規製造開始のたびに書き込まれる」ため、複数のPCから同時に追記が発生する可能性がある。したがって、この索引ファイルにこそ、リトライ付きの排他制御と追記オンリーの運用を最も厳密に適用する必要がある。件数がある程度増えても、線形探索で実用上問題ない規模であれば、この単純な仕組みで十分だと考えている。
まとめ
今回の検討を通じて見えてきたのは、必ずしも最初から本格的なデータベース中心の設計にする必要はなく、
- CSVという軽量な中間形式を「履歴の正」として活用する
- 名前付き範囲によってセル配置の違いを吸収し、処理ロジックを汎用化する
- 索引もまた同じCSVという枠組みの中でシンプルに解決する
という形で、身の丈に合った構成でも十分に堅牢な仕組みが作れる、ということだった。大掛かりな基盤を構えなくても、既存のExcel運用の延長線上で電子化を進められるという手応えが得られたのは収穫だった。次はこの設計をもとに、実際にプロトタイプを作って現場で試してみたいと考えている。
