在庫管理のDB設計

仕入、売上などを管理する物流業務システムで商品の在庫数も管理する必要がある時、
どのようなDB設計が良いのか調べてもなかなか参考になる記事が見当たらなかったので、自分なりの考えた在庫管理のDB設計を紹介していきます。

ER図

仕入、売上は「仕入-仕入明細」のようにそれぞれ親子関係になっています。
棚卸しの管理もするので棚卸しテーブル(inventories)も作成しています。
process_typesというマスタテーブルを作成し各工程をマスタ化します。

process_types

id	process_name
1	sales
2	arrival
99	inventory

在庫は在庫テーブル(stocks)で管理し、各明細テーブルと1対1の関係にします。
明細テーブルに在庫テーブルの外部キーを設定するため、データ挿入の順序は
親テーブル→在庫テーブル→明細テーブル
になります。
こうすることで明細テーブルと在庫テーブルの参照整合性を保証するようにしています。

在庫数の計算クエリ

在庫数を計算するSQL文を作成していきます。
最後に棚卸しが行われた日付以降の仕入、売上の商品数を集計します。

SELECT 
	t1.id AS product_id
	, (MAX(CASE WHEN t2.process_type_id = 99 THEN t2.quantity ELSE 0 END) + SUM(CASE WHEN t2.transaction_type_id = 2 THEN t2.quantity ELSE 0 END) - SUM(CASE WHEN t2.transaction_type_id = 1 THEN t2.quantity ELSE 0 END)) AS quantity
FROM products t1
INNER JOIN stocks t2
	ON t1.id = t2.product_id 
	t2.registered_at <= {TODAY}
INNER JOIN 
(SELECT
	t1.id AS product_id
	-- 最後に行われた棚卸し日を取得
	, MAX(CASE WHEN t2.process_type_id = 99 THEN t2.registered_at ELSE '1990-01-01 00:00:00' END) AS latest_product_stock_adjustment_date
FROM products t1
INNER JOIN stocks t2
ON t1.id = t2.product_id
WHERE t2.registered_at <= {TODAY}
GROUP BY t1.id) AS t3
ON t1.id = t3.product_id
AND t2.registered_at >= t3.latest_product_stock_adjustment_date
GROUP BY t1.id 

{TODAY}には現在時刻を指定することで現時点の在庫数を確認できます。
過去の日時を指定すると当時の在庫数も確認することができるようになります。