Here is the database schema.

model User {
  id            String       @id @default(cuid())
  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt
  firstName     String?
  lastName      String?
  email         String?      @unique
  password      String?
  emailVerified DateTime?
  photo         String?
  phoneNumber   Int?
  birthOfDate   DateTime?
  role          String?      @default("guest")
  address       String?
  instructor    Instructor[]
  student       Student[]
  accounts      Account[]
  sessions      Session[]
  employee      Employee[]
}

model Department {
  id         String       @id @unique @default(cuid())
  name       String?      @unique
  employees  Employee[]
}

model Employee {
  id           String       @id @unique @default(cuid())
  user         User?        @relation(fields: [userId], references: [id])
  department   Department?  @relation(fields: [departmentId], references: [id])
  jobTitle     String?
  hireDate     DateTime?
  userId       String?
  departmentId String?
  instructor   Instructor[]

  @@index([userId])
  @@index([departmentId])
}

model Instructor {
  id           String      @id @unique @default(cuid())
  user         User?       @relation(fields: [userId], references: [id])
  employee     Employee?   @relation(fields: [employeeId], references: [id])
  groups       group[]
  userId       String?
  employeeId   String?
  @@index([userId])
  @@index([employeeId])
}

model Student {
  id             String      @id @unique @default(cuid())
  user           User?       @relation(fields: [userId], references: [id])
  enrollmentID   String?     @unique
  enrollmentDate DateTime?
  groups         group[]
  userId         String?

  @@index([userId])
}

model group {
  id          String       @id @unique @default(cuid())
  name        String?      @unique
  startDate   DateTime?
  instructors Instructor[]
  students    Student[]
}

I'm using Prisma and MySQL to create a database schema for an education center that has different user roles (Instructor, Student, Employee) based on User model. 

I'm creating a dashboard for a education center that can anyone sign up and access to that dashboard.

I want to create User model that contains all user information when a user registrate.

When Admin authorize certain users for example as Employee, Instructor or Student these models should have connection with User model and receive user information.

Each user belongs to different Departments and Groups as shown Department and Group model.

Is my solution correct for that purpose.

CodePudding user response：

In your example there is lacking

model Account {
  id     String  @id @default(cuid())
  User   User?   @relation(fields: [userId], references: [id])
  userId String?
}

model Session {
  id     String  @id @default(cuid())
  User   User?   @relation(fields: [userId], references: [id])
  userId String?
}

After adding them you have the following schema

So let's check your requirements one by one:

1. Different roles

different user roles (Instructor, Student, Employee) based on User model

In your design there are two sources of truth:

• relations between tables with users and roles (Instructor, Student, Employee)
• role column in User table

You can consider this choice as correct, but have to pay special attention to operations on this database to avoid situations where one user has more than two roles or do not have any role.

If you want more elasticity and allow for users without any role or with many roles, then column role should be removed or replaced by array or json.

You can read more about json type here https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#array_contains or try use scalar lists but there is problem with mysql so json will be better https://www.prisma.io/docs/guides/upgrade-guides/upgrade-from-prisma-1/schema-incompatibilities-mysql

Finally I would rethink if user can have 0 or many roles. If no, then this schema is acceptable but should be used with high patience to avoid data inconsistency due lacking single source of truth. If user can not be assigned to any role or many roles can be granted to single user then I recommend removing role column or replacing it by roles with type json.

Authorization flow

can anyone sign up and access to that dashboard.

You have all required data:

• email
• password
• emailVerified

I do not know why do you need account table and it is not required (sometimes not recommended) to use database as session storage, but generally you filling this requirement.

User model with all data

User model that contains all user information when a user register.

Yes it is satisfied.

Admin authorization

When Admin authorize certain users...

We have to assume, that there is "authorization process" so there are two options:

• during some period of time users waiting for authorization to given role
• admin have list of users and authorizing them simply assign them roles

First scenario seems to be useful but is impossible in this model because there is no possibility of saving any intermediate state like: "user want given role but wait for authorization from admin". I think that you can consider this scenario because in real world user know on which role he want to register his account, so saving this can probably simplify admin work.

Departments and groups

Each user belongs to different Departments and Groups as shown Department and Group model.

I see problem there.

Firstly You can't write that each user belong to different Departments.

• if User have connection to Employee that means that admin authorized them, but before authorization this connection could not exists
• if User is not connected to Employee then can't be assigned to any Department
• if User is only Student but no Employee he can't be assigned to department. His potential assignment to department is dependent from relations between Student and Group and Instructor and Group.

Secondly not any user belongs to Group.

So if you will change your requirements from:

Each user belongs to different Departments and Groups as shown Department and Group model.

to

Each user can belongs to different Departments and Groups as shown Department and Group model.

everything will be ok.

General Note

Try to avoid naming tables using database keywords like Group. It is formally correct and you can have working program with these names but can be confusing and increase risk of problems during maintenance.

---

Answering to your question: I think that this model can be considered as correct but little overengineered and can be tune-up if you will think about authorization by admin as a process with intermediate states.