Building a Real-estate SQL schema: Explicit many-to-many relationship or alternatives

Question

We have an instance of a "Listing" model, and we want to be able to assign it a "listing type," e.g., Sale / Rent / (or Both). If it is both, how can we have both in a list to query them by their "listing type". We've been thinking about doing a many-to-many relationship and creating a model for the "ListingType." That way, the "Listing" model can hold multiple instances of the "ListingType" model.

(We tried to do an implicit many-to-many relationship, but PlanetScale complained because they don't support foreign keys, so we're thinking about trying an explicit many-to-many relation, but we would like some guidance on alternatives. Thank you ahead of time.

(We are using Prisma & PlanetScale)

model Listing {
  id                Int                    @id @default(autoincrement())
  name              String
  slug              String                 @unique
  bio               String
  price             Int
  userId            Int
  user              User                   @relation(fields: [userId], references: [id])
  status            ListingStatus          @default(ARCHIVED)
  visibility        ListingVisibility      @default(PUBLIC)
  createdAt         DateTime               @default(now())
  updatedAt         DateTime               @updatedAt
  listingTypes      ListingOnListingType[]
}

model ListingType {
  id                   Int                    @id @default(autoincrement())
  name                 ListingTypeEnum        @unique
  ListingOnListingType ListingOnListingType[]
}

model ListingOnListingType {
  listing       Listing     @relation(fields: [listingId], references: [id])
  listingId     Int
  listingType   ListingType @relation(fields: [listingTypeId], references: [id])
  listingTypeId Int
  assignedAt    DateTime    @default(now())
  assignedBy    String

  @@id([listingId, listingTypeId])
}

Answer 1

You CAN use implicit many-to-many relations in Prisma with Planetscale. Make sure you've updated to the latest version of Prisma.

The downside is that it has to perform a full-table lookup, so explicit many-to-many with an @@index is ideal