Prisma self-relation extra data field

Question

I'm trying to use prisma to model relationships between people with basic familial relation labels. The way that I'm thinking about modeling this relationship is using three tables "Person", "Relationships" and "Relation". Person for the individual's data, Relationships as the join table, and Relation describing the type of relationship (e.g. "father", "son", etc.). But I can't seem to get the foreign key relationships setup correctly... Here is what I have so far:

model Person {
  id       Int       @id @default(autoincrement())
  name     String
  slug     String    @unique
  relationships Relationship[]
}

model Relationship {
  id        Int       @id @default(autoincrement())
  relative Person? @relation(fields: [relativeId], references: [id])
  relativeId Int?
  relation Relation? @relation(fields: [relationId], references: [id])
  relationId Int?
  startDate DateTime?
  endDate   DateTime?
}

model Relation {
  id              Int    @id @default(autoincrement())
  name            String
  relationships Relationship[]
}

Any pointers appreciated!

Answer 1

I was coming from Mongo and forgetting that there are no nested data sets in SQL 😖. So the trick here was to include two references to the Person model in the Relationship/Join table I had above. Using Prisma's explicit @relation syntax, you can denote separate self-references in the Person model. Not sure if I did the @@id in the join table correctly, but everything seems to be working now:

model Person {
  id                        Int            @id @default(autoincrement())
  name                      String
  slug                      String         @unique
  personRelationshipRecords Relationship[] @relation("person")
  personRelatives           Relationship[] @relation("relative")
}

model Relationship {
  person     Person    @relation("person", fields: [personId], references: [id])
  personId   Int
  relation   Relation  @relation("relation", fields: [relationId], references: [id])
  relationId Int
  relative   Person    @relation("relative", fields: [relativeId], references: [id])
  relativeId Int
  startDate  DateTime?
  endDate    DateTime?

  @@id([personId, relationId, relativeId])
}

model Relation {
  id              Int            @id @default(autoincrement())
  name            String
  relationships   Relationship[] @relation("relation")
}