mysql - 使用 Prisma 的 MySQL 锻炼应用程序架构

Question

我正在使用 MySQL 和 Prisma 创建一个锻炼应用程序，我正在努力为数据设计一个架构。
该应用程序将拥有用户和锻炼计划。例如，锻炼计划“Get Jacked”可能包含 3 个块(每个块为 1 个月)。每个街区将包含每周 5 次锻炼，每次锻炼将包含多次练习和热身。需要注意的一些重要事项:每个用户都应该能够记录他们在锻炼中每次锻炼的个人组数和次数。他们还应该能够完成一个程序(“Get Jacked”)，次数不限，每次都应该能够为他们的代表和组记录新的值。
到目前为止，这是我的模型:

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  role    Role     @default(USER)
  workouts   Workout[]
}

model Program {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  name       String
  published  Boolean    @default(false)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
}

model Block {
  id         Int        @id @default(autoincrement())
  name       String
  program    Program    @relation(fields: [programId], references: [id])
  programId  Int
}

model Workout {
  id         Int        @id @default(autoincrement())
  name       String
  week       String
  day        String
  block      Block    @relation(fields: [blockId], references: [id])
  blockId    Int
}

model WorkoutSet {
  id         Int        @id @default(autoincrement())
  name       String
  sets       Int
  reps       Int
  workout    Workout   @relation(fields: [workoutId], references: [id])
  workoutId  Int
  exercise   Exercise   @relation(fields: [exerciseId], references: [id])
  exerciseId Int
}

model Exercise {
  id         Int        @id @default(autoincrement())
  name       String
}

model LogWorkout {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  workout    Workout    @relation(fields: [workoutId], references: [id])
  workoutId  Int
}

model LogWorkoutSet {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  sets       Int
  reps       Int
  weight     Int
  logWorkout LogWorkout @relation(fields: [logWorkoutId], references: [id])
  logWorkoutId   Int
  workoutSet     User       @relation(fields: [workoutSetId], references: [id])
  workoutSetId   Int
}

我对关系数据库比较陌生，我似乎无法理解的是，代表的记录如何与用户联系起来，以及用户如何多次完成锻炼计划。
任何帮助将非常感激。
谢谢，
亚当

Answer 1

你越来越接近了，只有几个建议:

使用带有 Prisma 扩展的 VSCode，它将帮助您轻松格式化关系并更快地发现错误

声明双方关系

尝试将您的数据库模型抽象为易于理解的概念，这样以后维护会变得更容易

这可能是你想要的:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id             Int              @id @default(autoincrement())
  email          String           @unique
  name           String?
  role           Role             @default(USER)
  programs       Program[]
  programRecords ProgramRecord[]
  exercises      ExerciseRecord[]
}

// Programs available
model Program {
  id        Int             @id @default(autoincrement())
  name      String
  published Boolean         @default(false)
  authorId  Int
  author    User            @relation(fields: [authorId], references: [id])
  blocks    Block[]
  records   ProgramRecord[]
  createdAt DateTime        @default(now())
}

// Blocks within a program
model Block {
  id        Int       @id @default(autoincrement())
  name      String
  programId Int
  program   Program   @relation(fields: [programId], references: [id])
  workouts  Workout[]
}

// Workouts within a block
model Workout {
  id        Int                 @id @default(autoincrement())
  name      String
  week      String
  day       String
  blockId   Int
  block     Block               @relation(fields: [blockId], references: [id])
  exercises ExerciseOnWorkout[]
}

// Exercises to be done in workout (Relation table)
model ExerciseOnWorkout {
  id         Int              @id @default(autoincrement())
  workoutId  Int
  workout    Workout          @relation(fields: [workoutId], references: [id])
  exerciseId Int
  exercise   Exercise         @relation(fields: [exerciseId], references: [id])
  name       String
  sets       Int
  reps       Int
  weight     Int
  records    ExerciseRecord[]
  createdAt  DateTime         @default(now())

  // Restrict to do not repeat combinations with same name
  @@unique([workoutId, exerciseId, name])
}

// Exercise options
model Exercise {
  id       Int                 @id @default(autoincrement())
  name     String
  workouts ExerciseOnWorkout[]
}

// New "enrollment" record of a user in a program
model ProgramRecord {
  id              Int              @id @default(autoincrement())
  programId       Int
  program         Program          @relation(fields: [programId], references: [id])
  userId          Int
  user            User             @relation(fields: [userId], references: [id])
  exerciseRecords ExerciseRecord[]
  // TODO: You could track the status to prevent users starting a new one
  isComplete      Boolean          @default(false)
  createdAt       DateTime         @default(now())
}

// User personal record for a workout exercise
model ExerciseRecord {
  id              Int               @id @default(autoincrement())
  userId          Int
  user            User              @relation(fields: [userId], references: [id])
  exerciseId      Int
  exercise        ExerciseOnWorkout @relation(fields: [exerciseId], references: [id])
  programRecordId Int
  programRecord   ProgramRecord     @relation(fields: [programRecordId], references: [id])
  name            String
  sets            Int
  reps            Int
  weight          Int
  createdAt       DateTime          @default(now())

  @@unique([userId, exerciseId, programRecordId, name])
}

enum Role {
  USER
}