MarketplaceスキーマをつなぐForeign Keys

データベースがあって、テーブルもある。しかもデータも入ってる。じゃあ、そろそろそのデータの整合性をちゃんと守る時だね。

各スキーマ内でテーブルを作った時、同じスキーマ内のテーブルには自動でforeign keysが追加されてた。でも今度は、違うスキーマのテーブル同士をつなぐ必要があるよ。foreign keysを追加しよう。

例えば、orderスキーマのorderテーブルは、userスキーマのaccountテーブルのユーザーを参照してる。このテーブルにforeign keyを追加しなきゃ。こんな感じのSQLになるよ：

ALTER TABLE "order".order
  ADD CONSTRAINT fk_order_user
  FOREIGN KEY (user_id)
  REFERENCES "user".account(id)
  ON DELETE RESTRICT;

foreign keysのリスト

全部で40個のforeign keysを追加する必要があるよ :)

productスキーマ：

[product].review.user_id → [user].account.id

商品のレビューは、それを書いたユーザーとつながってる必要がある。

[product].question.user_id → [user].account.id

商品への質問は登録ユーザーがする。

[product].answer.user_id → [user].account.id

回答もユーザーのものだよ。

orderスキーマ：

[order].order.user_id → [user].account.id

注文したユーザーは必ず登録ユーザー。

[order].order_item.product_id → [product].product.id

注文アイテムは必ず特定の商品を参照する。

[order].order_item.variant_id → [product].variant.id

商品バリアントが選ばれてたら、それが存在しなきゃダメ。でもバリアントが消されたら（削除されたら）、このフィールドはNULLになる。

[order].cart.user_id → [user].account.id

カートは特定のユーザーのもの。

[order].cart_item.product_id → [product].product.id

カート内の商品は有効な商品じゃなきゃダメ。

[order].cart_item.variant_id → [product].variant.id

指定されてたら、選ばれたバリアントが存在しなきゃ。消されたらNULLになる。

logisticsスキーマ：

[logistics].inventory.variant_id → [product].variant.id

バリアントは削除されることもある（例えば販売終了とか）。

[logistics].inventory_movement.product_id → [product].product.id

倉庫の動きは全部、実際の商品に紐づいてる。

[logistics].inventory_movement.variant_id → [product].variant.id

動きの種類は特定のバリアントに関係することもある。

[logistics].stock_level.product_id → [product].product.id

在庫レベルは存在する商品で設定される。

[logistics].transfer.product_id → [product].product.id

移動は本当に存在する商品だけ。

[logistics].transfer.variant_id → [product].variant.id

バリアントは削除されることもある。

[logistics].package.order_id → [order].order.id

各パッケージは特定の注文とつながってる。

paymentスキーマ：

[payment].payment_transaction.order_id → [order].order.id

支払いトランザクションは必ず注文に紐づいてる。

[payment].invoice.order_id → [order].order.id 請求書は注文ごとに発行される。

[payment].billing_address.user_id → [user].account.id

請求先住所はユーザーのもの。

[payment].wallet.user_id → [user].account.id

ウォレットはユーザーごとにユニーク。

marketingスキーマ：

[marketing].discount.product_id → [product].product.id

割引は特定の商品に紐づけられることがある。

[marketing].discount.category_id → [product].category.id

割引は商品カテゴリに紐づけられることもある。

[marketing].discount.brand_id → [product].brand.id

ブランド向けの割引。

[marketing].promo_usage.user_id → [user].account.id

プロモコードは登録ユーザーだけが使える。

[marketing].featured_product.product_id → [product].product.id

ショーケースのおすすめ商品は存在してなきゃダメ。

[marketing].referral_use.referrer_id → [user].account.id

招待した人は必須のユーザー。

[marketing].referral_use.referee_id → [user].account.id

招待された人も必須のユーザー。

supportスキーマ：

[support].support_ticket.user_id → [user].account.id

各チケットはユーザーが作成する。

[support].support_ticket.category_id → [support].ticket_category.id

チケットカテゴリは削除されることもあるけど、チケット自体は残る。

[support].ticket_message.sender_id → [user].account.id

メッセージはユーザー（またはuser.accountにいるエージェント）が送る。

[support].support_agent.user_id → [user].account.id

サポートエージェントも必ずユーザー。

[support].ticket_status_log.changed_by → [user].account.id

誰がステータスを変えたか（ユーザーかスタッフか）を記録する。

contentスキーマ：

[content].article.author_id → [user].account.id

記事は登録ユーザーが書く。

[content].media.uploaded_by → [user].account.id

誰がメディアファイルをアップしたか（削除されることもある）。

[content].content_revision.author_id → [user].account.id

誰が変更したか（削除されることもある）。

メモ

できるだけ、ON DELETE SET NULLを使って、エンティティが削除されても関連情報が完全に消えないようにしよう。

「target_type/target_id」みたいなタイプでエンティティをつなぐ場合（例：user.comment）、SQLレベルではforeign keyは無理だから、アプリ側で管理しよう。

このリストは、SQLレベルでの基本的なデータ整合性のための主要なスキーマ間の関連をカバーしてるよ。

解決策を含むファイル。