注文テーブル設計ーー留痕（トレース）


### 注文表に関するテーブル設計
支払いシステムは通常、注文時に商品情報の記録を保存し、リアルタイムで商品情報を読み取るのではなく、保存された情報を利用します。
# **１．注文の情報の「留痕（トレース）」**

| 設計方法 | 設計1: `order_items` に当時の商品情報を保存する | 設計2: `products` テーブルを直接参照する |
| --- | --- | --- |
| **概要** | 注文が確定した時点の商品の情報（商品名、価格、型番など）を `order_items` に保存し、その後の商品データの変化に影響されないようにする。 | 注文時に `products` テーブルを参照して、商品情報を取得する。`order_items` には商品IDなどの最低限の情報のみを保存。 |
| **メリット** | - 過去の注文情報が確実に保持される<br>- 商品の情報が削除された場合でも注文データに影響がない<br>- パフォーマンスが向上し、複雑な JOIN を避けられる<br>- 会計、法務、監査の際に正確な情報を保持できる。 | - データの重複を減らす<br>- 商品情報を集中管理し、管理が簡単になる<br>- ストレージの節約が可能で、管理がシンプル |
| **デメリット** | - データが冗長になるため、ストレージの消費が増える<br>- 商品情報を更新する際に冗長なデータの同期が必要になる場合がある | - 商品情報の変更（例: 価格、名前の変更）が過去の注文に反映され、注文時の情報と不一致になる<br>- 商品が削除されると、注文履歴に不完全なデータが残る可能性がある<br>- 注文履歴の整合性が失われる可能性がある。 |
| **主な使用ケース** | - 電子商取引システムなど、注文時の正確な情報を保持する必要がある場合<br>- 会計、法務、監査など、正確な記録が必要な場合 | - 商品情報が頻繁に変更されず、在庫管理やシンプルなシステムに適している場合<br>- ストレージを節約したい場合 |


### 結論

- **設計1** は、電子商取引や正確な履歴管理が必要なシステムに最適です。
- **設計2** は、シンプルな在庫管理システムや商品データの変更が少ないシステムに適しています。

### `order_items` **設計**実装方法

実際のシステムでは、ユーザーが注文を確定した際に、システムは `products` テーブルから商品に関する最新のデータを取得し、それを `order_items` テーブルに挿入します。大まかな流れは以下の通りです。

1. ユーザーが商品を選択し、注文を確定する。
2. システムが `products` テーブルから最新の商品データ（`product_name`, `product_price`, `product_size`）を取得する。
3. システムは、これらのデータと注文番号を一緒に `order_items` テーブルに挿入する。

### サンプルSQL

例えば、ユーザーが注文時に `product_id = '123'` の商品を選択した場合、システムは以下のような操作を実行するかもしれません。

```sql
sql
コードをコピーする
-- products テーブルから最新の商品データを取得
INSERT INTO order_items (order_id, product_id, product_name, product_price, product_size, number, subtotal)
SELECT 'order_001', p.product_id, p.product_name, p.product_price, p.product_size, 2, p.product_price * 2
FROM products p
WHERE p.product_id = '123';

```

このようにして、`order_items` テーブルのレコードは注文時点の商品のスナップショットを保持します。これにより、`products` テーブルの情報が後で変更されたとしても、すでに確定した注文には影響を及ぼしません。

# 関連DDL

