AIAZHTabela Principal de Funcionários
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.
Hierarquia de Departamentos e Cargos
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.
Frequência e Controle de Tempo
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.
Gestão de Férias
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.
Esquema de Folha de Pagamento
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.
Gestão de Desempenho
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.
Recrutamento e Integração
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.
Melhores Práticas para Banco de Dados HRMS
Implemente segurança a nível de linha para dados de folha de pagamento e desempenho — os gerentes de RH devem ver apenas sua cadeia de reporte. Use exclusões suaves em todas as tabelas relacionadas a funcionários para manter trilhas de auditoria. Criptografe colunas sensíveis como salário, números de conta bancária e endereços pessoais a nível de banco de dados. Indexe employee_code e work_email para buscas rápidas. Para organizações com milhares de funcionários, faça partição de attendance_logs por mês e arquive registros com mais de 2 anos. Sempre use declarações preparadas para cálculos de folha de pagamento. Mantenha uma tabela audit_trail separada que registre quem mudou o quê e quando para conformidade com regulamentações trabalhistas.
