Blueluelueluelue Posts - Answer Overflow

Blueluelueluelue

•Created by Blueluelueluelue on 1/3/2025 in #help-and-questions

Join a column with multiple tables.

I want to store all media in the system in a central table. Now the associated resource of the media can be anything, like chat, project, ticket, etc. So I have a Media model like this

model Media {
  id                 String    @id 
  resource_id        String
  resource_type      String  
  resourceA          ResourceA?  @relation("ResourceA", fields: [resource_id], references: [id])  
  resourceB          ResourceB?  @relation("ResourceA", fields: [resource_id], references: [id])
}

model Media {
  id                 String    @id 
  resource_id        String
  resource_type      String  
  resourceA          ResourceA?  @relation("ResourceA", fields: [resource_id], references: [id])  
  resourceB          ResourceB?  @relation("ResourceA", fields: [resource_id], references: [id])
}

and an arbitrary number of resource models

model ResourceA {
    id String @id
}
model ResourceB {
    id String @id
}

model ResourceA {
    id String @id
}
model ResourceB {
    id String @id
}

It compiles just fine but during insertion it fails because a foreign key constraint get violated. When I try to insert a media associated with ResourceA, the ResouceB foreign key constraint gets violated. I know a simple solution would be have a column for each resource table like resourceAId, resourceBId and so on but is there a more elegant way?

2 replies

Gaming

Programming