Collecting Information towards my Degree in Management Program
Very much interest in doing my research on HR Management system within the department I'm working for...
Database Design for Payroll Management System
Submitted by oretnom23 on Tuesday, March 22, 2022 - 14:53.
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.
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!
- Add new comment
- 26386 views