danchive

Tables (12)

categories

  • id — BIGINT
  • name — VARCHAR(80)
  • parent_id — BIGINT
  • created_at — DATETIME(3)

exhibit_categories

  • exhibit_id — BIGINT
  • category_id — BIGINT
  • created_at — DATETIME(3)

exhibits

  • Key (id) — BIGINT — Primary key — NOT NULL
  • team_id — BIGINT
  • title — VARCHAR(200)
  • description_md — MEDIUMTEXT
  • semester — CHAR(7)
  • poster_media_id — BIGINT
  • ppt_media_id — BIGINT
  • view_count — BIGINT
  • like_count — BIGINT
  • rating_avg — DECIMAL(3,2)
  • rating_count — INT
  • is_awarded — BOOLEAN
  • award_type — ENUM('최우수','우수','장려','동')
  • created_at — DATETIME(3)
  • updated_at — DATETIME(3)
  • Key (id2) — BIGINT — Foreign key → teams.id — NOT NULL

likes

  • id — BIGINT
  • exhibit_id — BIGINT
  • user_id — BIGINT
  • visitor_session_id — BIGINT
  • created_at — DATETIME(3)

media

  • id — BIGINT
  • kind — ENUM('poster','ppt')
  • variant — ENUM('original','thumb','slide')
  • parent_media_id — BIGINT
  • storage_path — VARCHAR(1024)
  • mime — VARCHAR(120)
  • size_bytes — BIGINT
  • width — INT
  • height — INT
  • page_no — INT
  • created_by — BIGINT
  • created_at — DATETIME(3)

notifications

  • id — BIGINT
  • type — VARCHAR(40)
  • title — VARCHAR(140)
  • body — VARCHAR(512)
  • deep_link — VARCHAR(512)
  • scheduled_at — DATETIME
  • sent_at — DATETIME
  • created_at — DATETIME(3)

prof_reviews

  • id — BIGINT
  • exhibit_id — BIGINT
  • prof_user_id — BIGINT
  • criteria_json — JSON
  • avg_score — DECIMAL(4,2)
  • comment — TEXT — EX
  • created_at — DATETIME(3)

teams

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(120)
  • semester — CHAR(7)
  • created_at — DATETIME(3)
  • Key (team_id) — BIGINT — Foreign key → users.team_id — NOT NULL

users

  • Field (id) — BIGINT — Primary key
  • team_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255)
  • name — VARCHAR(100)
  • department — VARCHAR(120)
  • role — ENUM('admin','professor','team')
  • is_verified_email — BOOLEAN
  • is_active — BOOLEAN
  • created_at — DATETIME(3)
  • last_login_at — DATETIME(3)

views

  • id — BIGINT
  • exhibit_id — BIGINT
  • user_id — BIGINT
  • visitor_session_id — BIGINT
  • ip_hash — CHAR(64)
  • created_at — DATETIME(3)

visitor_sessions

  • id — BIGINT
  • device_fp — CHAR(64)
  • ip_hash — CHAR(64)
  • issued_at — DATETIME(3)
  • expires_at — DATETIME(3)
  • qr_gate — VARCHAR(40)

votes

  • id — BIGINT
  • exhibit_id — BIGINT
  • voter_user_id — BIGINT
  • visitor_session_id — BIGINT
  • q1 — TINYINT
  • q2 — TINYINT
  • q3 — TINYINT
  • text_feedback — TEXT
  • sentiment_score — DECIMAL(4,3)
  • star — DECIMAL(3,1)
  • created_at — DATETIME(3)