💥倉庫崩壊を阻止！🚚失敗しないWMS開発の極意 | 第2回: データベース設計の最適化

## はじめに
**倉庫管理システム**（`WMS`）の成功は、効率的で**スケーラブル**な**データベース**設計に大きく依存します。在庫管理や入出庫のデータを正確かつ高速に処理するためには、適切な**スキーマ**が不可欠です。この記事では、`WMS`のコアとなる**データベース**を設計する方法を、商品、在庫、**ロケーション**などのエンティティを中心に、具体的な`SQL`例、実際のユースケース、そして実践的な教訓とともに詳しく解説します。これにより、物流業務のニーズに応える柔軟で**スケーラブル**なシステムの基盤を構築します。

## なぜデータベース設計が重要か
`WMS`の**データベース**は、倉庫業務の心臓部です。以下のような理由から、初期の設計がシステム全体の成功を左右します：
- **データ整合性**：在庫データが不一致だと、誤出荷や在庫切れが発生し、顧客クレームや収益損失につながる。
- **パフォーマンス**：数百万のSKUやトランザクションを処理する場合、クエリの遅延は業務停止を招く。
- スケーラビリティ：小規模な倉庫から複数倉庫やグローバル運用まで対応可能な設計が必要。
- 柔軟性：ロット管理や有効期限追跡など、将来の機能追加に耐えうる構造が求められる。

筆者の経験では、あるEコマース企業で**データベース**設計が不十分だったため、在庫データの不一致が頻発し、月間100万円以上の損失が発生しました。このような失敗を避けるため、**スキーマ**設計に時間をかけることが不可欠です。

## WMSの主要エンティティ
`WMS`の**データベース**は、倉庫業務の主要な要素をモデル化します。以下は、典型的なエンティティとその役割です：
- **商品**（`Product`）：商品の基本情報（**SKU**、名前、説明）。
- 倉庫（`Warehouse`）：倉庫の情報（名前、住所、容量）。
- **ロケーション**（`Location`）：倉庫内の格納場所（ゾーン、棚、位置）。
- 在庫（`Stock`）：特定の**ロケーション**にある商品の数量。
- トランザクション（`Transaction`）：入庫、出庫、移動の履歴。

これらのエンティティは、1対多（`1-n`）や多対多（`n-n`）の関係で結ばれます。たとえば、1つの倉庫に複数の**ロケーション**があり、1つの**ロケーション**に複数の在庫が格納される可能性があります。

### エンティティ関係の例
以下は、エンティティ間の関係を簡略化したER図の説明です：
- 倉庫（1） ↔ **ロケーション**（多）：1つの倉庫に複数の**ロケーション**。
- **商品**（1） ↔ 在庫（多）：1つの**商品**が複数の**ロケーション**に格納。
- **ロケーション**（1） ↔ 在庫（多）：1つの**ロケーション**に複数の**商品**の在庫。
- **商品**（1） ↔ トランザクション（多）：1つの**商品**に関連する複数の入庫/出庫記録。

この関係を正しくモデル化することで、**在庫管理**の正確性を確保できます。

## データベーススキーマの設計
以下は、`PostgreSQL`を使用した**スキーマ**の例です。この設計は、自由ロケーション、ロット管理、有効期限追跡をサポートし、**スケーラビリティ**を考慮しています。

