attendance
Table: attendance
The attendance table is designed to track employee attendance, including check-in and check-out times, locations, statuses, and metadata for audit and reporting.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| mappedId | int(11) | NOT NULL | Links to a user or entity in the idmapper table. |
| workgrouptypeId | int(11) | NOT NULL | Links to the workgrouptype table, defining the workgroup type. |
| checkIn | time | Time of check-in. | |
| checkOut | time | Time of check-out. | |
| checkInLatitude | varchar(45) | Latitude of check-in location. | |
| checkInLongitude | varchar(45) | Longitude of check-in location. | |
| geopoint | varchar(45) | Combined geolocation data as a single point. | |
| InStatus | varchar(45) | Status of check-in (e.g., on-time, late). | |
| location | int(11) | Links to the location table. | |
| assignedCheckIn | time | Scheduled check-in time. | |
| assignedCheckOut | time | Scheduled check-out time. | |
| createdByUserId | int(11) | User who created the record (foreign key). | |
| editedByUserId | int(11) | User who last edited the record (foreign key). | |
| laxTime | varchar(45) | Grace period allowed for check-in/out. | |
| attendanceId | int(11) | NOT NULL AUTO_INCREMENT | Unique identifier for each attendance record. |
| createdDate | datetime | Date the record was created. | |
| description | varchar(255) | Additional notes about the attendance record. | |
| lastEditedDate | datetime | Date the record was last modified. | |
| OutStatus | varchar(45) | Status of check-out (e.g., early, late). | |
| attendanceDate | date | Date of attendance. | |
| checkOutLatitude | varchar(45) | Latitude of check-out location. | |
| checkOutLongitude | varchar(45) | Longitude of check-out location. | |
| latitude | varchar(30) | General latitude coordinate. | |
| longitude | varchar(30) | General longitude coordinate. | |
| isBiometric | tinyint(1) | Indicates if attendance was recorded using biometrics. | |
| shiftStatus | varchar(20) | Status of the shift (e.g., completed, ongoing). |
Indexes
- Primary Index
- attendanceId: Ensures unique identification for each record.
- Foreign Key Indexes
- workgrouptype_fk_idx: Links workgrouptypeId to the workgrouptype table.
- userIdMapper_pk_fk: Links mappedId to the idmapper table.
- createdByuser_fk_idx: Links createdByUserId to the user table.
- editedByuser_fk_idx: Links editedByUserId to the user table.
- location_fk_idx and assignedLocation_fk_idx: Link location to the location table.
- Additional Indexes
- FK7117E2E946A840E5: Supports performance optimization for primary key lookups.
Foreign Key Relations
- attendance_location_fk
- Links location to the location table, representing the associated physical location.
- createdByuser_fk and editedByuser_fk
- Link createdByUserId and editedByUserId to the user table (mappedId), enabling tracking of record creation and updates.
- userIdMapper_pk_fk
- Links mappedId to the idmapper table for user identification.
- workgroupId_fk
- Links workgrouptypeId to the workgrouptype table, identifying the associated workgroup type.
Usage Notes
- Attendance Tracking: Supports detailed tracking of employee attendance with timestamps and geolocation.
- Audit Trail: Metadata fields like createdByUserId, editedByUserId, createdDate, and lastEditedDate ensure auditability.
- Shift Compliance: Includes fields like assignedCheckIn, assignedCheckOut, and laxTime for managing shift schedules.
- Geolocation Support: Provides detailed location data for check-in and check-out events.