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)