leavemanagement
Table: leavemanagement
The leavemanagement table records employee leave information.
It tracks leave types, dates, approval details, and audit information for each staff member.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| mappedId | int(11) | NOT NULL | ID of the employee taking leave (references idmapper.mappedId) |
| leaveType | varchar(45) | NOT NULL | Type of leave (e.g., Sick, Casual, Annual) |
| leaveDate | date | NOT NULL | Date of the leave |
| createdByUserId | int(11) | NULL | ID of the user who created the leave record (references user.mappedId) |
| lastEditedByUserId | int(11) | NULL | ID of the user who last edited the leave record (references user.mappedId) |
| leaveId | int(11) | NOT NULL, AUTO_INCREMENT | Primary identifier for the leave record |
| voided | int(11) | DEFAULT 0 | Flag indicating if the leave record is voided (0 = active, 1 = voided) |
| additionalNote | varchar(255) | NULL | Any additional notes for the leave |
| approvedByName | varchar(255) | NULL | Name of the approver for the leave |
| approvedByRole | varchar(255) | NULL | Role/designation of the approver |
| leaveReason | varchar(255) | NULL | Reason provided for the leave |
| createdDate | datetime | NULL | Timestamp when the leave record was created |
| lastEditedDate | datetime | NULL | Timestamp when the leave record was last edited |
| voideDate | datetime | NULL | Timestamp when the record was voided |
| voidedReason | varchar(255) | NULL | Reason for voiding the leave record |
| shiftStatus | varchar(45) | NULL | Status of the shift during the leave (if applicable) |
Indexes
- PRIMARY - Implicit via
leaveId(auto-incremented) - UNIQUE -
YourTable_uniqueon (mappedId,leaveDate) - createdUserId_fk_idx - Index on
createdByUserId - editedUserId_fk_idx - Index on
lastEditedByUserId - FK438A0E3ABF05F133 - Index on
leaveId
Foreign Key Relations
-
createdUserId_fk→user.mappedId- Links the record to the user who created it
-
editedUserId_fk→user.mappedId- Links the record to the user who last edited it
-
mappedIdIdMapper_pk_fk→idmapper.mappedId- Links the leave record to the employee in the ID mapping table
Usage Notes
- Tracks leave requests for employees including type, reason, approval, and shift impact.
voidedandvoidedReasonhelp manage cancelled or corrected leave entries.- Unique constraint on
mappedIdandleaveDateprevents duplicate leave records for the same employee on the same day. - Indexed for efficient retrieval by creator, editor, or leave record ID.