수정2 erd

테이블 (17)

absence

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • approval_request_id — BIGINT — 외래 키 → approval_requests.id — NOT NULL
  • type — VARCHAR(50) — NOT NULL
  • urgency — VARCHAR(50)
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • start_time — TIMESTAMP(6)
  • end_time — TIMESTAMP(6)
  • reason — TEXT
  • approval_status — VARCHAR(30) — NOT NULL
  • approver_id — BIGINT
  • approved_at — TIMESTAMP(6)
  • reject_comment — TEXT — LO
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

approval_requests

  • id — BIGINT — 기본 키 — NOT NULL
  • approver_id — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • applicant_id — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • request_type — VARCHAR(50) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • requested_at — TIMESTAMP(6) — NOT NULL
  • processed_at — TIMESTAMP(6)
  • reason — TEXT
  • title — VARCHAR(255)
  • vacation_type — VARCHAR(50)
  • certificate_type — VARCHAR(50)
  • start_date — DATE
  • end_date — DATE
  • reject_comment — TEXT — LO
  • absence_type — VARCHAR(50)
  • urgency — VARCHAR(50)
  • start_time — TIMESTAMP(6)
  • end_time — TIMESTAMP(6)

attachments

  • attachment_id — BIGINT — 기본 키 — NOT NULL
  • message_id — NUMBER(19) — 외래 키 → messages.message_id — NOT NULL
  • attachment_url — VARCHAR(1024) — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL

attendance

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — NUMBER(19) — 외래 키 → users.employee_no — NOT NULL
  • attendance_date — DATE — NOT NULL
  • check_in_time — TIMESTAMP(6) — NOT NULL
  • check_out_time — TIMESTAMP(6)
  • go_out_time — TIMESTAMP(6)
  • return_time — TIMESTAMP(6)
  • ip_address — VARCHAR(45) — NOT NULL

categories

  • id — BIGINT — 기본 키 — NOT NULL
  • department_id — BIGINT — 외래 키 → departments.department_id — NOT NULL
  • owner_employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • color — VARCHAR(7) — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • is_checked — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

certificates

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • approval_request_id — BIGINT — 외래 키 → approval_requests.id — NOT NULL
  • type — VARCHAR(50)
  • status — VARCHAR(30) — NOT NULL
  • request_date — DATE
  • approve_date — DATE
  • expiration_date — DATE
  • reject_comment — TEXT — LO
  • approver_id — NUMBER(19)
  • approver_name — VARCHAR(255)
  • processed_at — TIMESTAMP(6)

chat_messages

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • conversation_id — VARCHAR(255) — NOT NULL
  • message_content — TEXT — NOT NULL
  • sender_type — VARCHAR(20) — NOT NULL
  • timestamp — TIMESTAMP(6) — NOT NULL

departments

  • department_id — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • department_color — VARCHAR(225) — NOT NULL
  • image_url — VARCHAR(225)

events

  • id — BIGINT — 기본 키 — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.id — NOT NULL
  • owner_employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • start_date — DATE
  • end_date — DATE
  • is_all_day — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • updated_at — TIMESTAMP(6) — NOT NULL

messages

  • message_id — BIGINT — 기본 키 — NOT NULL
  • receiver_id — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • sender_id — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • subject — VARCHAR(255)
  • content — TEXT
  • sent_at — TIMESTAMP(6) — NOT NULL
  • is_read — TINYINT(1) — NOT NULL
  • read_at — TIMESTAMP(6)
  • is_notice — TINYINT(1) — NOT NULL

notifications

  • notification_id — BIGINT — 기본 키 — NOT NULL
  • message_id — BIGINT — 외래 키 → messages.message_id — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • type — VARCHAR(50) — NOT NULL
  • message — VARCHAR(500) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • is_read — TINYINT(1) — NOT NULL
  • read_at — TIMESTAMP(6)

payrolls

  • payroll_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • base_payroll — NUMBER(10)
  • position_allowance — NUMBER(10)
  • meal_allowance — NUMBER(10)
  • bonus — NUMBER(10)
  • pay_year — NUMBER(10) — NOT NULL
  • pay_month — NUMBER(10) — NOT NULL
  • updated_at — TIMESTAMP(6)
  • total_work_minutes — NUMBER(19)
  • final_pay_amount — NUMBER(19)
  • overtime_pay — NUMBER(10)

position

  • position_id — BIGINT — 기본 키 — NOT NULL
  • position_name — VARCHAR(255) — NOT NULL

users

  • employee_no — BIGINT — 기본 키 — NOT NULL
  • position_id — BIGINT — 외래 키 → position.position_id — NOT NULL
  • department_id — BIGINT — 외래 키 → departments.department_id — NOT NULL
  • user_name — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • external_email — VARCHAR(100)
  • resident_reg_no — VARCHAR(100)
  • phone — VARCHAR(20) — NOT NULL
  • birth_date — DATE — NOT NULL
  • gender — VARCHAR(10) — NOT NULL
  • created_at — TIMESTAMP(6) — NOT NULL
  • hire_date — DATE — NOT NULL
  • address — TEXT — NOT NULL
  • remarks — TEXT
  • bank_name — VARCHAR(50)
  • account_number — VARCHAR(50)
  • account_holder — VARCHAR(50)
  • retire_date — DATE
  • activate — VARCHAR(4) — NOT NULL
  • hr_role — VARCHAR(4) — NOT NULL

vacation_balances

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • total_granted — NUMBER(4,1) — NOT NULL
  • used_days — NUMBER(4,1) — NOT NULL

vacations

  • id — BIGINT — 기본 키 — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • approval_request_id — BIGINT — 외래 키 → approval_requests.id — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • vacation_type — VARCHAR(50) — NOT NULL
  • vacation_status — VARCHAR(50) — NOT NULL
  • reason — TEXT
  • reject_comment — TEXT — LO

work_statuses

  • id — BIGINT — 기본 키 — NOT NULL
  • attendance_id — BIGINT — 외래 키 → attendance.id — NOT NULL
  • employee_no — BIGINT — 외래 키 → users.employee_no — NOT NULL
  • date — DATE — NOT NULL
  • status_type — VARCHAR(50) — NOT NULL
  • work_day_type — VARCHAR(50)
  • reason — TEXT
  • check_in_time — TIMESTAMP(6)
  • check_out_time — TIMESTAMP(6)
  • is_late — TINYINT(1) — NOT NULL