AIAZHTable Principale des Employés
The employees table is the central entity. Fields include employee_id (PK), employee_code (unique alphanumeric identifier), first_name, last_name, date_of_birth, gender, phone, personal_email, work_email, department_id (FK), position_id (FK), manager_id (self-referencing FK for reporting hierarchy), employment_type (full-time/part-time/contract/intern), date_of_joining, confirmation_date, exit_date, and employment_status. An employee_documents table stores references to offer letters, contracts, and ID proofs, each with an expiry_date field to trigger renewal reminders.
Hiérarchie des Départements et Positions
The departments table organizes the company structure: department_id (PK), department_name, department_code, head_of_department (FK to employee_id), parent_department_id (self-referencing FK), and cost_center_code. The positions table defines job roles: position_id (PK), position_title, job_grade, minimum_salary, maximum_salary, and required_qualifications. An employee_positions junction table tracks position history: employee_id (FK), position_id (FK), effective_date, end_date, and is_current. This design supports employees who change roles over time while maintaining a complete career history.
Présence et Suivi du Temps
The attendance table records daily check-in/check-out: attendance_id (PK), employee_id (FK), date, check_in_time, check_out_time, total_hours_worked, overtime_hours, and status. An attendance_logs table stores raw biometric or RFID punches for audit purposes. For shift-based organizations, a shifts table (shift_id, shift_name, start_time, end_time, grace_period_minutes) combines with an employee_shifts assignment table. Late arrivals and early departures are calculated by comparing actual check-in/out times against the assigned shift window.
Gestion des Congés
The leave_types table lists available leave categories: leave_type_id (PK), leave_name (Annual/Sick/Maternity/Paternity/Compassionate/Unpaid), is_paid, and carry_forward_allowed. The employee_leave_balance table tracks entitlement: balance_id (PK), employee_id (FK), leave_type_id (FK), fiscal_year, total_days_allocated, days_used, days_pending_approval, and days_carried_forward. The leave_applications table handles requests: leave_application_id (PK), employee_id (FK), leave_type_id (FK), start_date, end_date, total_days, reason, status, and approved_by.
Schéma de Paie
The payroll schema is the most financially sensitive part. The payroll_cycles table defines periods: cycle_id (PK), cycle_name, start_date, end_date, payment_date, and status. The salary_structures table defines components: structure_id (PK), employee_id (FK), effective_date, basic_pay, hra, conveyance_allowance, medical_allowance, special_allowance, pf_deduction, professional_tax, income_tax, and net_salary. The payroll_runs table stores generated payslips: payroll_run_id (PK), employee_id (FK), cycle_id (FK), gross_pay, total_deductions, net_pay, overtime_pay, reimbursement, leave_deductions, and payment_status. A payroll_audit table logs every change to salary structures for compliance.
Gestion de la Performance
The performance_reviews table records appraisals: review_id (PK), employee_id (FK), reviewer_id (FK), review_period, review_date, overall_rating, and summary. A review_goals table tracks objectives: goal_id (PK), review_id (FK), goal_description, weight_percent, self_rating, manager_rating, and comments. The review_skills table evaluates competencies: skill_id (PK), review_id (FK), skill_name, skill_category, and rating. Performance scores can feed into bonus calculations and promotion recommendations.
Recrutement et Intégration
The job_openings table lists positions: opening_id (PK), department_id (FK), position_title, required_skills, experience_required, openings_count, filled_count, status, and posted_date. The candidates table tracks applicants: candidate_id (PK), first_name, last_name, email, phone, resume_url, source, current_company, current_ctc, expected_ctc, notice_period, and status. An interviews table schedules and scores: interview_id (PK), candidate_id (FK), job_opening_id (FK), interviewer_id (FK), interview_date, interview_type, round_number, rating, and feedback.
Meilleures Pratiques pour la Base de Données HRMS
Implémentez la sécurité au niveau des lignes pour les données de paie et de performance — les gestionnaires RH ne devraient voir que leur chaîne de reporting. Utilisez les suppressions logiques sur toutes les tables liées aux employés pour maintenir des pistes d'audit. Chiffrez les colonnes sensibles comme le salaire, les numéros de compte bancaire et les adresses personnelles au niveau de la base de données. Indexez employee_code et work_email pour des recherches rapides. Pour les organisations avec des milliers d'employés, partitionnez attendance_logs par mois et archivez les enregistrements de plus de 2 ans. Utilisez toujours des requêtes préparées pour les calculs de paie. Maintenez une table audit_trail séparée qui enregistre qui a changé quoi et quand pour la conformité avec les réglementations du travail.
