１．注文の情報の「留痕（トレース）」

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

結論

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

`order_items` 設計実装方法

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

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

サンプルSQL

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

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

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
コードをコピーする
-- 商品データを挿入
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
コードをコピーする
-- 注文データを挿入
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
コードをコピーする
-- 注文商品データを挿入
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, 'リラックスタイム用');