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.
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:
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])
}
Note
s for different entities (polymorphism)Note
, taking up spaceNote
row is associated to both a Class
and a Lecture
(but can be resolved by adding constraint validation)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])
}
Note
s, so they can change independentlyNote
s, regardless if they're of Class
or of Lecture
)So which one best suits your use-case?