Guide To Design Database For Employee Management System In MySQL

This tutorial provides the complete steps to design a database schema of the Employee Management System to manage the users, roles, permissions, organizations, and employees.

The Entity Relationship Diagram or visual database design is shown below.

You can also visit the popular tutorials including How To Install MySQL 8 on Ubuntu 20.04 LTS, How To Install MySQL 8 on Windows, How To Install MySQL Workbench On Ubuntu, How To Install MySQL 8 With Workbench On Windows 10, RBAC Database in MySql, Blog Database in MySql, Quiz Database in MySQL, Poll & Survey Database in MySQL, Online Shopping Cart Database in MySQL, Inventory Database in MySQL, and Learn Basic SQL Queries In MySQL.

Notes: You can also follow the Custom Database schema of the Employee Management System to get customized solutions based on your requirements.

Organization Database

The very first step is to create the Organization Database. It can be created using the query as shown below.

CREATE SCHEMA `organization` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have used the character set utf8mb4 to support a wide range of characters.

Role Table

In this section, we will design the Role Table to store the system and organization roles. The column type can be used to identify whether the role is for the application users or organization employees. Below mentioned is the description of all the columns of the Role Table.

Id	The unique id to identify the role.
Title	The role title.
Slug	The unique slug to search the role.
Description	The description to mention the role.
Type	The role type to distinguish among system and organization roles.
Active	The flag to check whether the role is currently active.
Created At	It stores the date and time at which the role is created.
Updated At	It stores the date and time at which the role is updated.
Content	The complete details about the role.

The Role Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`role` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(75) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TINYTEXT NULL,
  `type` SMALLINT NOT NULL DEFAULT 0,
  `active` TINYINT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC) );

Permission Table

In this section, we will design the Permission Table to store the system and organization permissions. The column type can be used to identify whether the permission is for the application users or organization employees. Below mentioned is the description of all the columns of the Permission Table.

Id	The unique id to identify the permission.
Title	The permission title.
Slug	The unique slug to search the permission.
Description	The description to mention the permission.
Type	The permission type to distinguish among system and organization permissions.
Active	The flag to check whether the permission is currently active.
Created At	It stores the date and time at which the permission is created.
Updated At	It stores the date and time at which the permission is updated.
Content	The complete details about the permission.

The Permission Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`permission` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(75) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TINYTEXT NULL,
  `type` SMALLINT NOT NULL DEFAULT 0,
  `active` TINYINT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC) );

Role Permission Table

The Role Permission Table can be used to store the mappings of the permissions to the roles. Below mentioned is the description of all the columns of the Role Permission Table.

Role Id	The role id to identify the role.
Permission Id	The permission id to identify the permission.
Created At	It stores the date and time at which the mapping is created.
Updated At	It stores the date and time at which the mapping is updated.

