I use Prisma and Postgresql for a project to store and manage recipes.
I am struggling to implement a Many to Many relationship where I can constrain one side to require at least one value for the relation field.
Recipes can have one or more aromas. Aromas can be included in zero or more recipes.
I use an explicit relation table because I need to store additional data in it (the quantity for each aroma in a recipe).
The Recipe, Aroma and relation models are the following:
model Recipe {
id Int @id @default(autoincrement())
name String
base String? @default("50/50")
description String? @default("aucune description")
rating Int? @default(1)
aromas RecipeToAromas[]
}
model Aroma {
id Int @id @default(autoincrement())
name String
brand Brand? @relation(fields: [brandId], references: [id])
brandId Int? @default(1)
recipes RecipeToAromas[]
@@unique([name, brandId], name: "aromaIdentifier")
}
model RecipeToAromas {
id Int @id @default(autoincrement())
recipeId Int
aromaId Int
quantityMl Int
recipe Recipe @relation(fields: [recipeId], references: [id])
aroma Aroma @relation(fields: [aromaId], references: [id])
}
I want to constrain recipes to have at least one aroma.
By definition Many to Many defines zero to many relationship.
I thought about solving the problem with adding an additional One to Many relationship between Recipe and Aroma.
That would imply adding an additional aroma field in Recipe to store the one aroma that is required (and rename aromas field to additionalAromas to avoid confusion) :
model Recipe {
id Int @id @default(autoincrement())
name String
base String? @default("50/50")
description String? @default("aucune description")
rating Int? @default(1)
aromas RecipeToAromas[]
aroma Aroma @relation(fields: [aromaId], references: [id])
aromaId Int
}
And adding a recipe field in Aroma as it required to establish the relation :
model Aroma {
id Int @id @default(autoincrement())
name String
brand Brand? @relation(fields: [brandId], references: [id])
brandId Int? @default(1)
recipes RecipeToAromas[]
recipe Recipe[]
@@unique([name, brandId], name: "aromaIdentifier")
}
But that feels wrong as I will have duplicates : recipes and recipe fields in Aroma would store identical data.
I could of course workaround the problem by just relying on validation in mutation functions and user input. And probably try to add a layer of safety with types as I am using typescript.
But I feel like it would make the database brittle and is prone to error especially if I have to collaborate with other devs, or even use the database in a different projet.
I could not find any resource covering a similar situation, and of course I have spend a lot of time searching and re-reading the documentation.
I am new to prisma (started yesterday) and I dont have too much experience with RDBMS, so it feels like I am missing something.