JSONからEコマースデータベーススキーマを生成する

JSONからのEコマーススキーマ

Eコマースデータベースには、商品、顧客、注文、明細など複数の関連エンティティが含まれます。これらのエンティティからのJSONデータを一貫したSQLスキーマに変換することで、コンバーターが複雑なマルチテーブルの関係をどのように処理するかを示します。

JSON例

{
  "order": {
    "id": 5001,
    "customer_id": 42,
    "status": "processing",
    "shipping_address": {
      "street": "456 Oak Ave",
      "city": "Portland",
      "state": "OR",
      "zip": "97201"
    },
    "items": [
      {
        "product_id": 301,
        "product_name": "Mechanical Keyboard",
        "quantity": 1,
        "unit_price": 149.99
      },
      {
        "product_id": 302,
        "product_name": "USB-C Cable",
        "quantity": 2,
        "unit_price": 12.99
      }
    ],
    "subtotal": 175.97,
    "tax": 15.84,
    "total": 191.81,
    "ordered_at": "2024-06-15T14:22:00Z"
  }
}

生成されるSQL

-- 商品テーブル（明細から参照される）
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  CONSTRAINT chk_products_price CHECK (price >= 0)
);

-- 顧客テーブル（注文から参照される）
CREATE TABLE customers (
  id SERIAL PRIMARY KEY
  -- 顧客JSONからの追加フィールド
);

-- 注文テーブル
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL DEFAULT 0,
  total DECIMAL(10, 2) NOT NULL,
  ordered_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
  CONSTRAINT chk_orders_total CHECK (total >= 0)
);

-- 配送先住所（注文ごとに1つ）
CREATE TABLE shipping_addresses (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL UNIQUE,
  street VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  state VARCHAR(50) NOT NULL,
  zip VARCHAR(20) NOT NULL,
  CONSTRAINT fk_shipping_addresses_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- 注文明細（注文ごとに複数）
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,

  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT chk_order_items_quantity CHECK (quantity > 0),
  CONSTRAINT chk_order_items_price CHECK (unit_price >= 0)
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

スキーマ設計のハイライト

金額はDECIMAL — すべての金額値は正確な精度のためにDECIMAL(10, 2)を使用します。金額にFLOATやDOUBLEを絶対に使用しないでください — 丸めエラーが蓄積されます。

非正規化されたproduct_name — order_itemsテーブルは購入時の商品名を保存します。商品名は後で変更される可能性がありますが、注文履歴は元の名前を保持する必要があります。

注文ごとの配送先住所 — 各注文は独自の配送先住所を持ちます（再利用可能な住所テーブルへの参照ではありません）。顧客は注文間で住所を変更する可能性があるためです。

CHECK制約 — 数量は正の値でなければならず、価格は非負でなければならず、ステータスは定義されたセットからでなければなりません。これらは無効なデータから保護します。

正規化の判断

コンバーターは正規化のタイミングについてインテリジェントな判断を行います：

• 独立したライフサイクルを持つエンティティ（商品、顧客、注文）には個別のテーブル。
• 親と密結合の1対1サブオブジェクト（配送先住所から注文へ）には埋め込み。
• 注文と商品の間の多対多の関係を形成する明細には中間テーブルスタイル。

拡張ポイント

実際のEコマーススキーマには、在庫追跡、割引コード、支払い記録、返金履歴、監査ログも含まれます。これらはそれぞれ、上記で示した同じJSONからSQLへのパターンに従います。