임포트테스트용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

files

    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 시점