【DB】ログで作る在庫管理DB設計【Part1/3】
皆さん、どうぞよろしくお願いします。りんと申します。
今回はログで作る在庫管理のDB設計について、ご提案したいと思います。
はじめに
皆さんは 在庫管理システム（WMS） をご存知でしょうか？
その名の通り、在庫を把握して管理するシステムですね。
私を含め、初学者の方も開発した経験があるのではないでしょうか。
自分が開発したWMSを思い出してみてください。
🤔...
多くのWMSが
一品目ごとにレコードを持ち、在庫数量を一つのデータとして保存して、変更の際はUPDATEする
そんな設計だったのでは？
😒<そうだけど？
今回はそんな設計の課題を提示し、別の設計をご提案しようと思います。
WMSの例
今回は以下のような在庫管理を実現するWMSを開発する前提とします。
- 複数品目が存在する
- 在庫の移動が存在する（入庫・出庫・拠点移動など）
設計
ストックベース
まずは
一品目ごとにレコードを持ち、在庫数量を一つのデータとして保存してUPDATEする
という設計です。
これをストックベースのWMSと呼びます。
フローベース
次に、今回ご提案したい、ログを使って在庫を管理するという設計です。
これをフローベースのWMSと呼びます。
注意点
フローベースのログテーブルにはアプリケーション設計に大きく関わる必須事項があります。
- ログは１イベントにつき２レコード生成する。（入庫と出庫を除く）
- ログの
数量カラムにはプラス・マイナスの両方の値が入る。
具体例
mysql> SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+
| id | item_id | location_id | type | reason | quantity |
+----+---------+-------------+---------+--------------+----------+
| 1 | 1 | 1 | receipt | 入庫 | 10 |
| 2 | 1 | 1 | issue | 出庫 | -5 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 |
+----+---------+-------------+---------+--------------+----------+
ログは１イベントにつきIssueとReceiptの２レコードを生成する必要があります。
これは、「どこから何が出ていって」（Issue）・「どこへ何が入ったのか」（Receipt）ということを表現するためです。
また、上記に合わせ、Issueの数量カラムはマイナスの値、Receiptの数量カラムはプラスの値とする必要があります。
😲<つまり、Aという品目がlocation1からlocation2に３個移動したときは、「location1からAが-3・location2へAが+3」っていう２レコードで１組のログを作る必要があるのか！
mysql> SELECT * FROM warehouse_logs WHERE reason = "拠点移動";
+----+---------+-------------+---------+--------------+----------+
| id | item_id | location_id | type | reason | quantity |
+----+---------+-------------+---------+--------------+----------+
| 3 | 1 | 1 | issue | 拠点移動 | -3 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 |
+----+---------+-------------+---------+--------------+----------+
この２つを組み合わせることで、フローベースの利点を実現することが可能になります。
ストックベースの課題
- 過去時点での在庫数量計算が複雑（になりがち）
- 在庫数量の変更をUPDATEで反映させる必要がある（重要）
まずは、現在時点ではなく過去・未来時点での在庫数量の計算が複雑になりがちです。
なぜなら、ストックベースの在庫テーブルでは現在時点での在庫数量しか持たないため、他テーブル（例えば「発注」や「出庫」のためのテーブル）を参照して計算を考える必要があります。
🤔＜えーと、在庫テーブル + 過去の発注 - 過去の出庫か...？
また大きな問題として、在庫数量を変更する際にUPDATEをかける必要があります。
そして複数作業者による同時操作が存在し得るのであれば、適切な排他制御が必要になります。
これはパフォーマンスを悪化させる要因になります。
また、アプリケーションでの制御も厳重に注意する必要があります。
フローベースの利点
- 過去時点での在庫数量計算が単純
- 在庫数量の変更をINSERTのみで反映させることが可能
フローベースでは、全ての在庫移動をログとして保存するため、過去の在庫数量計算をシンプルにすることが可能です。
😀<在庫品目や場所でGROUP BYして数量をSUMすれば良いのか！
mysql> SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+
| id | item_id | location_id | type | reason | quantity |
+----+---------+-------------+---------+--------------+----------+
| 1 | 1 | 1 | receipt | 入庫 | 10 |
| 2 | 1 | 1 | issue | 出庫 | -5 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 |
+----+---------+-------------+---------+--------------+----------+
mysql> SELECT item_id, location_id, SUM(quantity) FROM warehouse_logs GROUP BY item_id, location_id;
+---------+-------------+---------------+
| item_id | location_id | SUM(quantity) |
+---------+-------------+---------------+
| 1 | 1 | 2 |
| 1 | 2 | 3 |
+---------+-------------+---------------+
そして、在庫数量の変更は新たにログを追記することだけで表現可能です。
それは数量の変更や在庫移動のキャンセルなどを含みます。
😲<つまり、「発注キャンセル」や「出庫戻り」といったイレギュラーもクエリはそのままで対応できるってことか！
そのため、UPDATEを行うストックベースに比べて排他制御の検討を必要とせず、アプリケーションでの制御をシンプルにすることが可能です。
フローベースの課題
一方でフローベースの課題もあります。
- 在庫数量の計算量が大きい
何と言ってもこれです。ストックベースであれば現在時点の在庫数量の計算は不要で、在庫テーブルからレコードを取得すれば十分です。
一方でフローベースでは、現在時点までの全てのログを取得し、計算する必要があります。
😰<計算量エグそう...
実際、この計算量はボトルネックになり得ます。
そのため、以下のことを慎重に検討する必要があります。
- 計算クエリは最適化できているか？（不必要にフルスキャンを行っていないか？など）
- 本当に全てのログを取得して計算すべきか？
2つ目を検討すれば、多くの場合は時点在庫を保存する別テーブルを設計することになると思います。
棚卸し業務が存在する場合はその時点での在庫を保存する設計とすることで、業務要件を満たしつつ計算量を減らすことが可能でしょう。
※追記
時点在庫の設計も大きな課題を伴ったため、別記事を書こうかな！？
別観点の課題
また、今回検討していない在庫管理に伴う課題も存在します。
- 特定の在庫の業務工程に伴う状態遷移の表現
今回は考慮していませんが、在庫品の状態遷移というものが要件にはつきものです。
🤔<注文に対して在庫を出庫予定として予約する「引当」とか...
フローベースのログテーブルのみでは、「どこにどの在庫がいくつあるのか」は管理できても、「特定の在庫が今どの工程の中にあり、その在庫を特定して、次はどの工程へ遷移させるのか」は管理が困難です。
別の記事にて、この課題を解決するための別テーブルを検討したいと思います。
最後に
今回この記事を執筆する動機となったのは、ログベースの在庫管理DB設計の参考とできるソースが中々見当たらなかった苦労からでした。
参考とさせていただいた文献を以下に記載します。
この記事が誰かの助けになれば幸いです。
参考文献
Discussion