Hey guys, Really awesome having been referred to Prisma by a colleague. I'm absolutely loving it 🙂 I have a query, which is why I'm here. Consider the following SQL Script:

CREATE TABLE [Movie] (
  [id] int PRIMARY KEY,
  [title] nvarchar(255)
)

CREATE TABLE [User] (
  [id] int PRIMARY KEY,
  [username] nvarchar(255)
)

CREATE TABLE [Theater] (
  [id] int PRIMARY KEY,
  [theatre_name] nvarchar(255)
)

CREATE TABLE [Schedule] (
  [id] int PRIMARY KEY,
  [sdt] datetime,
  [movieId] int REFERENCES Movie (id),
  [userId] int REFERENCES User (id),
  [theatreId] int REFERENCES Theater (id)
)

versus the equivalent prisma.schema

model Movie {
  id       Int        @id @default(autoincrement())
  title    String
  Schedule Schedule[]
}

model User {
  id       Int        @id @default(autoincrement())
  name     String
  Schedule Schedule[]
}

model Theatre {
  id       Int        @id @default(autoincrement())
  name     String
  Schedule Schedule[]
}

model Schedule {
  id        Int      @id @default(autoincrement())
  movieId   Int
  theatreId Int
  userId    Int
  date      DateTime
  movie     Movie    @relation(fields: [movieId], references: [id])
  theatre   Theatre  @relation(fields: [theatreId], references: [id])
  user      User     @relation(fields: [userId], references: [id])
}

I notice that all Theatre, User and Movie have to reference Schedule in Prisma. However I don't have to do similar when it comes to SQL. Is there a way to achieve this SQL simplicity? I'm open to learning about the reason for this design if there are better reasons I should rather follow this format. However, I have a simple approach to design the database where I'm happy to be making multiple calls for related data within the BFF. Further, I have some tables that have two or three relationships related to them and it becomes a less nice to read so much information stuck into just one model.

Hey Aderito 👋 , we are glad that you are enjoying Prisma so far! Prisma represents the “other side” of the one-to-many relation by adding the

Schedule

field in each of the respective models. The underlying database tables are actually the exact same as you describe in your diagram. When you are querying with Prisma, you conceptually think that a

Theater

has many

Schedules

, and representing it this way in the Prisma Schema helps make that clear. When you are querying with Prisma, you conceptually think that

Hi, Austin. I like the concept. It’s great and it makes sense. I believe I should play with the approach some more. My thought is - and I’m new to database design -If I still wanted this approach would my best best be maintaining SQL scripts like the example I posted and I believe Prisma can query the database and generate the schema for me? I’m in experimentation phase right now so don't wanna rule out Prisma’s approach or “SQL” approach.