The Role Permission Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`role_permission` (
  `roleId` BIGINT NOT NULL,
  `permissionId` BIGINT NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL,
  PRIMARY KEY (`roleId`, `permissionId`),
  INDEX `idx_rp_role` (`roleId` ASC),
  INDEX `idx_rp_permission` (`permissionId` ASC),
  CONSTRAINT `fk_rp_role`
    FOREIGN KEY (`roleId`)
    REFERENCES `organization`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rp_permission`
    FOREIGN KEY (`permissionId`)
    REFERENCES `organization`.`permission` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

User Table

In this section, we will design the User Table to store user information. Users can manage their own profiles. Also, the users can use the application according to the system roles assigned to them. Below mentioned is the description of all the columns of the User Table.

Id	The unique id to identify the user.
Role Id	The role of the user. It can be Admin or User.
First Name	The first name of the user.
Middle Name	The middle name of the user.
Last Name	The last name of the user.
Mobile	The mobile number of the user. It can be used for login and registration purposes.
Email	The email of the user. It can be used for login and registration purposes.
Password Hash	The password hash generated by the appropriate algorithm. We must avoid storing plain or encrypted passwords.
Registered At	This column can be used to calculate the life of the user with the application.
Last Login	It can be used to identify the last login of the user.
Intro	The brief introduction of the User.
Profile	User details.

The User Table with the appropriate constraints is shown below.

CREATE TABLE `organization`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `roleId` BIGINT NOT NULL,
  `firstName` VARCHAR(50) NULL DEFAULT NULL,
  `middleName` VARCHAR(50) NULL DEFAULT NULL,
  `lastName` VARCHAR(50) NULL DEFAULT NULL,
  `username` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
  `passwordHash` VARCHAR(32) NOT NULL,
  `registeredAt` DATETIME NOT NULL,
  `lastLogin` DATETIME NULL DEFAULT NULL,
  `intro` TINYTEXT NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_username` (`username` ASC),
  UNIQUE INDEX `uq_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC),
  INDEX `idx_user_role` (`roleId` ASC),
  CONSTRAINT `fk_user_role`
    FOREIGN KEY (`roleId`)
    REFERENCES `organization`.`role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Organization Table

In this section, we will design the Organization Table to store the organization data. Below mentioned is the description of all the columns of the Organization Table.

Id	The unique id to identify the organization.
Created By	The user id to identify the user who registered the organization.
Updated By	The user id to identify the user who updated the organization.
Title	The organization title.
Meta Title	The meta title to be used for browser title and SEO purposes.
Slug	The slug to form the unique URL.
Summary	The summary to mention the key highlights.
Status	The status of the organization can be New, Approved, Active, or Blocked.
Created At	It stores the date and time at which the organization is created.
Updated At	It stores the date and time at which the organization is updated.
Profile	The column used to store the profile details of the organization.
Content	The column used to store the additional details of the organization.

It uses the column status to track the status of the organization. The status can be either New, Approved, Active, or Blocked. The Organization Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`organization` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `createdBy` BIGINT NOT NULL,
  `updatedBy` BIGINT NOT NULL,
  `title` VARCHAR(75) NOT NULL,
  `metaTitle` VARCHAR(100) NULL,
  `slug` VARCHAR(100) NOT NULL,
  `summary` TINYTEXT NULL,
  `status` SMALLINT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `profile` TEXT NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uq_slug` (`slug` ASC),
  INDEX `idx_organization_creator` (`createdBy` ASC),
  CONSTRAINT `fk_organization_creator`
    FOREIGN KEY (`createdBy`)
    REFERENCES `organization`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `organization`.`organization` 
ADD INDEX `idx_organization_modifier` (`updatedBy` ASC);
ALTER TABLE `organization`.`organization` 
ADD CONSTRAINT `fk_organization_modifier`
  FOREIGN KEY (`updatedBy`)
  REFERENCES `organization`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Organization Meta

The Organization Meta Table can be used to store additional information about organizations including the organization banner URL etc. Below mentioned is the description of all the columns of the Organization Meta Table.

Id	The unique id to identify the organization meta.
Organization Id	The organization id to identify the parent organization.
Key	The key identifying the meta.
Content	The column used to store the organization's metadata.

The Organization Meta Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`organization_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `organizationId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_organization` (`organizationId` ASC),
  UNIQUE INDEX `uq_meta_organization` (`organizationId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_organization`
    FOREIGN KEY (`organizationId`)
    REFERENCES `organization`.`organization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Employee Table

In this section, we will design the Employee Table to store the employee data. Below mentioned is the description of all the columns of the Employee Table.

Id	The unique id to identify the employee.
User Id	The user id to identify the user associated with the Employee.
Role Id	The organization-specific role id assigned to the Employee.
Created By	The user id to identify the user who added the employee.
Updated By	The user id to identify the user who updated the employee.
Code	The code used by the organization to identify the employee.
Status	The status of the employee can be New, Approved, Active, Blocked, or Terminated.
Created At	It stores the date and time at which the employee is created.
Updated At	It stores the date and time at which the employee is updated.
Starts At	It stores the date and time at which the employment starts.
Ends At	It stores the date and time at which the employment ends.
Notes	The column used to store the notes specific to employment.

It uses the column status to track the status of the employee. The status can be either New, Approved, Active, Blocked, or Terminated. The Employee Table with the appropriate constraints is as shown below.

CREATE TABLE `organization`.`employee` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `organizationId` BIGINT NOT NULL,
  `userId` BIGINT NOT NULL,
  `roleId` BIGINT NOT NULL,
  `createdBy` BIGINT NOT NULL,
  `updatedBy` BIGINT NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `status` SMALLINT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `startsAt` DATETIME NULL DEFAULT NULL,
  `endsAt` DATETIME NULL DEFAULT NULL,
  `notes` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_employee_user` (`userId` ASC),
  CONSTRAINT `fk_employee_user`
    FOREIGN KEY (`userId`)
    REFERENCES `organization`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `organization`.`employee` 
ADD INDEX `idx_employee_organization` (`organizationId` ASC);
ALTER TABLE `organization`.`employee` 
ADD CONSTRAINT `fk_employee_organization`
  FOREIGN KEY (`organizationId`)
  REFERENCES `organization`.`organization` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `organization`.`employee` 
ADD INDEX `idx_employee_role` (`roleId` ASC);
ALTER TABLE `organization`.`employee` 
ADD CONSTRAINT `fk_employee_role`
  FOREIGN KEY (`roleId`)
  REFERENCES `organization`.`role` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `organization`.`employee` 
ADD INDEX `idx_employee_creator` (`createdBy` ASC);
ALTER TABLE `organization`.`employee` 
ADD CONSTRAINT `fk_employee_creator`
  FOREIGN KEY (`createdBy`)
  REFERENCES `organization`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `organization`.`employee` 
ADD INDEX `idx_employee_modifier` (`updatedBy` ASC);
ALTER TABLE `organization`.`employee` 
ADD CONSTRAINT `fk_employee_modifier`
  FOREIGN KEY (`updatedBy`)
  REFERENCES `organization`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Summary

In this tutorial, we have discussed the database design of an Employee Management System to store the roles, permissions, users, organizations, and manage organization employees.

You may submit your comments to join the discussion. You may also be interested in designing the database of the Blog, Shopping Cart, and Poll & Survey applications. The complete database schema is also available on GitHub.