AIAZH従業員コアテーブル
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.
部門とポジションの階層
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.
出勤とタイムトラッキング
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.
休暇管理
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.
給与スキーマ
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.
パフォーマンス管理
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.
採用とオンボーディング
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.
HRMSデータベースのベストプラクティス
給与とパフォーマンスデータには行レベルセキュリティを実装 — HRマネージャーは報告チェーンのみを表示すべき。すべての従業員関連テーブルには監査証跡を維持するためにソフト削除を使用。給与、銀行口座番号、個人住所などの機密カラムはデータベースレベルで暗号化。高速ルックアップのためにemployee_codeとwork_emailにインデックス。数千人の従業員を持つ組織では、attendance_logsを月ごとにパーティショニングし、2年以上前のレコードをアーカイブ。給与計算には常にプリペアドステートメントを使用。労働規制への準拠のために、誰が何をいつ変更したかを記録する別のaudit_trailテーブルを維持。
