Zenn
🙌

【DB】ログで作る在庫管理DB設計 -時点在庫-【Part3/3】

2024/09/14に公開
db設計
ログ
在庫管理
tech

どうも、りんです。
今回はログで作る在庫管理のDB設計パート３です。

https://zenn.dev/rin0107/articles/b58595f593d609

https://zenn.dev/rin0107/articles/99debc0b5a3d40

今回はこれまでに見たフローベースの課題、在庫数量の計算量を解消するために時点在庫を保存する別テーブルを検討します。

時点在庫テーブル

時点在庫テーブルとは

ある時点での在庫数量を示すストックベースのテーブル

です。

これを用意して、ログベースの課題である計算量を改善します。

在庫数量の計算と課題

在庫数量の計算方法はパート１でご紹介した通りです。
https://zenn.dev/rin0107/articles/b58595f593d609#フローベースの利点

要約すると、在庫から「入るログ（数量プラス）」と「出ていくログ（数量マイナス）」をSUMすることで在庫数量を計算する方法です。

在庫量計算（出庫）
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 |  引当可能   |
+---------+-------------+---------------+-------------+

課題

この設計の課題は

現在時点までの全てのログを取得し、計算する必要がある点

です。

これを解消するために、時点在庫を保存し、ある時点までは時点在庫、時点在庫以降はログを集計するという方法を取ります。

テーブル設計

時点在庫を保存するテーブル、inventoriesを用意します。

特徴は以下２点です。

  • status：warehouse_logsと共通したstatus（「引当可能」など）
  • closed_at：時点在庫を保存した時点（＝棚卸し時点）

在庫数量の計算は以下のように行います。

在庫量計算（引当可能）
SELECT id, item_id, location_id, quantity, status, created_at FROM warehouse_logs;
+----+---------+-------------+----------+------------+---------------------+
| id | item_id | location_id | quantity |    status  |       created_at    |
+----+---------+-------------+----------+------------+---------------------+
|  1 |       1 |           1 |       10 |  引当可能   | 2024-01-01 00:00:00 |
|  2 |       1 |           1 |       -5 |  引当可能   | 2024-01-15 00:00:00 |
+----+---------+-------------+----------+------------+---------------------+

INSERT INTO inventories (item_id, location_id, quantity, status, closed_at) VALUES (1, 1, 5,　'引当可能',　'2024-01-31 23:59:59');

SELECT id, item_id, location_id, quantity, status, closed_at FROM inventories;
+----+---------+-------------+----------+------------+---------------------+
| id | item_id | location_id | quantity |    status  |       closed_at     |
+----+---------+-------------+----------+------------+---------------------+
|  1 |       1 |           1 |       5  |  引当可能   | 2024-01-31 23:59:59 |
+----+---------+-------------+----------+------------+---------------------+

INSERT INTO warehouse_logs (item_id, location_id, type, reason, status, quantity, created_at) VALUES (1, 1, 'receipt', '入庫', '引当可能', 1, '2024-02-01 00:00:00');

SELECT id, item_id, location_id, quantity, status, created_at FROM warehouse_logs;
+----+---------+-------------+----------+------------+---------------------+
| id | item_id | location_id | quantity |    status  |       created_at    |
+----+---------+-------------+----------+------------+---------------------+
|  1 |       1 |           1 |       10 |  引当可能   | 2024-01-01 00:00:00 |
|  2 |       1 |           1 |       -5 |  引当可能   | 2024-01-15 00:00:00 |
|  3 |       1 |           1 |        1 |  引当可能   | 2024-02-01 00:00:00 |
+----+---------+-------------+----------+------------+---------------------+

SELECT 
    (log_quantity + COALESCE(inventory_quantity, 0)) AS total_quantity,
    item_id, 
    location_id, 
    status
FROM
(
    SELECT 
        SUM(warehouse_logs.quantity) AS log_quantity,
        warehouse_logs.item_id, 
        warehouse_logs.location_id, 
        warehouse_logs.status
    FROM warehouse_logs
    LEFT JOIN 
    (
        SELECT 
            SUM(quantity) AS inventory_quantity,
            item_id, 
            location_id, 
            status, 
            MAX(closed_at) AS latest_closed_at 
        FROM inventories 
        GROUP BY item_id, location_id, status
    ) AS latest_inventories
    ON warehouse_logs.item_id = latest_inventories.item_id
    AND warehouse_logs.location_id = latest_inventories.location_id
    AND warehouse_logs.status = latest_inventories.status
    AND warehouse_logs.created_at > latest_inventories.latest_closed_at
    GROUP BY warehouse_logs.item_id, warehouse_logs.location_id, warehouse_logs.status
) AS combined_quantities;

+---------+-------------+---------------+-------------+
| item_id | location_id | SUM(quantity) |   status    |
+---------+-------------+---------------+-------------+
|       1 |           1 |             6 |  引当可能    |
+---------+-------------+---------------+-------------+

最終的な結果を取得するクエリは複雑になりますが、これによってログテーブルの計算量を減らして在庫数量の計算が可能となります！

最後に

在庫数量をログベースのテーブル設計で取得するには、ストックベースの設計よりも考慮すべきことが多いですが、一方でそのメリットもあります。
システムの目的に合わせた設計を選ぶ一助となれば幸いです。

Discussion