DB범죄수사

테이블 (8)

CASE_PERSON

  • id — INT — 기본 키 — NOT NULL
  • case_id — INT — 외래 키 → CASE_TB.case_id — NOT NULL
  • person_id — INT — 외래 키 → PERSON.person_id — NOT NULL
  • relation_type — VARCHAR(30) — NOT NULL
  • registered_at — DATETIME

CASE_TB

  • case_id — INT — 기본 키 — NOT NULL
  • case_code — VARCHAR(20) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • crime_type — VARCHAR(50) — NOT NULL
  • occurred_at — DATE — NOT NULL
  • location — VARCHAR(300)
  • status — VARCHAR(20)
  • lead_detective_id — INT — 외래 키 → DETECTIVE.detective_id
  • created_at — DATETIME

DETECTIVE

  • detective_id — INT — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • badge_no — VARCHAR(20) — NOT NULL
  • rank — VARCHAR(30) — NOT NULL
  • department — VARCHAR(100)
  • status — VARCHAR(20)

EVIDENCE

  • evidence_id — INT — 기본 키 — NOT NULL
  • case_id — INT — 외래 키 → CASE_TB.case_id — NOT NULL
  • evidence_type — VARCHAR(30) — NOT NULL
  • description — VARCHAR(500) — NOT NULL
  • location_found — VARCHAR(300)
  • collected_at — DATE
  • storage_loc — VARCHAR(200)
  • status — VARCHAR(20)

EVIDENCE_PERSON

  • id — INT — 기본 키 — NOT NULL
  • evidence_id — INT — 외래 키 → EVIDENCE.evidence_id — NOT NULL
  • person_id — INT — 외래 키 → PERSON.person_id — NOT NULL
  • link_type — VARCHAR(50) — NOT NULL

INVESTIGATION_LOG

  • log_id — INT — 기본 키 — NOT NULL
  • case_id — INT — 외래 키 → CASE_TB.case_id — NOT NULL
  • detective_id — INT — 외래 키 → DETECTIVE.detective_id — NOT NULL
  • log_date — DATE — NOT NULL
  • action_type — VARCHAR(50) — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME

PERSON

  • person_id — INT — 기본 키 — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • birth_date — DATE
  • gender — VARCHAR(10)
  • memo — TEXT

SUSPECT

  • suspect_id — INT — 기본 키 — NOT NULL
  • case_id — INT — 외래 키 → CASE_TB.case_id — NOT NULL
  • person_id — INT — 외래 키 → PERSON.person_id — NOT NULL
  • suspicion_score — INT
  • motive — VARCHAR(500)
  • alibi — TEXT
  • status — VARCHAR(20)