Employee Leave System ERD (With Database Structure)

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

 

 

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.

 

 

 

PART 1) 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	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

COLUMN	DATA TYPE	NOTES
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

COLUMN	DATA TYPE	NOTES
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

COLUMN	DATA TYPE	NOTES
leave_id	INTEGER	Primary key.
staff_id	INTEGER	Foreign key. Staff taking leave.
leave_type	VARCHAR	Foreign key. Type of leave.
leave_start leave_end	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

COLUMN	DATA TYPE	NOTES
leave_id	INTEGER	Primary and foreign key.
leave_date	DATE	Primary 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

3A) HOLIDAYS

COLUMN	DATA TYPE	NOTES
holiday_date	DATE	Primary key.
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”

COLUMN	DATA TYPE	NOTES
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.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• Simple Leave Management System With PHP MySQL – Code Boxx