Prisma•4mo ago

Refactoring schema

I have the following schema:

model Invoice {
  id         Int      @id @default(autoincrement())

  // ...

  // Relations
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  organizationId Int
  attachments    Attachment[]
}

model Attachment {
  id   Int    @id @default(autoincrement())
  name String
  path String

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  invoice   Invoice @relation(fields: [invoiceId], references: [id], onDelete: Cascade)
  invoiceId Int
}

model Organization {
  id            Int      @id @default(autoincrement())

  // ...
  
  // Relationships
  invoices         Invoice[]

  logo   Logo? @relation(fields: [logoId], references: [id])
  logoId Int?  @unique
}

model Logo {
  id   Int    @id @default(autoincrement())
  name String
  path String

  // Timestamps
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
  Organization Organization?
}

model Invoice {
  id         Int      @id @default(autoincrement())

  // ...

  // Relations
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  organizationId Int
  attachments    Attachment[]
}

model Attachment {
  id   Int    @id @default(autoincrement())
  name String
  path String

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  invoice   Invoice @relation(fields: [invoiceId], references: [id], onDelete: Cascade)
  invoiceId Int
}

model Organization {
  id            Int      @id @default(autoincrement())

  // ...
  
  // Relationships
  invoices         Invoice[]

  logo   Logo? @relation(fields: [logoId], references: [id])
  logoId Int?  @unique
}

model Logo {
  id   Int    @id @default(autoincrement())
  name String
  path String

  // Timestamps
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
  Organization Organization?
}

Both the logos and Attachment are the same thing: files. So I thought that maybe I should refactor it, into the same table, but then apparently I am forced to do this

model File {
  id          Int       @id @default(autoincrement())
  name        String
  path        String

  // Timestamps
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Relations
  organization Organization? @relation(fields: [organizationId], references: [id])
  organizationId Int?
  invoice      Invoice? @relation(fields: [invoiceId], references: [id])
  invoiceId    Int?
}

model File {
  id          Int       @id @default(autoincrement())
  name        String
  path        String

  // Timestamps
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Relations
  organization Organization? @relation(fields: [organizationId], references: [id])
  organizationId Int?
  invoice      Invoice? @relation(fields: [invoiceId], references: [id])
  invoiceId    Int?
}

Which seems like a waste, because now the model File will need to update, everytime there is a new model that references the file. It does not scale well. What would be the best way to optimize my Schema?

2 Replies

Dbugger•4mo ago

Nobody?

jonfanz•4mo ago

table inheritance like this isn't directly supported in Prisma, but we have a pretty handy doc page about it: https://www.prisma.io/docs/orm/prisma-schema/data-model/table-inheritance Let me know if you have any follow up questions from there

Table inheritance | Prisma Documentation

Learn about the use cases and patterns for table inheritance in Prisma ORM that enable usage of union types or polymorphic structures in your application.

Gaming

Programming

Refactoring schema