Theo's Typesafe Cult•2y ago

Could I get some help for this Prisma schema?

I'm currently creating a "guess the rank" web-based game where you choose a competitive game, watch a short clip of gameplay and then guess the rank of the player in that clip. It's pretty simple and there's a few websites out there already. Each game can only have a single clip for that day. I reset the clip at midnight via a cron job and that all works fine. But I'm not sure if the way I'm storing the daily clip for each game is good. I originally thought I could have my Game model contain a featuredClip Clip but game also contains clips Clip[] and I can't figure out how to get this relationship to work. I've tried naming them but it didn't work. Still new to this. I'm currently trying to clean up my database and I wanted to know if the way I'm currently doing it is fine or should I go for the seconds approach, or even something different. First approach: - A clip that is user submitted. - Contains data about the submitted video file that's stored wherever, the rank, the youtubeId which is only set if I accept the clip and then hit the youtube api to upload the video, acceptedDate, isAccepted etc... You can see this table below.

model Clip {
  id              String       @id @default(cuid())
  videoFilePath   String
  rank            Rank         @relation(fields: [rankId], references: [id])
  rankId          String
  youtubeId       String?
  submittedDate   DateTime     @default(now())
  acceptedDate    DateTime?
  isAccepted      Boolean      @default(false)
  hasBeenFeatured Boolean      @default(false)
  game            Game         @relation(fields: [gameId], references: [id])
  gameId          String
  player          User         @relation(fields: [playerId], references: [id])
  playerId        String
  currentClip     CurrentClip?

  @@index([playerId])
  @@index([gameId])
  @@index([rankId])
}

model Clip {
  id              String       @id @default(cuid())
  videoFilePath   String
  rank            Rank         @relation(fields: [rankId], references: [id])
  rankId          String
  youtubeId       String?
  submittedDate   DateTime     @default(now())
  acceptedDate    DateTime?
  isAccepted      Boolean      @default(false)
  hasBeenFeatured Boolean      @default(false)
  game            Game         @relation(fields: [gameId], references: [id])
  gameId          String
  player          User         @relation(fields: [playerId], references: [id])
  playerId        String
  currentClip     CurrentClip?

  @@index([playerId])
  @@index([gameId])
  @@index([rankId])
}

Then I have CurrentClip which stores the current clip for each game. There can only be one row for each game:

model CurrentClip {
  game   Game   @relation(fields: [gameId], references: [id])
  gameId String @unique
  clip   Clip   @relation(fields: [clipId], references: [id])
  clipId String @unique
}

model CurrentClip {
  game   Game   @relation(fields: [gameId], references: [id])
  gameId String @unique
  clip   Clip   @relation(fields: [clipId], references: [id])
  clipId String @unique
}

I would prefer if I could just fetch the games, include the featuredClip as mentioned in my first paragraph and then in nextjs simply be able to get the game and do a game.featuredClip to get the current featured clip. I'm just struggling to get that to work if it's possible. Second approach: - FeaturedClip is the same as the CurrentClip

model FeaturedClip {
  gameId         String       @unique
  acceptedClipId String       @unique
  game           Game         @relation(fields: [gameId], references: [id])
  acceptedClip   AcceptedClip @relation(fields: [acceptedClipId], references: [id])
}

model AcceptedClip {
  id              String        @id @default(cuid())
  youtubeId       String        @unique
  submittedClipId String        @unique
  gameId          String
  acceptedAt      DateTime      @default(now())
  game            Game          @relation(fields: [gameId], references: [id])
  submittedClip   SubmittedClip @relation(fields: [submittedClipId], references: [id])
  featuredClip    FeaturedClip?

  @@index([submittedClipId])
  @@index([gameId])
}

model SubmittedClip {
  id            String        @id @default(cuid())
  videoFilePath String
  gameId        String
  rankId        String
  userId        String
  submittedAt   DateTime      @default(now())
  rank          Rank          @relation(fields: [rankId], references: [id])
  game          Game          @relation(fields: [gameId], references: [id])
  user          User          @relation(fields: [userId], references: [id])
  acceptedClip  AcceptedClip?

  @@index([userId])
  @@index([gameId])
  @@index([rankId])
}

model FeaturedClip {
  gameId         String       @unique
  acceptedClipId String       @unique
  game           Game         @relation(fields: [gameId], references: [id])
  acceptedClip   AcceptedClip @relation(fields: [acceptedClipId], references: [id])
}

model AcceptedClip {
  id              String        @id @default(cuid())
  youtubeId       String        @unique
  submittedClipId String        @unique
  gameId          String
  acceptedAt      DateTime      @default(now())
  game            Game          @relation(fields: [gameId], references: [id])
  submittedClip   SubmittedClip @relation(fields: [submittedClipId], references: [id])
  featuredClip    FeaturedClip?

  @@index([submittedClipId])
  @@index([gameId])
}

model SubmittedClip {
  id            String        @id @default(cuid())
  videoFilePath String
  gameId        String
  rankId        String
  userId        String
  submittedAt   DateTime      @default(now())
  rank          Rank          @relation(fields: [rankId], references: [id])
  game          Game          @relation(fields: [gameId], references: [id])
  user          User          @relation(fields: [userId], references: [id])
  acceptedClip  AcceptedClip?

  @@index([userId])
  @@index([gameId])
  @@index([rankId])
}

Now with this approach is a WIP. I don't know if it's correct so far. In order to fix some relationship errors I've needed to add duplicate rows from the tables. You can see multiple game fields etc..

1 Reply

EXILEOP•2y ago

If I do the second approach the game model would need a submittedClips SubmittedClips[] but then also one for the acceptedClips. Which means I would then need the game and game id on both submitted and accepted clips which is just duplicate data I dont need right? Super new to prisma btw

Gaming

Programming

Could I get some help for this Prisma schema?