Theo's Typesafe CultTTC
Theo's Typesafe Cult•3y ago•
2 replies
EXILE

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])
}


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
}


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])
}


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..
Was this page helpful?