How to write my schema?

[ElBaraaRadwan]

Question

I'm trying to write a schema using Prisma, but I keep running into a problem if design and relations between models.

This is the current schema that I'm using

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

// Models

model User {
  id   Int      @id @default(autoincrement())
  role UserRole @default(PATIENT)

  patient  Patient?
  nurse    Nurse?
  headDept headDept?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  nationalID   BigInt  @unique(map: "nationalID") @db.BigInt
  email        String? @unique(map: "UserMail")
  phone        BigInt? @unique(map: "UserPhone") @db.BigInt
  name         String?
  passwordHash String?
}

enum UserRole {
  PATIENT
  NURSE
  HEADDEPT
}

//Users models (unchanged)
model Nurse {
  patients   Patient[] // OneToMany Relationship (Nurse has many Paitents)
  department Department @relation("NurseToDept", fields: [depID], references: [id], onDelete: Cascade, onUpdate: Cascade)
  depID      Int
  isActive   Boolean?   @default(true)
  user       User       @relation(fields: [nurseID], references: [id], onDelete: Cascade, onUpdate: Cascade)
  nurseID    Int        @unique
  program      Program? @relation(fields: [programID], references: [id], onDelete: SetNull)
  programID    Int?

  @@id([nurseID])
  @@index([depID])
}

model Patient {
  nurse     Nurse @relation(fields: [nurseID], references: [nurseID], onDelete: Cascade, onUpdate: Cascade)
  nurseID   Int
  user      User  @relation(fields: [patientID], references: [id], onDelete: Cascade, onUpdate: Cascade)
  patientID Int   @unique
  program      Program? @relation(fields: [programID], references: [id], onDelete: SetNull)
  programID    Int?
  discharged   Boolean  @default(false)

  @@id([patientID])
  @@index([nurseID])
}

model headDept {
  user       User        @relation(fields: [headDepID], references: [id], onDelete: Cascade, onUpdate: Cascade)
  headDepID  Int         @unique
  department Department?
  depID      Int         @unique

  @@id([headDepID])
  @@index([depID])
}

model Department {
  id         Int       @id @default(autoincrement())
  name       String    @unique(map: "DepartmentName")
  nurses     Nurse[]   @relation("NurseToDept") // OneToMany Relationship (Department has many Nurses)
  headDept   headDept? @relation(fields: [headDeptID], references: [headDepID], onDelete: SetNull)
  headDeptID Int?      @unique
}

model Program {
  id                        Int      @id @default(autoincrement())
  createdAt                 DateTime @default(now())
  updatedAt                 DateTime @updatedAt
  
  name                      String   @unique(map: "ProgramName")
  potentialProblems         String
  preventionAndTherapies    String
  medications               String
  diagnosticTestsAndMonitoring String
  duration                  DateTime
  
  nurse                     Nurse[] 
  patient                   Patient[]
}

and this is the OLD schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

// Models

//Users models (unchanged)
model Nurse {
  id           Int       @id @default(autoincrement())
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

  nationalID   BigInt    @unique(map: "nationalID") @db.BigInt
  email        String?   @unique(map: "UserMail")
  phone        BigInt?   @unique(map: "UserPhone") @db.BigInt
  name         String?
  passwordHash String

  program      Program?   @relation(fields: [programID], references: [id], onDelete: SetNull)
  programID    Int?
  department   Department @relation("NurseToDept", fields: [depID], references: [id], onDelete: Cascade, onUpdate: Cascade)
  depID        Int

  @@index([depID])
  @@index([programID])
}

model Patient {
  id           Int       @id @default(autoincrement())
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

  nationalID   BigInt    @unique(map: "nationalID") @db.BigInt
  email        String?   @unique(map: "UserMail")
  phone        BigInt?   @unique(map: "UserPhone") @db.BigInt
  name         String?
  passwordHash String

  program      Program? @relation(fields: [programID], references: [id], onDelete: SetNull)
  programID    Int?
  discharged   Boolean  @default(false)
  
  @@index([programID])
}

model headDept {
  id           Int       @id @default(autoincrement())
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

  nationalID   BigInt    @unique(map: "nationalID") @db.BigInt
  email        String?   @unique(map: "UserMail")
  phone        BigInt?   @unique(map: "UserPhone") @db.BigInt
  name         String?
  passwordHash String

  department   Department?
  depID        Int       @unique

  @@index([depID])
}

model Department {
  id         Int       @id @default(autoincrement())
  name       String    @unique(map: "DepartmentName")
  nurses     Nurse[]   @relation("NurseToDept") // OneToMany Relationship (Department has many Nurses)
  headDept   headDept? @relation(fields: [headDeptID], references: [id], onDelete: SetNull)
  headDeptID Int?      @unique
}

model Program {
  id                        Int      @id @default(autoincrement())
  createdAt                 DateTime @default(now())
  updatedAt                 DateTime @updatedAt
  
  name                      String   @unique(map: "ProgramName")
  potentialProblems         String
  preventionAndTherapies    String
  medications               String
  diagnosticTestsAndMonitoring String
  duration                  DateTime
  
  nurse                     Nurse[] 
  patient                   Patient[]
}

the problem that I keep running at
1- is the self-relation in explicit way is correct or am i doing it wrong
2- the connection between models in the function is not visible any more alike the OLD schema

so is i started to have a doubt about this schema and should just get back to the old one but it has its own problem to, like
duplicated lines in the schema that result to more function and routes for handling every model.
not mentioning how hard it is to handle the relation between them.

So, what should i do to solve this confusion?
what should i do to make a better version of my schema for handling relations and not to have duplicated lines and functions/routes?

[ludralph]

Hi @ElBaraaRadwan 👋

In your old schema, you have separate models for Nurse, Patient, and headDept, each with their own unique fields. Each of these models has its own unique fields like nationalID, email, phone, name, and passwordHash. This means you have to manage separate records for each type of user. This could lead to data duplication if a user can be both a Nurse and a Patient, for example.

This is different from your new schema where you have a single User model with optional relations to Nurse, Patient, and headDept. The relations in your old schema are similar to your new schema. You have correctly defined one-to-many and one-to-one relations. For example, a Nurse can have many Patients, and a Department can have many Nurses. Your new schema seems to be well-structured and correctly defines the relations between different models.

[ElBaraaRadwan]

Hi @ludralph
Thank you for taking some of your time to answer my questions, I actually made a new schema sense the old one did not work well for me 😆, and it became more readable and less complicated

enum Role {
  patient
  nurse
  headdept
}

enum Gender {
  FEMALE
  MALE
}

model User {
  id              String    @id @default(uuid())
  name            String
  email           String?   @unique
  nationalID      String    @unique 
  phone           String?   @unique 
  password        String
  role            Role
  gender          Gender?
  tokens      Token[]
  allergies       Json?     @db.Json
  nursePrograms   Program[] @relation("nursePrograms")
  patientPrograms Program[] @relation("patientPrograms")

  nurseDepartments Department? @relation("NurseToDept", fields: [departmentId], references: [id])
  departmentId     Int?

  headDeptDepartment Department? @relation("HeadDeptDepartment")
  createdAt          DateTime    @default(now())
  updatedAt          DateTime    @updatedAt

  @@index([departmentId])
}

model Program {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name                         String   @unique
  potentialProblems            Json?    @db.Json
  preventionAndTherapies       Json?    @db.Json
  medications                  Json?    @db.Json
  diagnosticTestsAndMonitoring Json?    @db.Json
  duration                     DateTime
  patients                     User[]   @relation("patientPrograms")
  nurses                       User[]   @relation("nursePrograms")
}

model Department {
  id     Int    @id @default(autoincrement())
  name   String @unique
  nurses User[] @relation("NurseToDept") // OneToMany Relationship (Department has many Nurses)

  headDept   User?    @relation("HeadDeptDepartment", fields: [headDeptID], references: [id], onDelete: NoAction, onUpdate: NoAction)
  headDeptID String?  @unique
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  @@index([headDeptID])
}


model Token {
 id         Int       @id @default(autoincrement())
 createdAt  DateTime  @default(now())
 updatedAt  DateTime  @updatedAt
 type       TokenType
 emailToken String?   @unique // Only used for short lived email tokens
 valid      Boolean   @default(true)
 expiration DateTime

 // Relation fields
 user   User @relation(fields: [userId], references: [id])
 userId String

 @@index([userId])
}

enum TokenType {
 EMAIL // used as a short-lived token sent to the user's email
 API
}

So, i'm now trying to display the related data to user only and didn't find other way than using SELECT every column that should show in Patient or Nurse.etc. the is not like an omit or exclude to be used