在庫管理システムをSQLで構築する手順：テーブル設計から集計クエリまで解説

-- 商品マスタテーブルの作成
CREATE TABLE products (
    product_id INT PRIMARY KEY, -- 商品ID
    product_name VARCHAR(100) NOT NULL, -- 商品名
    category VARCHAR(50), -- カテゴリ
    standard_cost DECIMAL(10, 2), -- 標準仕入単価
    list_price DECIMAL(10, 2), -- 販売価格
    reorder_level INT -- 発注点 (この値を下回ったら発注)
);

-- 入出庫履歴テーブルの作成
CREATE TABLE stock_transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY, -- 取引ID
    product_id INT, -- 商品ID
    transaction_type ENUM('IN', 'OUT', 'ADJUST') NOT NULL, -- IN:入荷, OUT:出荷, ADJUST:調整
    quantity INT NOT NULL, -- 動いた数量
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 取引日時
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 現在の在庫状況を保持するテーブル
CREATE TABLE inventories (
    product_id INT PRIMARY KEY,
    quantity_on_hand INT DEFAULT 0, -- 現在の手元在庫
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 商品の登録
INSERT INTO products (product_id, product_name, category, standard_cost, list_price, reorder_level)
VALUES (1, '高性能ノートPC', '電子機器', 80000, 120000, 5);

INSERT INTO products (product_id, product_name, category, standard_cost, list_price, reorder_level)
VALUES (2, 'ワイヤレスマウス', '周辺機器', 1500, 3500, 20);

-- 初回入荷の記録 (100台入荷)
INSERT INTO stock_transactions (product_id, transaction_type, quantity)
VALUES (1, 'IN', 100);

-- 在庫テーブルの更新 (初回登録)
INSERT INTO inventories (product_id, quantity_on_hand)
VALUES (1, 100);

-- 履歴から算出した現在在庫とマスタ情報の結合
SELECT 
    p.product_name,
    SUM(CASE WHEN st.transaction_type = 'IN' THEN st.quantity 
             WHEN st.transaction_type = 'OUT' THEN -st.quantity 
             WHEN st.transaction_type = 'ADJUST' THEN st.quantity 
             ELSE 0 END) AS calculated_stock
FROM 
    products p
JOIN 
    stock_transactions st ON p.product_id = st.product_id
GROUP BY 
    p.product_id, p.product_name;

product_name      | calculated_stock
------------------|-----------------
高性能ノートPC     | 100
ワイヤレスマウス   | 0

-- 在庫が発注点を下回っている商品の抽出
SELECT 
    p.product_name,
    i.quantity_on_hand,
    p.reorder_level,
    (p.reorder_level - i.quantity_on_hand) AS shortage_quantity
FROM 
    products p
JOIN 
    inventories i ON p.product_id = i.product_id
WHERE 
    i.quantity_on_hand <= p.reorder_level;

START TRANSACTION;

-- 1. 履歴を挿入
INSERT INTO stock_transactions (product_id, transaction_type, quantity)
VALUES (1, 'OUT', 5);

-- 2. 在庫数をマイナス更新
UPDATE inventories 
SET quantity_on_hand = quantity_on_hand - 5 
WHERE product_id = 1;

COMMIT;

CREATE TRIGGER after_stock_transaction_insert
AFTER INSERT ON stock_transactions
FOR EACH ROW
BEGIN
    IF NEW.transaction_type = 'IN' THEN
        UPDATE inventories 
        SET quantity_on_hand = quantity_on_hand + NEW.quantity 
        WHERE product_id = NEW.product_id;
    ELSEIF NEW.transaction_type = 'OUT' THEN
        UPDATE inventories 
        SET quantity_on_hand = quantity_on_hand - NEW.quantity 
        WHERE product_id = NEW.product_id;
    END IF;
END;

-- 日付検索を高速化するインデックス
CREATE INDEX idx_transaction_date ON stock_transactions(transaction_date);

-- 商品ごとの集計を高速化するインデックス
CREATE INDEX idx_product_id ON stock_transactions(product_id);