I'm currently working with a Prisma schema that includes products, orders, and a many-to-many relationship between them. My goal is to store the product price in the relation table so that I can capture the price of the product at the time of sale, regardless of any future price changes. Is there a way to achieve this in Prisma without having to retrieve the current prices of all products?
Below is the schema for reference:
model Product {
id String @id @default(cuid())
name String
// todo add check stock can't be less than zero
stock Int
// todo add check price can't be less than zero
buyPrice Float
sellPrice Float
image String
createdAt DateTime @default(now())
createdBy User @relation(fields: [createdById], references: [id])
createdById String
category Category @relation(fields: [categoryId], references: [id])
categoryId String
orders ProductsOnOrder[]
}
model Order {
id String @id @default(cuid())
// guess it's suppose to be computed value
total Float
paymentType PaymentType @default(CASH)
createdAt DateTime @default(now())
createdById String
createdBy User @relation(fields: [createdById], references: [id])
products ProductsOnOrder[]
}
model ProductsOnOrder {
productId String
Product Product @relation(fields: [productId], references: [id])
orderId String
order Order @relation(fields: [orderId], references: [id])
quantity Int @default(1)
price Float // Price of the product at the time of the order
@@id([productId, orderId])
}