rednl2
Pay-per-click Pro
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
400 XP
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!