biz

Tables (9)

attendances

  • attendance_id — number(12) — NOT NULL
  • employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
  • record_date — date — NOT NULL
  • check_in_time — timestamp
  • check_out_time — timestamp
  • work_hours — number(5,2)
  • status — varchar(20) — NOT NULL
  • source — varchar(20) — NOT NULL
  • reason — varchar(1000)
  • created_by — number(10)
  • created_at — timestamp
  • updated_by — number(10)
  • updated_at — timestamp

departments

  • dept_id — number(6) — NOT NULL
  • dept_code — varchar(30) — NOT NULL
  • dept_name — varchar(200) — NOT NULL
  • parent_dept_id — number(6) — Foreign key → departments.dept_id
  • manager_emp_id — number(10) — Foreign key → employees.employee_id
  • created_at — timestamp
  • updated_at — timestamp

employees

  • employee_id — number(10) — NOT NULL
  • emp_no — varchar(30)
  • emp_name — varchar(100) — NOT NULL
  • gender — char(1)
  • birth_date — date
  • phone — varchar(30)
  • email — varchar(150)
  • address — varchar(400)
  • dept_id — number(6) — Foreign key → departments.dept_id
  • position — varchar(100)
  • rank — varchar(50)
  • start_date — date — NOT NULL
  • termination_date — date
  • status — varchar(20) — NOT NULL
  • career_years — number(5,1)
  • ssn_mask — varchar(20)
  • created_by — number(10)
  • created_at — timestamp
  • updated_by — number(10)
  • updated_at — timestamp

leave_requests

  • leave_id — number(12) — NOT NULL
  • employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
  • leave_type — varchar(30) — NOT NULL
  • start_date — date — NOT NULL
  • end_date — date — NOT NULL
  • days — number(6,2)
  • reason — varchar(2000)
  • status — varchar(20) — NOT NULL
  • approver_id — number(10) — Foreign key → employees.employee_id
  • requested_at — timestamp
  • approved_at — timestamp
  • comments — varchar(2000)
  • created_by — number(10)
  • created_at — timestamp

personnel_assignments

  • assignment_id — number(12) — NOT NULL
  • employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
  • assignment_date — date — NOT NULL
  • previous_dept_id — number(6) — Foreign key → departments.dept_id
  • new_dept_id — number(6) — Foreign key → departments.dept_id
  • previous_position — varchar(100)
  • new_position — varchar(100)
  • previous_rank — varchar(50)
  • new_rank — varchar(50)
  • reason — varchar(1000)
  • created_by — number(10)
  • created_at — timestamp — NOT NULL

roles

  • role_id — number(10) — NOT NULL
  • role_name — varchar(50) — NOT NULL

salaries

  • salary_id — number(12) — NOT NULL
  • employee_id — number(10) — Foreign key → employees.employee_id — NOT NULL
  • pay_period_start — date — NOT NULL
  • pay_period_end — date — NOT NULL
  • payment_date — date — NOT NULL
  • base_salary — number(12,2) — NOT NULL
  • additional_allowance — number(12,2)
  • commission — number(12,2)
  • other_pay — number(12,2)
  • gross_pay — number(14,2)
  • deductions — number(14,2)
  • net_pay — number(14,2)
  • remarks — varchar(2000)
  • created_by — number(10)
  • created_at — timestamp

user_roles

  • user_id — number(10) — Foreign key → users.user_id — NOT NULL
  • role_id — number(10) — Foreign key → roles.role_id — NOT NULL

users

  • user_id — number(10) — NOT NULL
  • employee_id — number(10) — Foreign key → employees.employee_id
  • username — varchar(100) — NOT NULL
  • password_hash — varchar(255) — NOT NULL
  • salt — varchar(255)
  • is_active — char(1) — NOT NULL
  • is_locked — char(1) — NOT NULL
  • last_login_at — timestamp
  • failed_login_count — number(4)
  • created_at — timestamp
  • updated_at — timestamp