javascriptprismaprisma2

Prisma modeling many to many


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.


Solution

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

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

    So which one best suits your use-case?