Employee Payroll System ERD (With Database Structure)

Welcome to a sharing of an ERD for a simple employee payroll system. So you are working on a project to process the staff payroll? Or maybe got this one as a school project? Well, here is a sharing of my own simple version – Read on!

 

 

TABLE OF CONTENTS

 

 

EMPLOYEE PAYROLL SYSTEM ERD

As in the introduction above, kept as simple as possible.

• Staff & Salary – Staff members and their salary.
• Payroll – Payment and details.
• Attendance – To keep track of staff “check in” and “check out”.

Yep, don’t be fooled though. Quite some thought has gone into these few tables, will explain more below.

 

FOR REFERENCE ONLY

I can hear angry experts banging on keyboards – “Too simple”, “useless”, “inaccurate”. By any means, this is only a simple example for your reference.

• Leave management is not included, as this is a “payroll system” and not a “leave system”. Although you can deduct unpaid leave in “payment details”.
• Staff members can have multiple “types of salaries” – Monthly, weekly, ad-hoc, or maybe even basic plus hourly. If that is not the case, change it on your own.
• Don’t see a point in adding another “department” table. Go ahead and add it to your own project if it makes sense.
• If you allow staff members to sign in and check their own payroll, go ahead and add a “password”.

Yep, you are building your own system now. You decide what makes sense and set your own rules.

 

 

 

PART 1) STAFF & SALARY

1A) STAFF

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_name	VARCHAR	Full name.
staff_email	VARCHAR	Email. Set this to unique to prevent duplicate registrations.
staff_tel	VARCHAR	Contact number.
staff_title	VARCHAR	Manager, HOD, receptionist, IT support, HR manager, accountant, etc…
staff_department	VARCHAR	HR, IT, Finance, service, etc…

This one should be self-explanatory, go ahead and add more fields as required in your own project. Maybe address and password.

 

1B) SALARY

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary and foreign key.
salary_type	VARCHAR	Primary key. Up to you to decide – Monthly, weekly, hourly, basic, ad-hoc, etc…
salary_amount	DECIMAL	The agreed salary amount.
salary_start salary_end	DATE TIME	Employment period. For example, contract staff will have start and end dates, but full-timers will have a NULL end date.

Most “other tutorials” I see online assume that all staff members are taking a monthly salary. There is a good reason why salary is a separate table, because “all monthly salary” is not realistic.

• Part-timers can be on a weekly or bi-weekly payroll.
• Temp staff can be paid at an hourly rate.
• Some can have a basic monthly plus hourly rate.
• Freelancers can be paid on an ad-hoc project basis.
• Overtime can be yet another different rate.

Yep, a separate salary table can better handle all of these different situations.

 

 

PART 2) PAYROLL

2A) PAYMENT

COLUMN	DATA TYPE	NOTES
pay_id	INTEGER	Primary key.
staff_id	INTEGER	Foreign key, staff being paid.
pay_start pay_end	DATE	Payment for this period.
pay_mode	VARCHAR	Up to you to decide – Cash, bank transfer, cheque, etc…
pay_total	DECIMAL	The total amount paid to the staff.

To address the elephant in the room, how much is paid to the staff for which period?

 

2B) PAYMENT DETAILS

COLUMN	DATA TYPE	NOTES
detail_id	INTEGER	Primary key.
pay_id	INTEGER	Foreign key.
pay_type	VARCHAR	Up to you to decide, can be the foreign key for “salary type” – Monthly/weekly. Can also be things like “commission” and “bonus”.
pay_amount	DECIMAL	Amount paid. Can be a negative amount for unpaid leave.
pay_notes	VARCHAR	Notes, if any. For example, 3 months bonus, or leave from X to Y.

• Payment – A summary.
• Payment Details – The exact calculations.

That’s right, this one is used to record the exact payment calculations. For example, someone with a basic pay plus an hourly rate can have:

• Basic – $A
• Hourly – $B
• Overtime – $C
• Commissions – $D
• Bonus – $E

 

 

PART 3) ATTENDANCE

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary and foreign key.
attend_start	DATE TIME	Primary key, “check-in” timestamp.
attend_end	DATE TIME	The “check out” timestamp.

Finally, this is not an “attendance system”, but this is here for a good reason – A payroll system should also calculate the work hours for staff members who are on an hourly rate.

So “by right”, attendance should be linked to the payment details. But there are no foreign keys linking these together, we are merely calculating the total hours from the attendance – Just so you know and take extra note.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart