학생 민원/고충 관리 시스템(논리적 모델링, 물리적 모델링)

테이블 (5)

Admin(관리자) (Admin)

  • admin_id — INT — 기본 키
  • name — VARCHAR(100) — NOT NULL
  • role — ENUM('학생회','선생님') — NOT NULL
  • password — VARCHAR(255) — NOT NULL

AdminResponse(관리자 답변) (AdminResponse)

  • response_id — INT — 기본 키
  • admin_id (admin_id2) — INT — 외래 키 → Admin(관리자).admin_id
  • complaint_id (complaint_id2) — INT — 외래 키 → Complaint(민원).complaint_id
  • content — TEXT — NOT NULL
  • created_at — DATETIME

Complaint(민원) (Complaint)

  • complaint_id — INT — 기본 키
  • student_id (student_id2) — INT — 외래 키 → Student(학생).student_id
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • category — ENUM('생활','시설','학사') — NOT NULL
  • is_anonymous — BOOLEAN
  • status — ENUM('등록','처리중','완료','삭제')
  • created_at — DATETIME

Student(학생) (Student)

  • student_id — INT — 기본 키
  • name — VARCHAR(100) — NOT NULL
  • student_number — VARCHAR(100) — NOT NULL
  • password — VARCHAR(100) — NOT NULL

Vote(투표) (Vote)

  • vote_id — INT — 기본 키
  • complaint_id (complaint_id2) — INT — 외래 키 → Complaint(민원).complaint_id
  • student_id (student_id3) — INT — 외래 키 → Student(학생).student_id
  • vote_type — ENUM('추천','비추') — NOT NULL
  • created_at — DATETIME