논리적erd

테이블 (22)

attachment

  • file_id — BIGINT — 기본 키 — NOT NULL
  • target_table — VARCHAR(50)
  • target_id — BIGINT
  • file_name — VARCHAR(255)
  • file_path — VARCHAR(500)
  • created_at — DATETIME

budget_allowance

  • allowance_id — BIGINT — 기본 키 — NOT NULL
  • project_id — BIGINT — 외래 키 → budget_project.project_id
  • category_id — BIGINT — 외래 키 → budget_category.category_id
  • initial_budget — DECIMAL(15,2)
  • limit_amt — DECIMAL(15,2)
  • created_by — BIGINT
  • created_at — DATETIME

budget_category

  • category_id — BIGINT — 기본 키 — NOT NULL
  • category_code — VARCHAR(50)
  • category_name — VARCHAR(100)
  • use_yn — CHAR(1)

budget_project

  • project_id — BIGINT — 기본 키 — NOT NULL
  • project_year — INT
  • project_type — VARCHAR(100)
  • project_name — VARCHAR(200)
  • start_date — DATE
  • end_date — DATE
  • status — VARCHAR(20)

change_log

  • log_id — BIGINT — 기본 키 — NOT NULL
  • target_table — VARCHAR(50)
  • target_id — BIGINT
  • action_type — VARCHAR(50)
  • old_val — TEXT
  • new_val — TEXT
  • processed_by — BIGINT
  • processed_date — DATETIME
  • process_comment — VARCHAR(255)

common_code

  • code_id — BIGINT — 기본 키 — NOT NULL
  • group_id — VARCHAR(50)
  • code_name — VARCHAR(100)
  • use_yn — CHAR(1)

expenditure

  • expense_id — BIGINT — 기본 키 — NOT NULL
  • allowance_id — BIGINT — 외래 키 → budget_allowance.allowance_id
  • professor_id — BIGINT — 외래 키 → professor.professor_id
  • expense_date — DATE
  • subject_type — VARCHAR(50)
  • subject_code — VARCHAR(50)
  • amount — DECIMAL(15,2)
  • status — VARCHAR(20)
  • created_at — DATETIME
  • created_by — BIGINT
  • processed_date — DATETIME
  • process_comment — VARCHAR(255)

expense_stu_labor

  • labor_id — BIGINT — 기본 키 — NOT NULL
  • expense_id — BIGINT — 외래 키 → expenditure.expense_id
  • student_id — BIGINT — 외래 키 → student.student_id
  • target_year_month — VARCHAR(10)
  • amount — DECIMAL(15,2)

mileage_req

  • req_id — BIGINT — 기본 키 — NOT NULL
  • student_id — BIGINT — 외래 키 → student.student_id
  • program_id — BIGINT — 외래 키 → sw_program.program_id
  • req_type — VARCHAR(50)
  • req_point — INT
  • status — VARCHAR(20)
  • req_date — DATETIME
  • memo — VARCHAR(500)
  • processed_by — BIGINT
  • processed_date — DATETIME
  • process_comment — VARCHAR(255)

permission

  • permission_id — BIGINT — 기본 키 — NOT NULL
  • permission_name — VARCHAR(100)
  • description — VARCHAR(255)

professor

  • professor_id — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(100)
  • dept_code — VARCHAR(50)
  • use_yn — CHAR(1)

response_detail

  • detail_id — BIGINT — 기본 키 — NOT NULL
  • response_id — BIGINT — 외래 키 → survey_response.response_id
  • question_id — BIGINT — 외래 키 → survey_question.question_id
  • score — INT
  • essay — TEXT

student

  • student_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id
  • student_no — VARCHAR(50)
  • name — VARCHAR(100)
  • college — VARCHAR(100)
  • dept_code — VARCHAR(50)
  • reg_field — VARCHAR(50)
  • campus — VARCHAR(50)
  • grade — INT
  • academic_status — VARCHAR(50)
  • status_change_reason — VARCHAR(500)
  • status_change_date — DATE
  • admission_type — VARCHAR(50)
  • admission_date — DATE
  • phone_number — VARCHAR(50)
  • sms_agree_yn — CHAR(1)
  • email — VARCHAR(100)

student_update_req

  • req_id — BIGINT — 기본 키 — NOT NULL
  • student_id — BIGINT — 외래 키 → student.student_id
  • target_col — VARCHAR(50)
  • old_val — VARCHAR(255)
  • new_val — VARCHAR(255)
  • status — VARCHAR(50)
  • req_date — DATETIME
  • processed_by — BIGINT
  • processed_date — DATETIME
  • process_comment — VARCHAR(255)

survey

  • survey_id — BIGINT — 기본 키 — NOT NULL
  • program_id — BIGINT — 외래 키 → sw_program.program_id
  • survey_title — VARCHAR(200)
  • start_date — DATE
  • end_date — DATE
  • status — VARCHAR(20)

survey_question

  • question_id — BIGINT — 기본 키 — NOT NULL
  • survey_id — BIGINT — 외래 키 → survey.survey_id
  • content — VARCHAR(500)
  • question_type — VARCHAR(50)
  • score — INT
  • scale_type — VARCHAR(50)

survey_response

  • response_id — BIGINT — 기본 키 — NOT NULL
  • survey_id — BIGINT — 외래 키 → survey.survey_id
  • student_id — BIGINT — 외래 키 → student.student_id
  • is_completed — BOOLEAN
  • completed_date — DATETIME

sw_program

  • program_id — BIGINT — 기본 키 — NOT NULL
  • program_type — VARCHAR(50)
  • program_name — VARCHAR(200)
  • event_date — DATE
  • host_org — VARCHAR(100)
  • participate_point — INT
  • award_point — INT

topcit_round

  • round_id — BIGINT — 기본 키 — NOT NULL
  • round_name — VARCHAR(100)
  • exam_date — DATE

topcit_score

  • score_id — BIGINT — 기본 키 — NOT NULL
  • student_id — BIGINT — 외래 키 → student.student_id
  • round_id — BIGINT — 외래 키 → topcit_round.round_id
  • total_score — INT
  • created_by — BIGINT
  • created_at — DATETIME

user_permission

  • user_permission_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id
  • permission_id — BIGINT — 외래 키 → permission.permission_id

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • login_id — VARCHAR(50)
  • password_hash — VARCHAR(255)
  • role — VARCHAR(50)
  • use_yn — CHAR(1)
  • created_at — DATETIME