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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) STAFF & SALARY
|staff_email||VARCHAR||Email. Set this to unique to prevent duplicate registrations.|
|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.
|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.|
|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
|staff_id||INTEGER||Foreign key, staff being paid.|
|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
|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
|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.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!