The best way to have many types of user in the same database-CodePudding

I building an app with 5 different users, and each user have different fields. What is the best way to model the database? im creating one table for each user, is the best way or exist another different way like put all in one table? Im not talking about performance, just the best way to organize.

Look what i have:

  model CoordinatorProfile {
    id String @id @default(uuid())

    name String @db.VarChar(255)

    image   Image?  @relation(fields: [imageId], references: [id])
    imageId String? @unique

    school School?
    user   User?

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
  }

  model TeacherProfile {
    id String @id @default(uuid())

    name String @db.VarChar(255)

    image   Image?  @relation(fields: [imageId], references: [id])
    imageId String? @unique

    school   School @relation(fields: [schoolId], references: [id])
    schoolId String @unique

    team Team[]
    user User?

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
  }

  model StudentProfile {
    id String @id @default(uuid())

    name String @db.VarChar(255)

    image   Image?  @relation(fields: [imageId], references: [id])
    imageId String? @unique

    school   School @relation(fields: [schoolId], references: [id])
    schoolId String @unique

    team   Team?   @relation(fields: [teamId], references: [id])
    teamId String?

    user User?
    text Text[]

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
  }

  model User {
    id String @id @default(uuid())

    email        String   @unique @db.VarChar(255)
    password     String   @db.VarChar(255)
    access_until DateTime @db.Timestamp

    student_profile  StudentProfile? @relation(fields: [studentProfileId], references: [id])
    studentProfileId String?         @unique

    corrector_profile  CorrectorProfile? @relation(fields: [correctorProfileId], references: [id])
    correctorProfileId String?           @unique

    coordinator_profile  CoordinatorProfile? @relation(fields: [coordinatorProfileId], references: [id])
    coordinatorProfileId String?             @unique

    teacher_profile  TeacherProfile? @relation(fields: [teacherProfileId], references: [id])
    teacherProfileId String?         @unique

    roles Role[]

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
  }

  enum Role {
    Estudante
    Administrador
    Professor
    Corrector
    Coordenador
  }

CodePudding user response：

You can extract profile data which is common between user types in a separate table for instance profiles, and create separate tables for each user type, teachers, administrators ...
For extracting to work you need to:

don't reference sub types in root, reference root type in sub types instead
separate concerns, so users table is just for access management purposes and profiles table is just for data storage and retrieval

model Profile {
    id String @id @default(uuid())

    name String @db.VarChar(255)

    image   Image?  @relation(fields: [imageId], references: [id])
    imageId String? @unique

    school School?
    user   User?

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
}

model Coordinator {
    id String @id @default(uuid())
    profile Profile
}

model Teacher {
    id String @id @default(uuid())
    team Team[]
    profile Profile
}
model Student {
    id String @id @default(uuid())
    schoolId String @unique

    team   Team?   @relation(fields: [teamId], references: [id])
    teamId String?
    text Text[]
    profile Profile
}
  model User {
    id String @id @default(uuid())

    email        String   @unique @db.VarChar(255)
    password     String   @db.VarChar(255)
    access_until DateTime @db.Timestamp

    createdAt DateTime @default(now()) @db.Timestamp
    updatedAt DateTime @updatedAt @db.Timestamp
    deleted   Boolean  @default(false) @db.Boolean
  }

But...
In the end it would not help if you don't consider what behaviors are needed in the software you're gonna build to use it, and what kind of queries you will need.
for example if you want to be able to query all user types in a single query, that might be a sign that you want to also manipulate those data, which is very hard to do in the design specified above.