수정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