🚶
【DB】ログで作る在庫管理DB設計 -特定在庫の状態遷移を表現する-【Part2/3】
どうも、りんと申します。
今回はログで作る在庫管理のDB設計パート２です。
パート１はこちら
パート２、今回は特定在庫の状態遷移の表現についてお話したいと思います。
今回の主題
今回の技術的内容は正直大したことありません、
私がこの記事に残したいのは、教訓です。
1つのテーブルが表現するものをしっかりと定めて、そこから外れるものは別のテーブルで表現しましょう。
Divide And Conquer（分割して統治せよ）という原則を常に心に持とうという話です。
前回までのあらすじ
前回は、フローベースの在庫管理DB設計について提案しました。
フローベース
次に、今回ご提案したい、ログを使って在庫を管理するという設計です。
これをフローベースのWMSと呼びます。
しかし、この設計では特定在庫の状態遷移が表現できないという課題がありました。
フローベースのログテーブルのみでは、「どこにどの在庫がいくつあるのか」は管理できても、「特定の在庫が今どの工程の中にあり、その在庫を特定して、次はどの工程へ遷移させるのか」は管理が困難です。
そのため特定在庫の状態遷移を表現するために、別テーブルを作ります。
特定在庫の状態遷移とは
まず、到底在庫の状態遷移を定義します。
| 遷移元 | 遷移先 | action |
|---|---|---|
| 引当可能 | 引当中 | 引当 |
| 引当中 | 引当可能 | 引当キャンセル |
| 引当中 | 出庫 | 出庫 |
これらを、特定の在庫において管理します。
例
🤔<location1 にある引当可能なAを1つ引き当てて、引当したそれを出庫したい。
特定の在庫 = location1にある引当可能なAを１つ
状態遷移 = 引当可能 → 引当中 → 出庫
表現方法
ログテーブルの目的は、在庫数量の管理であり、特定在庫を管理することを目的としていません。
そのため、ここでは単純に新しく特定在庫を管理することを目的とするテーブルを作ります。
このテーブルに、表現したい特定在庫ごとにレコードを挿入し、その在庫の状態が遷移するに従ってレコードをUPDATEします。
また、この時にログを作ることを忘れないようにしましょう。
今回はログテーブルにもstatusカラムを追加しました。
statusには、引当可能・引当中などを格納し、論理的な在庫区分を分けて集計できるようにしました。
あくまで在庫数の表現はログテーブルで行うようにすることが重要です。
引当前
SELECT * FROM stocks;
+---------+-------------+------------+----------+
| item_id | location_id | status | quantity |
+---------+-------------+------------+----------+
+---------+-------------+------------+----------+
SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+-------------+
| id | item_id | location_id | type | reason | quantity | status |
+----+---------+-------------+---------+--------------+----------+-------------+
| 1 | 1 | 1 | receipt | 入庫 | 10 | 引当可能 |
| 2 | 1 | 1 | issue | 出庫 | -5 | 引当可能 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 | 引当可能 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 | 引当可能 |
+----+---------+-------------+---------+--------------+----------+-------------+
SELECT item_id, location_id, SUM(quantity), status FROM warehouse_logs GROUP BY item_id, location_id, status;
+---------+-------------+---------------+-------------+
| item_id | location_id | SUM(quantity) | status |
+---------+-------------+---------------+-------------+
| 1 | 1 | 2 | 引当可能 |
| 1 | 2 | 3 | 引当可能 |
+---------+-------------+---------------+-------------+
引当中
INSERT INTO stocks (item_id, location_id, status, quantity) VALUES (1, 1, 'allocated', 1);
SELECT * FROM stocks;
+---------+-------------+------------+----------+
| item_id | location_id | status | quantity |
+---------+-------------+------------+----------+
| 1 | 1 | 引当中 | 1 |
+---------+-------------+------------+----------+
SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+-------------+
| id | item_id | location_id | type | reason | quantity | status |
+----+---------+-------------+---------+--------------+----------+-------------+
| 1 | 1 | 1 | receipt | 入庫 | 10 | 引当可能 |
| 2 | 1 | 1 | issue | 出庫 | -5 | 引当可能 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 | 引当可能 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 | 引当可能 |
| 5 | 1 | 1 | issue | 引当 | -1 | 引当可能 |
| 6 | 1 | 1 | receipt | 引当 | 1 | 引当中 |
+----+---------+-------------+---------+--------------+----------+-------------+
SELECT item_id, location_id, SUM(quantity), status FROM warehouse_logs GROUP BY item_id, location_id, status;
+---------+-------------+---------------+-------------+
| item_id | location_id | SUM(quantity) | status |
+---------+-------------+---------------+-------------+
| 1 | 1 | 1 | 引当可能 |
| 1 | 2 | 3 | 引当可能 |
| 1 | 1 | 1 | 引当中 |
+---------+-------------+---------------+-------------+
出庫
UPDATE stocks SET status = 'shipped' WHERE id = 1;
SELECT * FROM stocks;
+---------+-------------+------------+----------+
| item_id | location_id | status | quantity |
+---------+-------------+------------+----------+
| 1 | 1 | 出荷済み | 1 |
+---------+-------------+------------+----------+
SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+-------------+
| id | item_id | location_id | type | reason | quantity | status |
+----+---------+-------------+---------+--------------+----------+-------------+
| 1 | 1 | 1 | receipt | 入庫 | 10 | 引当可能 |
| 2 | 1 | 1 | issue | 出庫 | -5 | 引当可能 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 | 引当可能 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 | 引当可能 |
| 5 | 1 | 1 | issue | 引当 | -1 | 引当可能 |
| 6 | 1 | 1 | receipt | 引当 | 1 | 引当中 |
| 7 | 1 | 1 | issue | 引当 | -1 | 引当中 |
+----+---------+-------------+---------+--------------+----------+-------------+
SELECT item_id, location_id, SUM(quantity), status FROM warehouse_logs GROUP BY item_id, location_id, status;
+---------+-------------+---------------+-------------+
| item_id | location_id | SUM(quantity) | status |
+---------+-------------+---------------+-------------+
| 1 | 1 | 1 | 引当可能 |
| 1 | 2 | 3 | 引当可能 |
+---------+-------------+---------------+-------------+
出荷ではなく引当キャンセル
DELETE FROM stocks WHERE item_id = 1 AND location_id = 1;
SELECT * FROM stocks;
+---------+-------------+------------+----------+
| item_id | location_id | status | quantity |
+---------+-------------+------------+----------+
+---------+-------------+------------+----------+
SELECT * FROM warehouse_logs;
+----+---------+-------------+---------+--------------+----------+-------------+
| id | item_id | location_id | type | reason | quantity | status |
+----+---------+-------------+---------+--------------+----------+-------------+
| 1 | 1 | 1 | receipt | 入庫 | 10 | 引当可能 |
| 2 | 1 | 1 | issue | 出庫 | -5 | 引当可能 |
| 3 | 1 | 1 | issue | 拠点移動 | -3 | 引当可能 |
| 4 | 1 | 2 | receipt | 拠点移動 | 3 | 引当可能 |
| 5 | 1 | 1 | issue | 引当 | -1 | 引当可能 |
| 6 | 1 | 1 | receipt | 引当 | 1 | 引当中 |
| 7 | 1 | 1 | issue | 引当キャンセル | -1 | 引当中 |
+----+---------+-------------+---------+---------------+----------+-------------+
SELECT item_id, location_id, SUM(quantity), status FROM warehouse_logs GROUP BY item_id, location_id, status;
+---------+-------------+---------------+-------------+
| item_id | location_id | SUM(quantity) | status |
+---------+-------------+---------------+-------------+
| 1 | 1 | 1 | 引当可能 |
| 1 | 2 | 3 | 引当可能 |
+---------+-------------+---------------+-------------+
最後に
ログテーブルを用いることで、在庫数量の変動を上手く表現することは可能です。
ただし、テーブルの目的を見落としてしまい、何でもログテーブルで表現しようとすることは間違いです。（自戒）
適切にテーブルの目的を定め、目的に合わせた設計を心がけたいものです。
Discussion