Welcome to a sharing of an ERD for a simple employee leave system. So you are working on a leave management system? How do we design the system structure? Well, here is a quick sharing of my version – Read on!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
EMPLOYEE LEAVE SYSTEM ERD
To keep this simple, I have grouped the entities into 3 groups:
- Staff – Well… The staff members.
- Leave – Leave granted and taken.
- Holidays and “Leave Template” – These don’t actually quite fit anywhere. Will explain more below.
FOR REFERENCE ONLY
I can hear angry experts banging on keyboards – “Dumb, worst, useless”, “That is not how it works”. By any means, this is only a simple example for your reference.
- If you want to add “staff departments”, go ahead and do so.
- All staff members are guaranteed to have some leave days, go ahead and change that if it is not the case.
- If you want to restrict leave approval by the department, go ahead and do your own changes.
- If you think that holidays must be “linked” to leave taken, go ahead and do it.
Yes, you are building your own system now. You decide what makes sense, you create 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
|staff_email||VARCHAR||Email. Set this to unique to prevent duplicate registrations.|
|staff_title||VARCHAR||Receptionist, IT support, HR manager, accountant, warehouse assistant, etc…|
|role_id||VARCHAR||Foreign key, staff role. Manager, executive, engineer, assistant, intern, etc…|
|staff_password||VARCHAR||Staff login password.|
To address the elephant in the room. Feel free to add more fields as required, maybe the address or even the contract period.
PART 2) LEAVE
2A) LEAVE TYPES
|leave_id||VARCHAR||Primary key, shortcode for the type of leave. E.G. “UPD” for “unpaid”, and “PD” for “paid”.|
|leave_name||VARCHAR||The full “leave name”. E.G. Paid, unpaid, medical, maternity, compassionate.|
To the students who have never worked before – Not all companies have the “same set of leave types”. For example:
- Some companies have “medical” and “hospitalization”, while others only have “medical”.
- A few cold-hearted companies don’t have “compassionate leave”, only “unpaid leave”.
- Some companies may also decide that “maternity” is “home-based hospitalization”.
Yep, it’s best to leave the options open with this table.
2B) LEAVE GRANTED
|staff_id||INTEGER||Primary and foreign key. Leave granted to this staff.|
|leave_type||VARCHAR||Primary and foreign key. Type of leave granted.|
|grant_period||INTEGER||Primary key. The year where the leave is valid.|
|grant_days||DECIMAL||The number of days granted. Take note this is decimal, to cater for half-day leaves.|
|grant_notes||VARCHAR||Notes, if any.|
|grant_by||INTEGER||Foreign key. The manager who granted this leave to the staff, if any.|
This should be pretty self-explanatory – How many days of leave are granted to the staff members, and for which year/period?
P.S. The system can automatically calculate the annual leave, and managers can manually grant more days – That is what the “grant by and notes” are for.
2C) LEAVE TAKEN
|staff_id||INTEGER||Foreign key. Staff taking leave.|
|leave_type||VARCHAR||Foreign key. Type of leave.|
|DATE TIME||Period of leave.|
|leave_days||DECIMAL||Total number of leave days.|
|leave_status||VARCHAR||Pending, approved, denied.|
|leave_notes||VARCHAR||Notes, if any.|
|approve_by||INTEGER||The manager who approved this leave.|
Another self-explanatory table. Which staff is taking leave for which period?
2D) LEAVE DAYS
|leave_id||INTEGER||Primary and foreign key.|
|leave_half||BOOLEAN||Full or half day.|
Now, let’s say that a staff member takes leave “from Monday to Sunday”. But many things can happen within the week:
- Mon : Holiday
- Tue : Comes back to the office in the morning for a quick meeting.
- Wed to Fri : Full day leave.
- Sat – Sun : Off day.
So in fact, this is only a 3.5 days leave for an entire week. The above table cannot record all the details, thus the need for this one.
PART 3) EXTRAS
|holiday_name||VARCHAR||Holiday name. New year, Christmas, etc…|
|holiday_half||BOOLEAN||Full or half day.|
I know, some people may think “holidays are linked to the leave taken table”. But no, technically, there are no shared keys between these two. Holidays are used to calculate the leave taken, but they are not directly linked. If you think that they should, go ahead and do so in your own project.
3B) “LEAVE TEMPLATE”
|role_id||VARCHAR||Primary and foreign key.|
|leave_id||VARCHAR||Primary and foreign key.|
|leave_days||DECIMALS||The number of days granted.|
Finally, this table is used by the system to grant leave automatically by the user role. Examples:
- All managers will have 12 days paid and 20 days unpaid leave every year.
- All executives will have 10 days paid and 16 days unpaid leave every year.
Some may argue that this should be linked to the leave granted table again, but it’s only used for calculations – There are no shared keys between these two.
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
- Simple Leave Management System With PHP MySQL – Code Boxx
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!