임포트테스트용2
테이블 (27)
assessment_options
- option_id — BIGINT — 기본 키 — NOT NULL — AUTO_INCREMENT
- ques_id — INT — 외래 키 → assessment_questions.ques_id — NOT NULL — AUTO_INCREMENT
- option_text — VARCHAR(200) — NOT NULL
- score — INT — NOT NULL
- disp_order — INT
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
assessment_questions
- ques_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- comp_id — INT — 외래 키 → competencies.comp_id — NOT NULL
- question_text — TEXT — NOT NULL
- question_type — VARCHAR(20) — NOT NULL
- disp_order — INT
- is_active — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
assessment_responses
- resp_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- ques_id — INT — 외래 키 → assessment_questions.ques_id — NOT NULL — AUTO_INCREMENT
- option_id — BIGINT — 외래 키 → assessment_options.option_id — NOT NULL — AUTO_INCREMENT
- rslt_id — INT — 외래 키 → assessment_results.rslt_id — NOT NULL — AUTO__INCREMENT
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
assessment_results
- rslt_id — INT — 기본 키 — NOT NULL — AUTO__INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE=student
- assess_id — INT — 외래 키 → assessment_sections.assess_id — NOT NULL — AUTO_INCREMENT
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
- is_submitted — BOOLEAN
- submitted_at — TIMESTAMP
assessment_sections
- assess_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- title — VARCHAR(200) — NOT NULL
- description — TEXT
- start_date — DATATIME — NOT NULL
- end_date — DATATIME — NOT NULL
- is_active — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
common_codes
- code_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- code_group — VARCHAR(50) — NOT NULL
- code_value — VARCHAR(50) — NOT NULL
- code_name — VARCHAR(100) — NOT NULL
- description — TEXT
- disp_order — INT
- is_active — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
competencies
- comp_id — INT — 기본 키 — NOT NULL
- competency_id — INT — 외래 키 → competencies.comp_id — NOT NULL
- comp_name — VARCHAR(100) — NOT NULL
- description — TEXT
- disp_order — INT
- is_active — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
- Field — VARCHAR(255)
- Field2 — VARCHAR(255)
counseling_records
- record_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE='STUDENT
- ROLE=counselor, — `resv_id` — NOT NULL — UNIQUE. AUTO_INCREMENT
- counseling_date — DATETIME — NOT NULL
- content — TEXT — NOT NULL
- memo — TEXT
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
counseling_reservations
- resv_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE=counselor, ROLE=student
- counsel_type — VARCHAR(20) — NOT NULL
- content — TEXT — NOT NULL
- pref_date — DATETIME
- status — VARCHAR(20) — ENUM('PENDING', 'APPROVED
- CANCELLED), — `conf_date` — NOT NULL — INSERT 시점
- rejected_at — TIMESTAMP
- cancelled_at — TIMESTAMP
counseling_satisfaction
- satis_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- student_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE='STUDENT'
- resv_id — INT — 외래 키 → counseling_reservations.resv_id — NOT NULL — AUTO_INCREMENT
- avg_score — DECIMAL(3, 2) — NOT NULL — 객관식 문항 평균점수
- feedback — TEXT
- submitted_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
counseling_satisfaction_survey_answer
- answer_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- question_id — INT — 외래 키 → counseling_satisfaction_survey_question.question_id — NOT NULL — AUTO_INCREMENT
- satis_id — INT — 외래 키 → counseling_satisfaction.satis_id — NOT NULL — AUTO_INCREMENT
- answer_score — INT
- answer_text — TEXT — NOT NULL
- created_at — TIMESTAMP — INSERT 시점
- updated_at — TIMESTAP — UPDATE 시점
counseling_satisfaction_survey_question
- question_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- question_text — TEXT — NOT NULL
- question_type — ENUM('objective', 'subjective') — NOT NULL
- is_active — BOOLEAN — 비활성화시 설문에 미표시
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
counselors
- counselor_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT
- special — VARCHAR(100)
- intro — TEXT
- avail_days — VARCHAR(100)
- created_at — TIMESTAMP — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
counselour_weekly_unavailability
- unavail_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- date — DATE — NOT NULL
- start_time — TIME — NOT NULL — 불가능 시작시간
- end_time — TIME — 불가능 종료시간
- reason — VARCHAR(100) — 사유 (휴가, 외부회의 등). NULLABLE
- created_at — DATETIME — INSERT 시점
- updated_at — DATETIME — UPDATE 시점
email_logs
- email_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- recipient_id — INT — NOT NULL — 수신자 참조
- email_address — VARCHAR(100) — NOT NULL
- subject — VARCHAR(255) — NOT NULL
- tmpl_name — VARCHAR(100)
- status — VARCHAR(20) — NOT NULL — SUCCESS, FAILED
- err_msg — TEXT
- sent_at — TIMESTAMP — NOT NULL
- content — TEXT
login_history
- history_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT
- login_at — TIMESTAMP
- ip_address — VARCHAR(45)
- user_agent — VARCHAR(255)
- is_success — BOOLEAN — NOT NULL
- fail_reason — VARCHAR(255)
mileage_records
- mileage_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- student_id — INT — 외래 키 → users.user_id — NOT NULL — 학생참조, ROLE=student
- prog_id — INT — 외래 키 → programs.prog_id — NOT NULL — AUTO_INCREMENT
- points — INT — NOT NULL
- type — VARCHAR(20) — EARN, USE, ADJUST
- description — VARCHAR(255)
- created_by — INT — NOT NULL — 생성자 참조
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
notifications
- notif_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT
- title — VARCHAR(200) — NOT NULL
- content — TEXT
- type — VARCHAR(20) — NOT NULL — COUNSELING, PROGRAM, ASSESSMENT, SYSTEM
- is_read — BOOLEAN — NOT NULL
- ling_url — VARCHAR(500)
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- read_at — TIMESTAMP
program_applications
- application_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- prog_id — INT — 외래 키 → programs.prog_id — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE=student
- app_type — VARCHAR(20) — NOT NULL — PENDING, APPROVED, REJECTED, CANCELLED
- reject_rsn — TEXT
- applicated_at — TIMESTAMP — NOT NULL
- applied_at — TIMESTAMP
- cancelled_at — TIMESTAMP
program_attendance
- attendance_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- application_id — INT — 외래 키 → program_applications.application_id — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE=student, ROLE=operator
- attend_dt — DATE — NOT NULL
- status — VARCHAR(20) — NOT NULL — PRESENT, ABSENT, LATE, EXCUSED
- note — TEXT
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
program_categories
- catg_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- category_name — VARCHAR(100)
- description — TEXT
- is_active — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
program_competencies
- mapping_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- prog_id — INT — 외래 키 → programs.prog_id — NOT NULL — AUTO_INCREMENT
- comp_id — INT — 외래 키 → competencies.comp_id — NOT NULL
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
program_reports
- report_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- prog_id — INT — 외래 키 → programs.prog_id — NOT NULL — AUTO_INCREMENT
- application_id — INT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT
- writer_type — VARCHAR(20) — NOT NULL — STUDENT, MANAGER
- content — TEXT — NOT NULL
- file_path — VARCHAR(500)
- submitted_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
program_satisfaction
- satisfaction_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- program_id — INT — 외래 키 → programs.prog_id — NOT NULL — 프로그램 참조
- student_id — INT — 외래 키 → users.user_id — NOT NULL — 학생참조, ROLE=student
- rating — INT — NOT NULL — 만족도 평점(1-5)
- feedback — TEXT
- submitted_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
programs
- prog_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- catg_id — INT — 외래 키 → program_categories.catg_id — NOT NULL — AUTO_INCREMENT
- user_id — INT — 외래 키 → users.user_id — NOT NULL — AUTO_INCREMENT, ROLE=admin
- title — VARCHAR(200) — NOT NULL
- description — TEXT
- start_date — DATETIME — NOT NULL
- end_date — DATETIME — NOT NULL
- app_start — DATETIME — NOT NULL
- app_end — DATETIME — NOT NULL
- max_part — INT — NOT NULL
- min_part — INT
- cur_part — INT — NOT NULL
- mileage — INT — NOT NULL
- location — VARCHAR(200)
- status — VARCHAR(20) — NOT NULL — DRAFT, PENDING, APPROVED, REJECTED, ONGOING, COMPLETED, CANCELLED
- created_at — TIMESTAMP — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점
users
- user_id — INT — 기본 키 — NOT NULL — AUTO_INCREMENT
- student_num — INT — NOT NULL — 학생 학번
- name — VARCHAR(50) — NOT NULL — 사용자 이름
- email — VARCHAR(100) — NOT NULL
- phone — VARCHAR(100)
- password — VARCHAR(255)
- birth_date — DATE — NOT NULL
- department — VARCHAR(100)
- grade — INT
- role — VARCHAR(20) — ENUM('STUDENT', 'CONSELOR
- ADMIN), — `locked` — NOT NULL — INSERT 시점
- updated_at — TIMESTAMP — UPDATE 시점
- deleted_at — TIMESTAMP — Soft Delete 시점