```sql
-- 商品テーブル
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    weight DECIMAL(10, 2), -- 商品の重量（例：kg）
    dimensions VARCHAR(50), -- 寸法（例：10x20x30cm）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 倉庫テーブル
CREATE TABLE warehouses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    capacity INTEGER, -- 収容可能ロケーション数
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ロケーションテーブル
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    warehouse_id INTEGER REFERENCES warehouses(id) ON DELETE CASCADE,
    code VARCHAR(50) NOT NULL, -- 例: "A-01-01"（ゾーン-棚-位置）
    is_occupied BOOLEAN DEFAULT FALSE,
    max_capacity INTEGER, -- ロケーションの最大収容数
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_location_code UNIQUE (warehouse_id, code)
);

-- 在庫テーブル
CREATE TABLE stocks (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id) ON DELETE RESTRICT,
    location_id INTEGER REFERENCES locations(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    lot_number VARCHAR(50), -- ロット番号（オプション）
    expiry_date DATE, -- 有効期限（オプション）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_product_location UNIQUE (product_id, location_id, lot_number)
);

-- トランザクションテーブル
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id) ON DELETE RESTRICT,
    location_id INTEGER REFERENCES locations(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('IN', 'OUT', 'MOVE')), -- 'IN', 'OUT', 'MOVE'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

### 設計のポイント
1. 自由ロケーションのサポート：
   - `locations`テーブルは、固定位置を強制せず、商品を任意の空き**ロケーション**に格納可能。
   - `is_occupied`フラグと`max_capacity`で**ロケーション**の使用状況と収容制限を管理。
2. ロット管理と有効期限：
   - `stocks`テーブルに`lot_number`と`expiry_date`を追加し、食品や医薬品の追跡に対応。
   - これらはオプション（NULL許可）で柔軟性を確保。
3. トランザクション履歴：
   - `transactions`テーブルは、入庫、出庫、移動の履歴を記録し、監査やトラブルシューティングに役立つ。
4. **パフォーマンス**最適化：
   - `sku`と`code`にユニーク制約を設け、検索を高速化。
   - 頻繁に検索される`product_id`や`location_id`に**インデックス**を追加。例：
     ```sql
     CREATE INDEX idx_stocks_product_id ON stocks(product_id);
     ```
5. **データ整合性**：
   - `ON DELETE CASCADE`と`ON DELETE RESTRICT`で誤削除を防止。
   - `CHECK`制約で`quantity >= 0`を強制。

### 実際のクエリ例
以下は、**WMS**でよく使われるクエリの例です：

1. 特定の**ロケーション**での在庫確認：
   ```sql
   SELECT p.sku, p.name, s.quantity, l.code
   FROM stocks s
   JOIN products p ON s.product_id = p.id
   JOIN locations l ON s.location_id = l.id
   WHERE l.code = 'A-01-01';
   ```
2. 倉庫全体の**在庫管理**集計：
   ```sql
   SELECT p.sku, p.name, SUM(s.quantity) as total_quantity
   FROM stocks s
   JOIN products p ON s.product_id = p.id
   GROUP BY p.id, p.sku, p.name;
   ```
3. 有効期限切れ商品の確認：
   ```sql
   SELECT p.sku, p.name, s.lot_number, s.expiry_date, s.quantity
   FROM stocks s
   JOIN products p ON s.product_id = p.id
   WHERE s.expiry_date <= CURRENT_DATE + INTERVAL '30 days'
   AND s.quantity > 0;
   ```

これらのクエリは、**インデックス**を適切に設定することで、大規模なデータでも高速に実行できます。

## スケーラビリティの考慮
大規模な倉庫（例：数百万SKU、数千**ロケーション**）に対応するため、以下の工夫が必要です：

1. **パーティショニング**：
   - `stocks`や`transactions`を日付や倉庫IDで分割。例：
     ```sql
     CREATE TABLE stocks (
         ...
     ) PARTITION BY RANGE (created_at);
     ```
2. キャッシュ：
   - 在庫数量の集計クエリを`Redis`でキャッシュし、**データベース**負荷を軽減。
3. 非正規化：
   - 頻繁に使用されるデータ（例：商品名）を`stocks`に冗長に持つことで、結合クエリを削減。
4. シャーディング：
   - 複数倉庫に対応する場合、**データベース**を倉庫ごとに分割。

## 実際のユースケース
以下は、**データベース**設計が倉庫業務にどのように役立つかの例です：

1. Eコマース倉庫：
   - 課題：繁忙期に在庫データが遅延し、誤出荷が頻発。
   - 解決策：**インデックス**追加と**パーティショニング**でクエリ速度を30%向上。
2. 食品倉庫：
   - 課題：有効期限切れの在庫が販売され、廃棄コストが発生。
   - 解決策：有効期限クエリを自動実行し、優先出荷。
3. 複数倉庫運用：
   - 解決策：**パーティショニング**とキャッシュでクロス倉庫の集計を高速化。

## 実践のポイント
- 柔軟性を優先：将来の拡張（例：多言語対応）を考慮して、**スキーマ**を硬直化させない。
- **データ整合性**：外部キーやチェック制約で保証。
- **パフォーマンス**テスト：大規模データでクエリ速度を検証。
- バックアップ：`pg_dump`で定期的なバックアップを設定。
- ドキュメント：**スキーマ**や**インデックス**戦略を文書化。

## 学びのポイント
**スケーラビリティ**を最初から意識する：ロット管理や有効期限が必要ない場合でも、将来的な追加を考慮して、**スキーマ**に余裕を持たせましょう。筆者の経験では、硬直的な**スキーマ**が原因で、複数倉庫対応に3か月かかりました。**データベース**設計は、`WMS`の**パフォーマンス**を左右する基盤です。

## 次回予告
次回は、入庫機能の実装に焦点を当てます。バーコードスキャンを活用した入庫プロセスの構築方法や、モバイルフレンドリーなUIの設計について、`Python`と`React`のコード例とともに解説します。