Prisma Party Relationship Schema

Get Help and Help Others Show & Tell
1

I wrote a schema for the Entity/Party relationship based on this Vertabelo model. Feedback, thoughts, improvements or contributions are welcome. I would be happy for anyone to review and update or critique. If I should post this in a github or somewhere else, let me know and I will. A more robust implementation would include the helper functions to traverse this thing.

The one strange thing to me is the user/organization mapping to the party with no relation. However, I do believe I got that right…


model User {
  id                   Int           @id @default(autoincrement())
  prefix               String?
  firstName            String
  lastName             String
  suffix               String?
  email                String        @unique
  phone                String?
  userName             String?
  createdAt            DateTime      @default(now())
  hashedPassword       String
  salt                 String
  resetToken           String?
  resetTokenExpiresAt  DateTime?
}

model Organization {
  id   Int    @id @default(autoincrement())
  Name String
}

model Party {
  id                    Int                 @id @default(autoincrement())
  partyIdentifier       Int // This will reference User ID or Organization Id
  party1stInteractor    PartyRelationship[] @relation("party1")
  party2ndInteractor    PartyRelationship[] @relation("party2")
  partyIdentifierType   PartyIdentifierType @relation(fields: [partyIdentifierTypeId], references: [id])
  partyIdentifierTypeId Int
  partyType             PartyType           @relation(fields: [partyTypeId], references: [id])
  partyTypeId           Int
}

model PartyRelationship {
  id                     Int                  @id @default(autoincrement())
  effPeriodFrom          DateTime
  effPeriodTo            DateTime
  relationshipRef        String
  party1stInteractor     Party                @relation("party1", fields: [party1stInteractorId], references: [id])
  party1stInteractorId   Int                  @unique
  party2ndInteractor     Party                @relation("party2", fields: [party2ndInteractorId], references: [id])
  party2ndInteractorId   Int                  @unique
  partyIdentifierType    PartyIdentifierType  @relation(fields: [partyIdentifierTypeId], references: [id])
  partyIdentifierTypeId  Int
  roleTypeRelationship   RoleTypeRelationship @relation(fields: [roleTypeRelationshipId], references: [id])
  roleTypeRelationshipId Int
}

model PartyIdentifierType {
  id                Int                 @id @default(autoincrement())
  altSequence       Int
  description       String
  effPeriodFrom     DateTime
  effPeriodTo       DateTime
  prettyName        String
  typeKey           String
  party             Party[]
  partyRelationship PartyRelationship[]
}

model PartyType {
  id            Int        @id @default(autoincrement())
  altSequence   Int
  description   String
  effPeriodFrom DateTime
  effPeriodTo   DateTime
  prettyName    String
  typeKey       String
  party         Party[]
  roleType      RoleType[]
}

model RoleType {
  id                          Int                    @id @default(autoincrement())
  effPeriodFrom               DateTime
  effPeriodTo                 DateTime
  prettyName                  String
  typeKey                     String
  partyType                   PartyType              @relation(fields: [partyTypeId], references: [id])
  partyTypeId                 Int
  roleTypeRelationship1st     RoleTypeRelationship[] @relation("roleType1st")
  roleTypeRelationship2nd     RoleTypeRelationship[] @relation("roleType2nd")
  roleTypeRelationshipContext RoleTypeRelationship[] @relation("roleTypeContext")
}

model RoleTypeRelationship {
  id                   Int                 @id @default(autoincrement())
  description          String
  descriptionDirection String
  typeofRelationship   String
  roleType1st          RoleType            @relation("roleType1st", fields: [roleType1stId], references: [id])
  roleType1stId        Int
  roleType2nd          RoleType            @relation("roleType2nd", fields: [roleType2ndId], references: [id])
  roleType2ndId        Int
  roleTypeContext      RoleType?           @relation("roleTypeContext", fields: [roleTypeContextId], references: [id])
  roleTypeContextId    Int?
  partyRelationship    PartyRelationship[]
}
2 Likes