Hi, I’m quite new to Prisma and I’m wondering how I could create a model for a many-to-many relationship that works as follow I want to have a model

Quotation

and one

Invoice

, these two models are very similar and I can create a quotation and once the quotation is accepted, I can create an invoice for this quotation. When I create the quotation I’d like to be able to add sections and items to these sections. When I create an invoice I can either attach a quotation to this invoice or add sections and items to these sections. I took a look at the documentation on Prisma for the many-to-many relationships https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations, but I’m unsure how to do this when two tables depend on this. In my case I want to have a many-to-many relationship between

Quotation

,

Section

and

SectionItem

and

Invoice

,

Section

SectionItem

Here are the models I’ve created so far

model Client {
  id                  String      @id @default(uuid())
  name                String
  street              String
  postalCode          String
  city                String
  email               String
  typeId              String
  type                ClientType  @relation(fields: [typeId], references: [id], onDelete: Restrict)
  createdAt           DateTime    @default(now())
  updatedAt           DateTime    @updatedAt
  phoneNumberMobile   String?
  phoneNumberLandLine String?
  invoices            Invoice[]
  quotations          Quotation[]
}

model ClientType {
  id      String   @id @default(uuid())
  value   String
  clients Client[]
}

model Category {
  id       String    @id @default(uuid())
  value    String
  stocks   Stock[]
  services Service[]
}

model Stock {
  id         String   @id @default(uuid())
  name       String
  quantity   Int      @default(0)
  price      Float    @default(0)
  categoryId String
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Restrict)
}

model Invoice {
  id              String        @id @default(uuid())
  number          Int           @unique
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt
  invoiceStatusId String
  status          InvoiceStatus @relation(fields: [invoiceStatusId], references: [id], onDelete: Restrict)
  dueOn           DateTime
  validUntil      DateTime
  clientId        String
  client          Client        @relation(fields: [clientId], references: [id], onDelete: NoAction)
  quotationId     String?       @unique
  quotation       Quotation?    @relation(fields: [quotationId], references: [id], onDelete: NoAction)
  sections        Section[]
}

model Quotation {
  id              String    @id @default(uuid())
  number          Int
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  invoiceStatusId String
  validUntil      DateTime
  clientId        String
  client          Client    @relation(fields: [clientId], references: [id], onDelete: NoAction)
  invoice         Invoice?
  sections        Section[]
}

model InvoiceStatus {
  id       String    @id @default(uuid())
  value    String
  invoices Invoice[]
}

model Service {
  id         String   @id @default(uuid())
  name       String
  price      Float
  categoryId String
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Restrict)
}

model SectionUnit {
  id              String    @id @default(uuid())
  value           String
  serviceSections Section[]
}

model SectionItem {
  id               String    @id @default(uuid())
  name             String
  description      String    @db.Text
  serviceSectionId String
  sections         Section[]
}

model Section {
  id          String        @id @default(uuid())
  name        String
  items       SectionItem[]
  unitId      String
  unit        SectionUnit   @relation(fields: [unitId], references: [id], onDelete: Restrict)
  quotationId String?
  quotation   Quotation?    @relation(fields: [quotationId], references: [id], onDelete: NoAction)
  invoiceId   String?
  invoice     Invoice?      @relation(fields: [invoiceId], references: [id], onDelete: NoAction)
}

I’m not sure if this is the correct approach, any help would be appreciated 🙂 Thank you

@heliosalves Are you saying you would like to have a many-to-many relationship between

Quotation

and

Invoice

, that is a quotation can be related directly to multiple invoices and an invoice can be directly related to multiple quotiations? Or are you saying that a

Quotation

is related to an

Invoice

through the

Section

.

Hi @Jeremy Hinegardner, Thank you for taking the time to help and sorry for not being clearer. What I’m trying to do is two things, one, I want to have a many-to-many relationship between

Quotation

,

Section

and

SectionItem

, so a quotation can have multiple sections and a section can have multiple items. Two, I want to also have a many-to-many relationship between

Invoice

,

Section

and

SectionItem

. The reason I want this is because an

Invoice

can have a

Quotation

“attached” to it, but it can also have sections and section items added to the invoice. I’m wondering if I’d need to have

InvoiceSection

and

InvoiceSectionItem

tables that would have a relationship with

Invoice

and

QuotationSection

and

QuotationSectionItem

tables with a relationship with

Quotation

. Also, I though the way I’ve set up the relationship between

Invoice

and

Quotation

was a one-to-one. One quotation can only be associated with one invoice and one invoice can only have one quotation associated with it