LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@LucasHIKARI

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

Last updated at Posted at 2024-10-24

注文表に関するテーブル設計

支払いシステムは通常、注文時に商品情報の記録を保存し、リアルタイムで商品情報を読み取るのではなく、保存された情報を利用します。

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

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

結論

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

order_items 設計実装方法

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

  1. ユーザーが商品を選択し、注文を確定する。
  2. システムが products テーブルから最新の商品データ（product_name, product_price, product_size）を取得する。
  3. システムは、これらのデータと注文番号を一緒に 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, 'リラックスタイム用');
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?