This is more a design question than a coding question. Suppose the following schema:
// schema.prisma
// Solution 1
model Entity {
id Int @id @default(autoincrement())
attrs EntityAttr[]
}
model EntityAttr {
id Int @id @default(autoincrement())
value Json // or String, doesnt matter much here
// the point is I need to attach info on the
// join table of this relation
attr Attr @relation(fields: [attrId], references: [id])
entity Entity @relation(fields: [entityId], references: [id])
entityId Int
attrId Int
@@unique([entityId, attrId])
}
model Attr {
id Int @id @default(autoincrement())
entities EntityAttr[]
}
// Solution 2
model Entity {
id Int @id @default(autoincrement())
dateAttrs DateAttr[]
recordAttrs RecordAttr[]
// ... this pattern could continue for more Attr-like models
}
model DateAttr {
id Int @id @default(autoincrement())
name String
entity Entity @relation(fields: [entityId], references: [id])
value DateTime // Stronger typing in generated code
}
model RecordAttr {
// ... define another Entity @relation(...)
name String
value String
// ...
}
// ... and so on
Please note that the schema might not be 100% complete or accurate. It is mainly to get the point across.
Solution 1 has its merits where redundancy and the number of tables in the database is reduced significantly (depending on the number of Attr
s). Its downfall comes as confusing queries*
, possible case-specific type casting and no code-completion for the value
field for each Attr
-like model.
*
by confusing, I mean that the option for simplified m-n queries inprisma
is functionally disabled when using a custom join table (e.g.EntityAttr
)
Solution 2 has its merits where the generated code results in more strongly typed code generation for the value
field, however it falls in the number of generated tables (I don't actually know if more tables is a good thing or a bad thing, all I think is that if you have similar values, they ought to be in the same table).
What would you do in my shoes?
I was looking pretty long for an appropriate answer and found it here.
I'm not sure if it could be applied to your question, but this is question about prisma
and polymorphism
, so I think this code snippet might be useful for developers:
model Photo {
id Int @id @default(autoincrement())
likes Like[] @relation("PhotoLike")
}
model Video {
id Int @id @default(autoincrement())
likes Like[] @relation("VideoLike")
}
enum LikableType {
Photo
Video
}
model Like {
id Int @id @default(autoincrement())
Photo Photo? @relation("PhotoLike", fields: [likableId], references: [id], map: "photo_likableId")
Video Video? @relation("VideoLike", fields: [likableId], references: [id], map: "video_likableId")
likableId Int
likableType LikableType
}