I have two tables. An

Items

table and a

Sales

. I want the

Sales

table to represent sold items but be able to have some different fields if the sale is on a different platform. I was thinking maybe just have different types of

Sales

tables but then

Items

would have a number of possible unused relations. I've used raw SQL before and designed some toy databases but never

prisma.

Does anyone have any advice on how to proceed? Here's my schema so far:

model Item {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String
  desc      String?
  quality   String?
  quantity  Int      @default(1)
  sale      Sale     @relation(fields: [saleId], references: [id])
  saleId    Int // relation scalar field (used in the `@relation` attribute above)

}

model Sale {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  boughtPrice Decimal
  item        Item?
  platform    String //Enum on postgress EBAY,MERCARI,ETC
}

@Nole Stock 👋 Prisma design and database design is similar as Prisma uses the same concepts. If you need to represent Sales to be sold items, then it would be a 1-many relation as follows:

model Item {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String
  desc      String?
  quality   String?
  quantity  Int      @default(1)
  sale      Sale     @relation(fields: [saleId], references: [id])
  saleId    Int // relation scalar field (used in the `@relation` attribute above)
}

model Sale {
  id             Int           @id @default(autoincrement())
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  boughtPrice    Decimal
  items          Item[]
  platform       String //Enum on postgress EBAY,MERCARI,ETC
  platformData   PlatformData? @relation(fields: [platformDataId], references: [id])
  platformDataId Int
}

model PlatformData {
  id   Int   @id @default(autoincrement())
  sale Sale?
}

Also the extra platform data can be modelled as another relation if you would like to store extra fields.

Cool, thanks. So something like this if I wanted to have some extra fields that are only specfic to a platform?

model Item {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String
  desc      String?
  quality   String?
  quantity  Int      @default(1)
  sale      Sale     @relation(fields: [saleId], references: [id])
  saleId    Int // relation scalar field (used in the `@relation` attribute above)
}

model Sale {
  id             Int           @id @default(autoincrement())
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  boughtPrice    Decimal
  items          Item[]
  platform       String //Enum on postgress EBAY,MERCARI,ETC
  platformData   PlatformData? @relation(fields: [platformDataId], references: [id])
  platformDataId Int
}

model PlatformData {
  id         Int       @id @default(autoincrement())
  sale       Sale?
  platform   Platform? @relation(fields: [platformId], references: [id])
  platformId Int
}

model Platform {
  id           Int            @id @default(autoincrement())
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt
  name         String
  feild1       Decimal
  feild2       Decimal
  feild3       Decimal
  PlatformData PlatformData[]
}

I think you can directly link it:

model Item {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String
  desc      String?
  quality   String?
  quantity  Int      @default(1)
  sale      Sale     @relation(fields: [saleId], references: [id])
  saleId    Int // relation scalar field (used in the `@relation` attribute above)
}
model Sale {
  id             Int           @id @default(autoincrement())
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  boughtPrice    Decimal
  items          Item[]
  platform       String //Enum on postgress EBAY,MERCARI,ETC
  platform   Platform? @relation(fields: [platformId], references: [id])
  platformId Int
}

model Platform {
  id           Int            @id @default(autoincrement())
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt
  name         String
  feild1       Decimal
  feild2       Decimal
  feild3       Decimal
  sales        Sale[]
}

Wow, ok that's much more simple and makes sense. Thank you very much for your time and help!

Sure! Let me know how it goes 🙂