データベース設計

[hokoripon]

🐸細かい修正中

erDiagram
  roles ||--|{ users: ""
  users ||--o{ recipes: ""
  recipes ||--|{ ingredients: "1つ以上"
  recipes ||--|{ instructions: "1つ以上"
  recipes ||--o{ recipe_photos: ""
  recipes ||--o{ recipe_links: ""
  users ||--o{ cart_recipes: ""
  ingredients ||--o{ cart_ingredients:""
  recipes ||--o{ cart_recipes:""
  cart_recipes ||--o{ cart_ingredients:""
  users ||--o{ user_memos:""
  users ||--o{ my_recipes:""
  users ||--o{ favorite_chefs:""
  users ||--o{ user_followers:""
  users ||--o{ user_followers:""
  recipes ||--o{ favorite_recipes:""
  users ||--o{ favorite_recipes:""
  users ||--o{ user_links:""
  

%% ユーザ周り
  users {
    bigint id PK
    references role_id FK
    string name "あいうえお順で表示するために必要"
    string email
    string password
    string biography "シェフページの説明で利用する"
    timestamp created_at
    timestamp updated_at
    timestamp deleted_at "論理削除"
  }

  user_links {
    bigint id PK
    references user_id FK
    string name "twitter等が入るイメージ"
    string link
    timestamp created_at
  }

  user_followers {
    bigint id PK
    bigint follower_id FK "user_id"
    bigint followed_id FK "user_id"
    timestamp created_at
  }

  roles {
    bigint id PK
    string role "まずはシェフ、ユーザ、管理者"
    timestamp created_at
    timestamp deleted_at "論理削除"
  }

%% レシピ周り
  recipes {
    bigint id PK
    references user_id FK
    string name "レシピ名" 
    timestamp published_at
    timestamp created_at
    timestamp deleted_at "論理削除必要か？"
  }

  favorite_recipes {
    bigint id PK
    bigint user_id FK
    bigint recipe_id FK
    timestamp created_at
  }

  ingredients {
    bigint id PK
    references recipe_id FK
    string name
    string supplement "TODO:文字数考える"
    timestamp created_at
  }

  instructions {
    bigint id PK
    references recipe_id FK
    string steps
    string instruction
    timestamp created_at
  }

  recipe_photos {
    bigint id PK "${ユーザ名}${レシピ名}${id}で取得するイメージ" 
    references recipe_id FK
    timestamp created_at
  }

  recipe_links {
    bigint id PK
    references recipe_id FK
    string url
    timestamp created_at
  }

%% お買い物周り
  cart_recipes {
    bigint id PK
    references user_id FK
    references recipe_id FK "レシピ名取得"
    int display_order "0が一番上"
    timestamp created_at
  }

  cart_ingredients {
    bigint id PK
    references cart_recipes_id FK
    references ingredients_id FK "材料を取得"
    boolean completed
    timestamp created_at
  }

  user_memos {
    bigint id PK
    references user_id FK
    string title
    string content
    boolean completed
    timestamp created_at
  }

  my_recipes {
    bigint id PK
    references user_id FK
    references recipe_id FK
    timestamp created_at
  }

  favorite_chefs {
    bigint id PK
    references user_id FK
    references chef_id FK "ユーザID"
    timestamp created_at
  }

Loading

[tuboihirokidesu]

@hokoripon
めちゃ丁寧に書かれていてびっくりしました！笑

リファクタしたほうがいいかなと思ったことをツラツラと書き残しておきます。

レシピ

recipes テーブルに is_published 列がありますが、公開日時を保存する published_at 列を追加することで、公開状態だけでなく公開されたタイミングも保存できると思います。

favorite_recipes テーブルと my_recipes テーブルは、どちらもユーザーとレシピの間の関係を表しているので、user_recipesというテーブル作成し、列 type を追加し、その値を 'favorite' または 'my' に設定することで、判別するのが良いと思いました。
→favorite_recipes テーブルと my_recipes テーブル必要なくなるかも。recipes(user_recipes)のみになるかも

cart_recipes テーブルと cart_ingredients テーブルも同様にテーブルを統合し、cart_items というテーブルでいいかなと思いました。

cart_ingredients テーブルに completed 列がありますが、これは購入が完了したかどうかを示すものと思います。ただ、購入が完了したアイテムはカートから削除されるべきなので、この列は必要ないかもです。代わりに、購入履歴を保存するための新しいテーブルを作成でいいと思いました。

ユーザー

user_memos テーブルにも completedがありますが、これも必要ないと思います。

relationships テーブルは、ユーザー間のフォロー関係でしょうか？やや抽象的かと思ったので、user_followersのほうが具体的かと思いました。

↓必要ないかもです

users テーブルに role_id がありますが、ユーザーが複数の役割を持つこと、user_roles という中間テーブルを作成するのが良いかなと思いました。ユーザーと役割の間に多対多の関係が設定できるので

[hokoripon]

※すみません。少しずつ返信させてください。

確認いただきありがとうございます！

レシピ

recipes テーブルに is_published 列がありますが、公開日時を保存する published_at 列を追加することで、公開状態だけでなく公開されたタイミングも保存できると思います。

こちらは、日曜の定例でお話しし合意取れたと思うので修正します。
個人的にもこちらの方が良いと思いました！(ただしテーブルにnullを入れる設計はアンチパターンのようです。しかし公開日時のような2択の場合は問題ないと判断しました)

favorite_recipes テーブルと my_recipes テーブルは、どちらもユーザーとレシピの間の関係を表しているので、user_recipesというテーブル作成し、列 type を追加し、その値を 'favorite' または 'my' に設定することで、判別するのが良いと思いました。
→favorite_recipes テーブルと my_recipes テーブル必要なくなるかも。recipes(user_recipes)のみになるかも

my_recipesテーブルは削除で、recipesテーブルへの統合は不要だと考えています。
この後のコメントで記載していますが、my_recipesはそもそも不要でした。また、favoriteは複数のユーザから行われるため、別のテーブルとして存在していないと集計などができない認識ですので、統合は不要な認識です。

cart_recipes テーブルとcart_ingredients テーブルも同様にテーブルを統合し、cart_items というテーブルでいいかなと思いました。

すみません。あまり理解できていないので会話させてください。
※日曜定例で上がった、配列で持たせるはアンチパターンのようでした。第一正規化に反するためやめた方が良さそうなので、このままでよいと考えています。

cart_ingredients テーブルに completed 列がありますが、これは購入が完了したかどうかを示すものと思います。ただ、購入が完了したアイテムはカートから削除されるべきなので、この列は必要ないかもです。代わりに、購入履歴を保存するための新しいテーブルを作成でいいと思いました。

これはチェック状態を管理するものの想定でした。チェックした瞬間削除される想定はありません。削除ボタン押下した場合は削除APIをたたいて画面から削除する想定で設計しています。こちらは日曜の定例で認識あわせさせてください。🙇‍♂️

ユーザー

user_memos テーブルにも completedがありますが、これも必要ないと思います。

これも先述した理由で必要な認識ですが、詳細は会話させてください。

relationships テーブルは、ユーザー間のフォロー関係でしょうか？やや抽象的かと思ったので、user_followersのほうが具体的かと思いました。

ありがとうございます。おっしゃる通りだと思いました。user_followersテーブルに修正します。

users テーブルに role_id がありますが、ユーザーが複数の役割を持つこと、user_roles という中間テーブルを作成するのが良いかなと思いました。ユーザーと役割の間に多対多の関係が設定できるので

難しいですね。拡張性を持たせるならuser_rolesがあってもよいかなと思いますが、必要かと考えると微妙なところですね。会話させてください。

[hokoripon]

設計誤り
・my_recipesは不要
そもそもユーザーが作ったレシピもシェフのレシピもレシピに入れる想定だった。レシピをユーザーIDでクエリした結果がmyレシピまたはシェフのレシピになる。

[hokoripon]

日曜定例より、くまびこさんのコメント
ユーザーとシェフのテーブルは同じか？

[hokoripon]

同じにしているが、シェフでしか使わないカラムがあるので、分けたほうが良いかも？(バイオグラフィーcolumn)
また、ユーザのレシピとシェフのレシピを同じところで管理すると誤って公開する可能性がないか？

[hokoripon]

6/14(水) 会話した内容

■ロールテーブルを廃止したい。
→ユーザ、シェフ、管理者毎のテーブルを作成する。
それぞれのテーブルの目的が違うため、そもそも同じテーブルで管理するのはよくなさそうとなった。また拡張性がない。くまびこさんは実務で上記のように分けて管理されているようでした。

■レシピは1つのテーブルとして、新しくシェフIDを絡むとして追加したい。
検索結果に両方出るみたいなので利用用途や背景がシェフでもユーザでも変わらなそう。また１つのテーブルで管理すると楽そうなので同じテーブルで管理したい。
ただし、ユーザIDとシェフIDの両方のIDがカラムに入ってくるので一方がnullになってしまう。

2023/06/18(日)までに新設計案を定義して、BEチーム会で再度会話する。
日曜のアジェンダ
・新設計
・APIの内容確認
・設計とAPIの突合せ
・ディスカッション上の質問の確認

[hokoripon]

マイレシピは編集、削除、公開、非公開できるっぽい。

[tuboihirokidesu]

@hokoripon

追加でレビューさせてもらいます！

レシピ

• recipe_photos テーブルの idフィールド
  • ユーザー名やレシピ名をIDに含めると、これらの値が変更されたときに問題が生じる可能性があると思います。supabaseのストレージにアクセスためのファイルパスを保存するフィールドpathを設ける設計にする方が良いと思いました

以下のような感じでしょうか

const uniqueFilename = `recipe_photos/${recipeId}_${Date.now()}.jpg`;

// ストレージへのアップロード
await supabase.storage.from('recipes').upload(uniqueFilename, file);

// データベースにpathを保存
await prisma.recipePhoto.create({
  data: {
    recipeId: recipeId,
    path: uniqueFilename,
    // etc...
  },
});

• my_recipes テーブルは必要ないかも
  • recipes テーブル自体が既に user_id を持っているため、recipes テーブルから比較演算子で自身のrecipeを取得で良いと思いました

以下のような感じでしょうか

const { data: recipes, error } = await supabaseServerClient
    .from("recipe")
    .select("*")
    .eq("userId", session.user.id)
    .order("createdAt", { ascending: false });

• cart テーブルを追加すべきかも
  • 現状は cart_ingredients が ingredients と cart_recipes の両方に依存していると思います。この依存関係は、cart_recipesとcart_ingredients間の関連性が理解しにくいのかなと思います。ユーザーのカート全体を通じて材料を追跡する方法を提案します

erDiagram
    users ||--o{ cart : ""
    cart ||--o{ cart_recipes : ""
    recipes ||--|{ cart_recipes : ""
    recipes ||--|{ ingredients : "1つ以上"
    cart_recipes ||--o{ cart_ingredients : ""
    ingredients ||--o{ cart_ingredients : ""

    cart {
        bigint id PK
        references user_id FK
        timestamp created_at
    }

    cart_recipes {
        bigint id PK
        references cart_id FK
        references recipe_id FK 
        int display_order "0が一番上"
        timestamp created_at
    }

    cart_ingredients {
        bigint id PK
        references cart_recipe_id FK
        references ingredient_id FK 
        boolean completed
        timestamp created_at
    }

Loading

[hokoripon]

@tuboihirokidesu
ありがとうございます。少しずつ確認して返信させてください。

recipe_photos テーブルの idフィールド

ありがとうございます。おっしゃる通りだと思いました。ご提案いただいた内容で修正してみます🙇‍♂️

my_recipes テーブルは必要ないかも

はい。私も同じ認識で不要だと思うので削除します。

cart テーブルを追加すべきかも

こちらの修正の意図が理解できなかったので、お手数ですが定例にて会話させてください🙇‍♂️

[tuboihirokidesu]

@hokoripon

こちらの修正の意図が理解できなかったので、お手数ですが定例にて会話させてください🙇‍♂️

言葉足らずで申し訳ありません🙇‍♂️
定例にて詳細話したいと思いますが、文面でも書き残しておきます。

提案した設計だとcart_recipesテーブルの各レコードがcart_ingredientsテーブルのレコードに対応するcart_recipe_idを持つことで、ある特定のカートに属するレシピ（cart_recipes）を取得する際に、そのレシピに関連する材料（cart_ingredients）も同時に取得できると思います。

実際に一覧取得の例を考えると

const { data, error } = await supabase
  .from('cart')
  .select(`
    id,
    created_at,
    cart_recipes (
      id,
      display_order,
      recipe_id,
      created_at,
      recipe (
        name
      ),
      cart_ingredients (
        id,
        ingredient_id,
        completed,
        created_at,
        ingredient (
          name
        )
      )
    )
  `)
  .eq('user_id', userId)

これに対して、現行の設計では、cart_recipesとcart_ingredientsの間に直接的な接続が存在せず、両者はrecipesとingredientsを介して間接的にリンクされています。これにより、まずcart_recipesのデータを取得し、その後で各レシピに対応するcart_ingredientsのデータを取得するという、2段階のプロセスが必要になると思います。
すみません、こちらは誤りで、現行の設計でもcart_ingredientsにcart_recipes_id持たせているのでテーブル間の関連性は保たれていると思います🙇‍♂️以下のように一回で取得できると思います。

const { data, error } = await supabase
  .from('cart_recipes')
  .select(`
    id,
    display_order,
    recipe_id,
    created_at,
    recipe (
      name
    ),
    cart_ingredients (
      id,
      ingredient_id,
      completed,
      created_at,
      ingredient (
        name
      )
    )
  `)
  .eq('user_id', userId)

んーやっぱりrecipeテーブル必要ないかもです🙇‍♂️

[hokoripon]

@tuboihirokidesu @kumabiko
たくさんコメントいただきありがとうございます。tuboiさんからもコメントは回答できそうなまのはは全て回答しました。
いただいたコメントをもとにER図見直しました。
残課題や疑問点について、本日会話させていただき詳細決定させてください。

erDiagram
  users ||--o{ recipes: ""
  chefs ||--o{ recipes: ""
  recipes ||--|{ ingredients: "1つ以上"
  recipes ||--|{ instructions: "1つ以上"
  recipes ||--o{ recipe_photos: ""
  recipes ||--o{ recipe_links: ""
  users ||--o{ cart_recipes: ""
  ingredients ||--o{ cart_ingredients:""
  recipes ||--o{ cart_recipes:""
  cart_recipes ||--o{ cart_ingredients:""
  users ||--o{ user_memos:""
  users ||--o{ favorite_chefs:""
  users ||--o{ followers:""
  users ||--o{ followers:""
  recipes ||--o{ favorite_recipes:""
  users ||--o{ favorite_recipes:""
  chefs ||--o{ chef_links:""

%% ユーザ周り
  admin {
    bigint admin_id PK
    string name
    string email
    string password
    timestamp created_at
    timestamp updated_at
    timestamp deleted_at "論理削除"
  }

  users {
    bigint user_id PK
    string name "not null"
    string email
    string password
    string avatar "画像保存先までのパス"
    timestamp created_at
    timestamp updated_at
    timestamp deleted_at "論理削除"
  }

  chefs {
    bigint chef_id PK
    bigint created_by "adimnのIDが入る(必要か考える)"
    string name "あいうえお順で表示するために必要"
    string biography "シェフページの説明で利用する"
    timestamp created_at
    timestamp updated_at
    timestamp deleted_at "論理削除"
  }

  chef_links {
    bigint chef_links_id PK
    references chef_id FK
    string name "twitter等が入るイメージ"
    string link "https://~"
    timestamp created_at
  }

  followers {
    bigint follower_id FK "user_id"
    bigint followed_id FK "chef_id"
    timestamp created_at
  }

%% レシピ周り
  %% 作成者カラムを分けるのはバットプラクティスっぽい。
  recipes {
    bigint recipes_id PK
    bigint created_by FK "ユーザIDまたはシェフIDが入る"
    string title "レシピ名" 
    string description "レシピの説明"
    int serving_size "n人前"
    string image "保存先までのパス"
    timestamp published_at "nullの場合は非公開"
    timestamp created_at
    timestamp deleted_at "論理削除必要か？"
  }

  ingredients {
    bigint ingredients_id PK
    references recipe_id FK
    string text "材料名や材料名に関する補足情報"
    timestamp created_at
  }

  instructions {
    bigint instructions_id PK
    references recipe_id FK
    int steps
    string instruction
    timestamp created_at
  }

  recipe_photos {
    bigint recipe_photos_id PK
    references recipe_id FK
    string path "保存先までのパス"
    timestamp created_at
  }

  recipe_links {
    bigint recipe_links_id PK
    references recipe_id FK
    string url
    timestamp created_at
  }

  %% ユーザがお気に入りしたレシピ
  favorite_recipes {
    bigint user_id FK
    bigint recipe_id FK
    timestamp created_at
  }

%% お買い物周り
  cart_recipes {
    bigint cart_recipes_id PK
    references user_id FK
    references recipe_id FK "レシピ名取得"
    int display_order "0が一番上"
    timestamp created_at
  }

  cart_ingredients {
    references cart_recipes_id FK
    references ingredients_id FK "材料を取得"
    boolean completed
    timestamp created_at
  }

  user_memos {
    bigint user_memos_id PK
    references user_id FK
    string text
    boolean completed
    timestamp created_at
  }

  favorite_chefs {
    references user_id FK
    references chef_id FK "ユーザID"
    timestamp created_at
  }

Loading