Having several mysql tables (Toys, Users, Pets), where you want to add Categories in a many to many relation. Im wondering how the Prisma Schema should be, to include the Relations in it when using this Polymorphic Association so i can do better queries without having to join tables.
CREATE TABLE `Categories` (
`id` VARCHAR(36) NOT NULL PRIMARY KEY,
`name` VARCHAR(255)
);
CREATE TABLE `Toys` (
`id` VARCHAR(36) NOT NULL PRIMARY KEY,
`companionName` VARCHAR(255),
);
CREATE TABLE `Users` (
`id` VARCHAR(36) NOT NULL PRIMARY KEY,
`userName` VARCHAR(255),
);
CREATE TABLE `Pets` (
`id` VARCHAR(36) NOT NULL PRIMARY KEY,
`petName` VARCHAR(255),
);
CREATE TABLE `CategoryRelation` (
`id` VARCHAR(36) NOT NULL PRIMARY KEY,
`categoryId` VARCHAR(36),
`entityId` VARCHAR(36),
`entityType` VARCHAR(255), //Toys, Users, Pets
`created_at` datetime
FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`)
);
The Prisma Model i woud like to have is something similar to this. (added <-- ??? where i dont know what to use). Please take into account this is a basic example to show the problem, but we are working on a real and more complex database where we want to implement Prisma, so we cannot create lots of Many-to-Many automatic prisma relation tables, we need to continue with this Polymorphic approach
model Categories {
id Int @id @default(autoincrement())
name String
toys Toys[]
pets Pets[]
users Users[]
}
model Toys {
id Int @id @default(autoincrement())
companionName String
categories Category[] @relation("ToyCategoryRelation") <-- ????
}
model Users {
id Int @id @default(autoincrement())
userName String
categories Category[] @relation("UserCategoryRelation") <-- ????
}
model Pets {
id Int @id @default(autoincrement())
petName String
categories Category[] @relation("PetCategoryRelation") <-- ????
}
model CategoryRelation {
id Int @id @default(autoincrement())
categoryId Int
entityId Int
entityType String // Toys, Pets, Users
created_at DateTime
category Category @relation(fields: [categoryId], references: [id])
entity [Toys/Pets/User]??? @relation(fields: [entityId], references: [id]) <-- ????
}