```sql
CREATE TABLE order_items (
    order_id VARCHAR(50) NOT NULL, -- 注文番号 (外部キー)
    product_id VARCHAR(50) NOT NULL, -- 商品番号 (外部キー)
    product_name VARCHAR(255) NOT NULL, -- 商品名（商品が削除される可能性があるため、ここで記録。直接商品表を参照しない）
    product_price NUMERIC(10, 2) NOT NULL, -- 商品価格（変更される可能性があるため、ここで記録。直接商品表を参照しない）
    product_size VARCHAR(255), -- 商品サイズ情報（変更される可能性があるため、ここで記録。直接商品表を参照しない）
    product_store_barcode VARCHAR(100), -- 商品の倉庫バーコード
    discount_rate NUMERIC(5, 2), -- 割引率（何割引か）
    discount_amount NUMERIC(10, 2), -- 割引金額
    account  INTEGER NOT NULL, -- 購入数量
    subtotal NUMERIC(10, 2) NOT NULL, -- 小計金額
    remark TEXT -- 顧客の商品の備考
);

-- フィールドのコメントを追加
COMMENT ON COLUMN order_items.order_id IS '注文番号 (外部キー)';
COMMENT ON COLUMN order_items.product_id IS '商品番号 (外部キー)';
COMMENT ON COLUMN order_items.product_name IS '商品名（商品が削除される可能性があるため、ここで記録。直接商品表を参照しない）';
COMMENT ON COLUMN order_items.product_price IS '商品価格（変更される可能性があるため、ここで記録。直接商品表を参照しない）';
COMMENT ON COLUMN order_items.product_size IS '商品サイズ情報商品サイズ情報（変更される可能性があるため、ここで記録。直接商品表を参照しない）';
COMMENT ON COLUMN order_items.product_store_barcode IS '商品の倉庫バーコード';
COMMENT ON COLUMN order_items.discount_rate IS '割引率（何割引か）';
COMMENT ON COLUMN order_items.discount_amount IS '割引金額';
COMMENT ON COLUMN order_items.account IS '購入数量';
COMMENT ON COLUMN order_items.subtotal IS '小計金額';
COMMENT ON COLUMN order_items.remark IS '顧客の商品の備考';

CREATE TABLE products (
    product_id VARCHAR(50) PRIMARY KEY, -- 商品番号 (主キー)
    product_name VARCHAR(255) NOT NULL, -- 商品名
    description TEXT, -- 商品説明
    product_price NUMERIC(10, 2) NOT NULL, -- 商品価格
    product_marque VARCHAR(100), -- 商品型番
    product_store_barcode VARCHAR(100), -- 商品の倉庫バーコード
    product_size VARCHAR(255), -- 商品サイズ情報
    product_mode_params JSONB, -- 商品モデルパラメータ（JSON形式）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 作成日時
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新日時
);

-- フィールドのコメントを追加
COMMENT ON COLUMN products.product_id IS '商品番号 (主キー)';
COMMENT ON COLUMN products.product_name IS '商品名';
COMMENT ON COLUMN products.description IS '商品説明';
COMMENT ON COLUMN products.product_price IS '商品価格';
COMMENT ON COLUMN products.product_marque IS '商品型番';
COMMENT ON COLUMN products.product_store_barcode IS '商品の倉庫バーコード';
COMMENT ON COLUMN products.product_size IS '商品サイズ情報';
COMMENT ON COLUMN products.product_mode_params IS '商品モデルパラメータ（JSON形式）';
COMMENT ON COLUMN products.created_at IS '作成日時';
COMMENT ON COLUMN products.updated_at IS '更新日時';

CREATE TABLE orders (
    order_id VARCHAR(50) PRIMARY KEY, -- 注文の一意識別子、主キーとしてULIDを使用
    user_id VARCHAR(50) NOT NULL, -- 外部キー、ユーザーテーブルのユーザーIDに関連付け
    order_date TIMESTAMP NOT NULL, -- 注文作成日
    total_price NUMERIC(10, 2) NOT NULL, -- 注文の合計金額
    status VARCHAR(20) NOT NULL, -- 注文ステータス（例："作成済み"、"支払い済み"、"発送済み"、"完了"、"キャンセル"など）
    tracking_number VARCHAR(50), -- 追跡番号
    shipment_date TIMESTAMP, -- 発送日
    payment_date TIMESTAMP, -- 支払日
    payment  NUMERIC(10, 2), -- 支払金額
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 作成日時
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新日時
);

-- フィールドのコメントを追加
COMMENT ON COLUMN orders.order_id IS '注文の一意識別子、主キーとしてULIDを使用';
COMMENT ON COLUMN orders.user_id IS '外部キー、ユーザーテーブルのユーザーIDに関連付け';
COMMENT ON COLUMN orders.order_date IS '注文作成日';
COMMENT ON COLUMN orders.total_price IS '注文の合計金額';
COMMENT ON COLUMN orders.status IS '注文ステータス（例："作成済み"、"支払い済み"、"発送済み"、"完了"、"キャンセル"など）';
COMMENT ON COLUMN orders.tracking_number IS '追跡番号';
COMMENT ON COLUMN orders.shipment_date IS '発送日';
COMMENT ON COLUMN orders.payment_date IS '支払日';
COMMENT ON COLUMN orders.payment  IS '支払金額';
COMMENT ON COLUMN orders.created_at IS '作成日時';
COMMENT ON COLUMN orders.updated_at  IS '更新日時';
```

### テストデータの挿入

### 1. `products` テーブルにデータを挿入

```sql
sql
コードをコピーする
-- 商品データを挿入
INSERT INTO products (product_id, product_name, description, product_price, product_marque, product_store_barcode, product_size, product_mode_params)
VALUES
('PROD001', 'コーヒー', 'アラビカ豆100%の香り高いコーヒー', 500.00, 'COFFEE001', '1234567890', 'Mサイズ', '{"unit": "個", "color": "茶色", "size": "M"}'),
('PROD002', '紅茶', 'スリランカ産の上質な紅茶', 300.00, 'TEA001', '0987654321', 'Lサイズ', '{"unit": "個", "color": "紅色", "size": "L"}'),
('PROD003', '緑茶', '日本産の有機緑茶', 450.00, 'GREEN_TEA001', '1122334455', 'Sサイズ', '{"unit": "個", "color": "緑色", "size": "S"}');

```

### 2. `orders` テーブルにデータを挿入

```sql
sql
コードをコピーする
-- 注文データを挿入
INSERT INTO orders (order_id, user_id, order_date, total_price, status, tracking_number, shipment_date, payment_date, payment)
VALUES
('ORDER001', '01J8KSCNA2D72KZ50TQE8WKZSS', '2024-10-24 10:00:00', 1000.00, '支払い済み', 'TRACK12345', '2024-10-25 09:00:00', '2024-10-24 10:15:00', 1000.00),
('ORDER002', '01J8KSCNA2D72KZ50TQE8WKZSS', '2024-10-23 14:00:00', 600.00, '発送済み', 'TRACK67890', '2024-10-24 08:00:00', '2024-10-23 14:30:00', 600.00);

```

### 3. `order_items` テーブルにデータを挿入

```sql
sql
コードをコピーする
-- 注文商品データを挿入
INSERT INTO order_items (order_id, product_id, product_name, product_price, product_size, product_store_barcode, discount_rate, discount_amount, account, subtotal, remark)
VALUES
('ORDER001', 'PROD001', 'コーヒー', 500.00, 'Mサイズ', '1234567890', 0.00, 0.00, 2, 1000.00, '朝食用のコーヒー'),
('ORDER002', 'PROD002', '紅茶', 300.00, 'Lサイズ', '0987654321', 0.10, 30.00, 2, 570.00, 'リラックスタイム用');

```