Prisma modeling many to many

Question

Im hoping there's a simple answer to this i'm missing in the docs.

I have 3 models (tables): Class, Lecture, Notes

A Class record can have many Notes A Lecture record Can have many Notes

My models look like

model Class {
   id String @id
   name String
   notes Note[]
}

model Lecture {
   id String @id
   name String
   notes notes[]
}

model Note {
   id String @id
   name String
   // Here's where i get stuck, do i need to declare everything that has a relationship here?
   classId String
   class   Class @relation(fields: [classId], references: [id])

   lectureId String
   lecture   Lecture @relation(fields: [lectureId], references: [id])
}

If i understand this correctly, my Notes table will have columns for "classId" and "lectureId" even though only one of them is used per Note record.

If I try to break this apart, and make my own realtionship models...

model LectureNote {
   ...
   @@id([lectureId, noteId])
}

model ClassNote {
   ...
   @@id([classId, noteId])
}

I think i have the same problem on my Note model having 2 references:

model Note {
  ...
  lecture LectureNote
  class  ClassNote
}

Obviously, ive dumbed this down a bit for my example - but this gets at the heart of it. Should i just be having the extra fields on the Note model even though only one or the other is ever used? In classic DB design, i could make my own bridge tables (LectureNotes and ClassNotes) and just link them up via their PKs.

Answer 1

This is a relational database design question.

As with all design, there's never one answer, but different tradeoffs.

Lets consider the two options your question listed:

Option 1: Class, Lecture, Note

model Class {
   id String @id
   name String
   notes Note[]
}

model Lecture {
   id String @id
   name String
   notes Note[]
}

model Note {
   id String @id

   name String

   classId String
   class   Class @relation(fields: [classId], references: [id])

   lectureId String
   lecture   Lecture @relation(fields: [lectureId], references: [id])
}

• pros:
  • Least amount of tables
  • Reuse and interoperability between Notes for different entities (polymorphism)
• cons:
  • Unused column in Note, taking up space
  • Potential for bug, if a single Note row is associated to both a Class and a Lecture (but can be resolved by adding constraint validation)

Option 2: Class, Lecture, ClassNote, LectureNote

model Class {
   id String @id
   name String
   notes ClassNote[]
}

model Lecture {
   id String @id
   name String
   notes LectureNote[]
}

model ClassNote {
   id String @id

   name String

   classId String
   class   Class @relation(fields: [classId], references: [id])
}

model LectureNote {
   id String @id

   name String

   lectureId String
   lecture   Lecture @relation(fields: [lectureId], references: [id])
}

• pros:
  • No unused columns
  • Decoupled Notes, so they can change independently
• cons:
  • More tables, which can make interoperability harder (e.g. what if we want all Notes, regardless if they're of Class or of Lecture)

So which one best suits your use-case?