How to create 4-level nested relationship with Prisma

Question

I have 3 main types: Company, Service, and Specialty. Every Company has a list of Service. Every Service have a list of Specialty. Every Specialty have a list of Specialty (sub-specialties).

Company 1
- Service 1
-- Specialty 1
--- Sub-specialty 1

So I am working on this datamodel on Prisma 1.34:

type Company {
  id: ID! @id
  name: String! @unique
  services: [Service]
}
type Service {
  id: ID! @id
  name: String! @unique
  companies: [Company]! @relation(link: TABLE)
  specialties: [Specialty]
}
type Specialty {
  id: ID! @id
  name: String! @unique
  companies: [Company]! @relation(link: TABLE)
  services: [Service] @relation(link: TABLE)
  sub_specialties: [Specialty] @relation(link: TABLE)
}

My problem is, when I add one Company and use the same Service for that Company in another Company, all other Specialty and Sub-specialty records are coming along with that Service. I want to make Specialty and Sub-specialty fields unique for every company but they also should be under related Service under every company.

I have been working on it for the past 3 days, I would appreciate any help.

Answer 1

If you're committed to a nested relationship, then you're going to have to make the Service type have non-unique names. This will force a new Service model to be created for each company while still allowing Specialty to be more generalized and have better reuse.

The logic here is that if there are multiple Services with the same name but with different Specialty attributes, then they are NOT the same.

Answer 2

Here is the solution I came up with: I am holding unique service and specialty names in ServiceItem and SpecialtyItem tables. Whenever I add a new Service to a company, I link that Service to a specific ServiceItem that is unique. That Service and Specialty receives it's information (only name property in this project) from linked ServiceItem and SpecialtyItem. That way, whenever I update my ServiceItem or SpecialtyItem, all related Service and Specialty fields under each company get updated. Here is the datamodel:

type Company {
  id: ID! @id
  name: String! @unique
  services: [Service]! @relation(onDelete: CASCADE)
}

type ServiceItem {
  id: ID! @id
  name: String! @unique
}

type Service {
  id: ID! @id
  info: ServiceItem! @relation(link: INLINE)
  company: Company! @relation(link: INLINE)
  specialties: [Specialty] @relation(onDelete: CASCADE)
}

type SpecialtyItem {
  id: ID! @id
  name: String! @unique
}

type Specialty {
  id: ID! @id
  info: SpecialtyItem! @relation(link: INLINE)
  service: Service @relation(link: INLINE)
  sub_specialties: [Specialty] @relation(onDelete: CASCADE)
}