tbl_employeeimmigrationhistory

-1 rows


Description

Module: Employee Profile
Purpose: Maintains historical records of employee passport and visa details.
Data: Stores changes or new entries made by employees before HR/Admin approval.
Process Usage:
- When an employee adds or modifies passport or visa information, the record is stored here first.
- HR/Admin reviews the record and updates the main table (tbl_employeeimmigration) upon approval.
Key Points:
- Supports an approval workflow for immigration data.
- Preserves a history of all employee-submitted immigration details.
- Includes approval status and rejection reasons for audit tracking.
Business Impact: Ensures that only verified passport and visa details are reflected in official HR records, improving compliance and data integrity.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_employeeimmigrationhistory_id_seq'::regclass)

Primary key
Format: BIGSERIAL (auto-increment)
Used as: Unique identifier for each immigration history record.
eimmigrationid int8 19 √ null
tbl_employeeimmigration.eimmigrationid fk__tbl_emplo__eimmi__3002bfd0 R

Reference ID
Format: BIGINT
Meaning: Foreign key reference to tbl_employeeimmigration.eimmigrationid.
Usage: Links the history record to the corresponding main immigration record.
employeeid varchar 20 null
tbl_employee_previous_pf.employeeid Implied Constraint R

Employee ID
Format: VARCHAR(20)
Meaning: Identifies the employee who owns this immigration record.
Usage: Links the immigration history record to a specific employee.
eitype varchar 40 √ null

Document type
Format: VARCHAR(40)
Meaning: Type of immigration document submitted by the employee.
Possible Values: Passport, Visa.
Usage: Identifies what type of document is being reviewed.
einumber varchar 20 √ null

Document number
Format: VARCHAR(20)
Meaning: Passport or visa number provided by the employee.
Usage: Acts as a key identifier for the document in immigration records.
eicitizenship varchar 60 √ null

Citizenship
Format: VARCHAR(60)
Meaning: Country of citizenship for the employee associated with this document.
Usage: Used to identify nationality in immigration tracking.
eiissuedate timestamp 29,6 √ null

Issue date
Format: TIMESTAMP
Meaning: Date when the passport or visa was issued.
Usage: Helps determine document validity period.
eiexpirydate timestamp 29,6 √ null

Expiry date
Format: TIMESTAMP
Meaning: Date when the passport or visa will expire.
Usage: Used for compliance tracking and renewal alerts.
eicomments varchar 200 √ null

Additional comments
Format: VARCHAR(200)
Meaning: Optional notes related to the passport or visa (added by employee or HR).
Usage: Can include internal remarks or document-specific notes.
status varchar 20 √ null

Record status
Format: VARCHAR(20)
Possible Values: Approved, Rejected, Pending.
Meaning: Indicates whether this immigration history record has been approved by HR/Admin.
Usage: Determines if the record should be reflected in the main immigration table.
rejectreason varchar 200 √ null

Rejection reason
Format: VARCHAR(200)
Meaning: Stores remarks or reasons provided by HR/Admin when rejecting a record.
Usage: Useful for feedback and audit tracking of rejected entries.
createddate timestamp 29,6 √ null
createdby varchar 50 √ null
modifieddate timestamp 29,6 √ null
modifiedby varchar 50 √ null

Indexes

Constraint Name Type Sort Column(s)
tbl_employeeimmigrationhistory_pk_tbl_employeeimmigrationhistor Primary key Asc id

Relationships

Close relationships within degrees of separation