Database Design for Payroll Management System

Payroll ERD

This is a Database Design for a Simple Payroll Management System. This ERD (Entity Relationship Diagram) is the one I used for developing Payroll Management System using PHP/MySQL. The payroll system allows the company management to manage and calculates the payslip of their employees. With this ERD or Database Design of Payroll System, management can calculate the employees' payslip that includes their allowances and deductions. It also includes an Attendance Table which will be used in computing the total days of present, days of absences, minutes of tardy/undertime of each employee between the cut-off days.

ERD Payroll

The Employee Payroll Management System Database consists of 11 tables. It has the Department, Position, Allowances, Deductions, Allowances, Attendance, Employee, Employee Allowances, Employee Deductions, Payroll, Payroll Items/Payslip, and Users Tables.

The Department Table stores all the list of departments in the company. This table is connected to both the Position and Employee tables. The data stored in this table helps to identify the certain departments of the employees.

Department Table

Field Name Description Type Length
id (PK) Department ID int 11
name Department Name TEXT  

The Position Table stores all the Employees' Positions in the company. Each of the positions is connected to the department which means the position is only available for a certain Department.

Position Table

Field Name Description Type Length
id (PK) Position ID int 11
department_id (FK) Department ID int 11

The Allowances Table stores all the allowances available for the company's employees.

Allowances Table

Field Name Description Type Length
id (PK) Allowance ID int 11
allowance Allowance Name Text  
description Allowance's Description Text  

The Deductions Table stores all the allowances available for the company's employees.

Deductions Table

Field Name Description Type Length
id (PK) Deduction ID int 11
deduction Deduction Name Text  
description Deduction's Description Text  

The Employee Table stores the list of the employees of the company. This contains fields about the basic personal information and employee's details to the company.

Employee Table

Field Name Description Type Length
id (PK) Employee ID int 11
employee_code Employee's Company ID Code varchar 100
firstname Employee's First Name varchar 250
middlename Employee's Middle Name varchar 250
lastname Employee's Last Name varchar 250
department_id (FK) Department ID int 11
position_id (FK) Position ID int 11
salary Employee's Monthly Salary double  

The Employee Allowances Table store the list of allowances of each employee. Employees' Allowances might be different to others and others might not have. This table contains a field that helps the system to identify when (Monthly, Semi-Monthly) the system will add the other earnings of the employee.

Employee Allowances Table

Field Name Description Type Length
id (PK) Employee Allowance ID int 11
employee_id (FK) Employee ID int 11
allowance_id (FK) Allowance ID int 11
type Payroll Type to Add the Allowance (1 = Monthly, 2 = Semi-Monthly, 3 = once) tinyint 1
amount Allowance Amount double  
effective_date The date when the allowance will be added to the payslip date  
date_created Date/Time of the data inserted DateTIme  

The Employee Deductions Table store the list of deductions of each employee. Like the Allowance Table, Employees Deduction might be different from others and others might not have. This table contains a field that helps the system to identify if when (Monthly, Semi-Monthly, or once) the system will deduct the amount to the certain employee.

Employee Allowances Table

Field Name Description Type Length
id (PK) Employee Allowance ID int 11
employee_id (FK) Employee ID int 11
deduction_id (FK) Deduction ID int 11
type Payroll Type to deduct the Deduction (1 = Monthly, 2 = Semi-Monthly, 3 = once) tinyint 1
amount Deduction Amount double  
effective_date The date when the deduction will be added to the payslip date  
date_created Date/Time of the data inserted DateTIme  

The Attendance Table stores all the attendances of the employees. Each employee will have 4 data per day in this table. This data could be the employees for Time-in, Lunch-out, After Lunch-in, and Time-out. This also contains a DateTime log that will be used in calculating the rendered working hours of the employees each working day.

Attendance Table

Field Name Description Type Length
id (PK) Attendance ID int 11
employee_id (FK) Employee ID int 11
log_type Attendance Log Type (1 = Time IN, 2 = Lunch Out, 3 = After Lunch In, 4 = Time Out) tinyint 1
datetime_log Log's Date and Time Date  

The Payroll Table stores the payroll cut-offs of the company. The system relies on this table on how to compute the employees' payslips. The system will also fetch the employees' attendances between the cut-off's date range.

Payroll Table

Field Name Description Type Length
id (PK) Payroll ID int 11
ref_no Payroll Reference No. varchar 100
date_from Payroll Cut-off start date Date  
date_to Payroll Cut-off end date Date  
type Payroll Type (1 = Monthly, 2 = Semi-Monthly) tinyint 1
status Payroll Type (1 = New, 2 = Computed) tinyint 1
date_created Date/Time when the data has been inserted DateTime  

The Payroll Item/Payslip Table stores the payslip details of each employee for a certain payroll. This table contains the number of presents, absences, late of the employee. The Allowances and Deductions JSON Data of employees is also stored in this table.

Payslip Table

Field Name Description Type Length
id (PK) Payslip ID int 11
payroll_id (FK) Payroll ID int 11
employee_id (FK) Employee ID int 11
present Employee's days of present tinyint 3
absent Employee's days of absences tinyint 3
salary Base Salary Amount of the Employee for the certain Cut-off double  
allowance_amount Total Amount of the Employee's Allowance double  
allowances Employee's Allowance JSON Data TEXT  
deduction_amount Total Amount of the Employee's Deduction double  
deductions Employee's deduction JSON Data TEXT  
net Employee's NET Income double  
date_created Date/Time when the data has been inserted DateTime  

Lastly, the Users Table. This table doesn't have any relation with the other tables above. This table only stores the system users' credentials.

Users Table

Field Name Description Type Length
id (PK) User ID int 11
name User Name TEXT  
username Username TEXT  
password User Password TEXT  
type User Type (1 = Administrator, 2 = Staff) tinyint 1

That's it! You can use this Database Design for your Payroll System Project as a reference or use it to begin with to develop your own Employees Payroll System Application.

To check how this Payroll System Database Design works, you can click the link below to redirect to the actual project source code of a Payroll System that uses this ERD:

Payroll Management System using PHP and MySQL Database Source Code

I hope this will you with what you are looking for.

Thanks!

Comments

Is good

good article

Add new comment