Module Relationship Diagram (ERD) (Employee Management)
erDiagram
employees {
int id PK
string name
string code UK
string son_of
date dob
string cnic UK
enum gender
enum marital_status
enum blood_group
string religion
string image_path
boolean is_active
}
employee_employment {
int id PK
int employee_id FK
int branch_id FK
int department_id FK
int designation_id FK
date joining_date
date termination_date
decimal salary
int payment_account_id FK
int salary_expense_account_id FK
}
employee_contacts {
int id PK
int employee_id FK
string email UK
string mobile_number
text current_address
text permanent_address
string emergency_contact_name
string emergency_contact_relation
string emergency_contact_phone
}
employee_allowances_deductions {
int id PK
int employee_id FK
int allowance_deduction_id FK
decimal amount
text description
}
employee_academics {
int id PK
int employee_id FK
string degree_title
string institute_name
year passing_year
string grade_or_cgpa
}
employee_references {
int id PK
int employee_id FK
string reference_type "e.g., Family, Friend, Professional"
string name
string relation
string address
string designation
string phone
string email
}
employee_cheque_infos {
int id PK
int employee_id FK "Unique"
date transaction_date
string account_number
string cheque_number
string cheque_date
decimal amount
string cheque_location
text notes
}
employee_guarantors {
int id PK "701"
int employee_id FK "101"
string name "Javed Afridi"
string relation "Friend"
string cnic "17301-9876543-2"
string phone "0333-1122334"
string occupation "Businessman"
string city "Peshawar"
string residence_address "Phase 6, Hayatabad, Peshawar"
string residence_phone "091-7654321"
string since_living "2010"
string business_address "123 Business St, Peshawar"
string business_name "Afridi Enterprises"
string business_phone "091-1234567"
string since_working "2012"
}
employee_job_histories {
int id PK
int employee_id FK
string company_name
string designation
date start_date
date end_date
int salary
string reason_for_leaving
}
family_relations {
int employee_id PK, FK
int relative_id PK, FK
string relation
}
ALLOWANCE_DEDUCTION {
int id PK
string name
string type "e.g., Allowance, Deduction"
text description
}
employee_advances {
int id "51"
int employee_id FK "101"
int advance_type_id FK "1"
date request_date
decimal amount_requested "25000.00"
enum status "approved"
enum payment_mode "bank_transfer"
date payment_date
int approved_by FK "12"
}
advance_types {
int id "1"
string name "Salary Advance"
enum classification "temporary"
text description "Advance on upcoming salary"
boolean is_active "true"
}
employee_job_changes {
int id PK "902"
int employee_id FK "101"
enum change_type "Transfer, Promotion, Demotion"
date effective_date "2025-09-15"
int previous_branch_id FK "1"
int new_branch_id FK "2"
int approved_by FK "12"
}
employee_resignations {
int id PK "951"
int employee_id FK "101"
date notice_date "2025-09-01"
date resignation_date "2025-10-01"
string reason
text remarks
enum status "Approved, Pending, Rejected, Withdrawn"
}
employees ||--o{ employee_employment : "has employment"
employees ||--o{ employee_contacts : "has contacts"
employees ||--o{ employee_cheque_infos : "has cheque info"
employees }o--|| employee_allowances_deductions : "receives"
employees }o--|| employee_academics : "holds"
employees }o--|| employee_references : "provides"
employees }o--|| employee_job_histories : "has"
employees }o--|| employee_guarantors : "has"
employees }o--|| employee_job_changes : "history of"
employees }o--|| employee_resignations : "submits"
employees }o--|| employee_advances : "requests"
advance_types }o--|| employee_advances : "categorizes"
employees }o--|{ family_relations : "is defined in"
employees }o--|{ family_relations : "is related to"
employee_allowances_deductions }o--|{ ALLOWANCE_DEDUCTION : "is related to"
1. Employee Onboarding & Data Entry
flowchart TD
subgraph "New Employee Onboarding Process"
A_START((Start:
Hiring Approved)) --> A1["1. Create employees Record"]
A1 -- Name, CNIC, DOB, etc. --> A2["2. Create employee_contacts Record"]
A2 -- Addresses, Phone, Email --> A3["3. Create employee_employment Record"]
A3 -- Designation, Salary, Joining Date --> A4{Guarantor Required?}
A4 -- Yes --> A5["4a. Enter Guarantor Details"]
A5 -- Name, CNIC, Address --> A6["Create employee_guarantors Record"]
A6 --> A7{Security Cheque Provided?}
A7 -- Yes --> A8["4b. Enter Cheque Details"]
A8 -- Bank, Cheque #, Amount, Date --> A9["Create guarantor_cheques Record"]
A9 --> A10{Add Another Guarantor?}
A10 -- Yes --> A5
A10 -- No --> A11
A4 -- No --> A11
A11["5. Add Supporting Information"]
A11 --> A_ACADEMICS["employee_academics
Degrees, Institutes"]
A11 --> A_HISTORY["employee_job_histories
Previous Employers"]
A11 --> A_RELATIONS["family_relations
Link to existing employees"]
A_ACADEMICS & A_HISTORY & A_RELATIONS --> A12["6. Set employees.is_active = true"]
A12 --> A_END((End:
Profile is Active))
end
2. Employee Advance Request (pending two flows, Request and Approval flow)
flowchart TD
subgraph "Advance Request & Approval Flow"
B_START((Employee Requests Advance)) --> B1["1. Create employee_advances Record
Status: 'pending'"]
B1 --> B2["2. Request routed to Approver (Manager/HR)"]
B2 --> B3{Approval Decision}
B3 -- Approved --> B4["3a. Update Record
- status = 'approved'
- approved_by, approval_date"]
B4 --> B5["4. Finance processes payment"]
B5 -- Cash or Bank Transfer --> B6["5. Update Record
- status = 'paid'
- payment_mode, payment_date"]
B6 --> B_END((End:
Advance Paid))
B3 -- Rejected --> B10["3b. Update Record
- status = 'rejected'
- rejection_reason"]
B10 --> B_END_REJECT((End:
Request Rejected))
end
3. Job Change (Promotion, Demotion, Transfer)
flowchart TD
subgraph "Unified Job Change Process"
C_START((Start:
Management Decision)) --> C1["1. HR Initiates Job Change"]
C1 --> C2{Select Change Type}
C2 -- Promotion / Demotion --> C3a["2a. Create employee_job_changes Record
- change_type = 'promotion'/'demotion'
- Log previous & new designation/salary"]
C3a --> C4a["3a. Update employee_employment Record
with new designation & salary"]
C4a --> C5
C2 -- Transfer --> C3b["2b. Create employee_job_changes Record
- change_type = 'transfer'
- Log previous & new branch/department"]
C3b --> C4b["3b. Update employee_employment Record
with new branch & department"]
C4b --> C5
C5["4. Generate Official Letter (Promotion/Transfer Letter)"]
C5 --> C6["5. Notify Employee & Relevant Departments"]
C6 --> C_END((End:
Change is Effective))
end
4. Employee Resignation & Exit
flowchart TD
subgraph "Resignation & Exit Process"
D_START((Employee Submits Resignation)) --> D1["1. Create employee_resignations Record
Status: 'pending'"]
D1 --> D2["2. Notification sent to Manager"]
D2 --> D3{Manager Approves Resignation?}
D3 -- Yes --> D4["3a. Update Record
- status = 'approved'
- final_last_working_date"]
D4 --> D5["4. HR Initiates Exit Checklist
(Clearance, Asset Return, Final Dues)"]
D5 --> D6["5. On Last Working Day:"]
D6 --> D7["- Update employee_employment with termination_date
- Update employees set is_active = false"]
D7 --> D_END((End:
Employee Exited))
D3 -- No / Needs Discussion --> D8["3b. Manager holds discussion with employee"]
D8 --> D9{Withdraw Resignation?}
D9 -- Yes --> D10["Update Record
status = 'withdrawn'"] --> D_END
D9 -- No, Proceeds --> D4
end