ECサイトのSQLスキーマをPrismaに変換する

ECサイトスキーマの変換

ECサイトのデータベースには、商品、カテゴリ、顧客、注文、注文明細がさまざまなリレーションシップ型で含まれます。この例では、完全なマルチテーブルSQLスキーマをPrismaに変換することを示します。

SQLの例

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(100) NOT NULL UNIQUE,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  compare_at_price DECIMAL(10, 2),
  sku VARCHAR(50) UNIQUE,
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  metadata JSONB NOT NULL DEFAULT '{}',
  category_id INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT chk_price CHECK (price >= 0),
  CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL UNIQUE,
  customer_email VARCHAR(320) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL DEFAULT 0,
  shipping DECIMAL(10, 2) NOT NULL DEFAULT 0,
  total DECIMAL(10, 2) NOT NULL,
  notes TEXT,
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT chk_quantity CHECK (quantity > 0)
);

CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_products_is_active ON products (is_active);
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);

生成されるPrismaスキーマ

enum OrderStatus {
  pending
  processing
  shipped
  delivered
  cancelled
}

model Category {
  id       Int        @id @default(autoincrement())
  name     String     @unique @db.VarChar(100)
  slug     String     @unique @db.VarChar(100)
  parentId Int?       @map("parent_id")
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id], onDelete: SetNull)
  children Category[] @relation("CategoryTree")
  products Product[]

  @@map("categories")
}

model Product {
  id             Int       @id @default(autoincrement())
  name           String
  slug           String    @unique
  description    String?   @db.Text
  price          Decimal   @db.Decimal(10, 2)
  compareAtPrice Decimal?  @map("compare_at_price") @db.Decimal(10, 2)
  sku            String?   @unique @db.VarChar(50)
  stockQuantity  Int       @default(0) @map("stock_quantity")
  isActive       Boolean   @default(true) @map("is_active")
  metadata       Json      @default("{}")
  categoryId     Int?      @map("category_id")
  category       Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  createdAt      DateTime  @default(now()) @map("created_at") @db.Timestamptz()
  updatedAt      DateTime  @updatedAt @map("updated_at") @db.Timestamptz()
  orderItems     OrderItem[]

  @@index([categoryId], map: "idx_products_category_id")
  @@index([isActive], map: "idx_products_is_active")
  @@map("products")
}

model Order {
  id            Int         @id @default(autoincrement())
  orderNumber   String      @unique @map("order_number") @db.VarChar(20)
  customerEmail String      @map("customer_email") @db.VarChar(320)
  status        OrderStatus @default(pending)
  subtotal      Decimal     @db.Decimal(10, 2)
  tax           Decimal     @default(0) @db.Decimal(10, 2)
  shipping      Decimal     @default(0) @db.Decimal(10, 2)
  total         Decimal     @db.Decimal(10, 2)
  notes         String?     @db.Text
  orderedAt     DateTime    @default(now()) @map("ordered_at") @db.Timestamptz()
  items         OrderItem[]

  @@index([status], map: "idx_orders_status")
  @@index([orderedAt], map: "idx_orders_ordered_at")
  @@map("orders")
}

model OrderItem {
  id          Int     @id @default(autoincrement())
  orderId     Int     @map("order_id")
  productId   Int     @map("product_id")
  productName String  @map("product_name")
  quantity    Int     @default(1)
  unitPrice   Decimal @map("unit_price") @db.Decimal(10, 2)
  order       Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product     Product @relation(fields: [productId], references: [id])

  @@index([orderId], map: "idx_order_items_order_id")
  @@index([productId], map: "idx_order_items_product_id")
  @@map("order_items")
}

スキーマ設計のポイント

自己参照カテゴリ — Category modelは階層的なカテゴリツリー（例：電子機器 > ノートパソコン > ゲーミングノートパソコン）のためにparentIdで自分自身を参照しています。

CHECKからEnumへ — statusのCHECK制約はPrismaのenum OrderStatusに昇格し、アプリケーション全体でTypeScriptの型安全性を提供します。

非正規化されたproduct_name — OrderItemは購入時の商品名を保存しています。商品名は後で変更される可能性がありますが、注文履歴は正確に維持される必要があるためです。

金額にはDecimal — すべての金額フィールドは浮動小数点の丸め誤差を避けるため、明示的な精度を持つDecimalを使用しています。

JSONメタデータ — Product.metadataフィールドはPrismaのJson型を使用し、色、寸法、仕様などの柔軟なスキーマレス属性を格納します。

クエリの例

// 注文と明細・商品詳細を取得
const order = await prisma.order.findUnique({
  where: { orderNumber: "ORD-2024-5001" },
  include: {
    items: {
      include: { product: true }
    }
  }
});

// カテゴリ内のアクティブな商品一覧
const products = await prisma.product.findMany({
  where: { categoryId: 5, isActive: true },
  orderBy: { createdAt: "desc" }
});