WhoDidIt

Tables (7)

app_user

  • user_id — SERIAL
  • nickname — VARCHAR(50) — NOT NULL
  • role — VARCHAR(10) — NOT NULL
  • score — INTEGER — NOT NULL
  • CHECK — (ROLE IN ('의뢰인', '범인', '경찰', '탐정'))
  • CHECK — (score >= 0)

case_info

  • case_id — SERIAL
  • title — VARCHAR(100) — NOT NULL
  • content — TEXT
  • difficulty — INTEGER — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • true_criminal_id — INTEGER
  • CHECK — (difficulty >= 1 AND difficulty <= 5)
  • CHECK — (status IN ('등록', '조작', '접수중', '배정', '결과 확인'))

case_participation

  • part_id — SERIAL
  • case_id — INTEGER — NOT NULL
  • client_id — INTEGER
  • criminal_id — INTEGER
  • police_id — INTEGER
  • detective_id — INTEGER
  • detective_guess_id — INTEGER
  • is_solved — BOOLEAN

case_suspect

  • suspect_id — SERIAL
  • case_id — INTEGER — NOT NULL
  • suspect_name — VARCHAR(50) — NOT NULL

original_evidence

  • evidence_id — SERIAL
  • case_id — INTEGER — NOT NULL
  • description — VARCHAR(255) — NOT NULL
  • is_true — BOOLEAN — NOT NULL
  • is_fake_candidate — BOOLEAN — NOT NULL

score_log

  • log_id — SERIAL
  • user_id — INTEGER — NOT NULL
  • case_id — INTEGER
  • score_change — INTEGER — NOT NULL
  • reason — VARCHAR(50) — NOT NULL
  • log_time — TIMESTAMP

submitted_evidence

  • submit_id — SERIAL
  • case_id — INTEGER — NOT NULL
  • evidence_description — VARCHAR(255) — NOT NULL
  • is_true_evidence — BOOLEAN — NOT NULL