Missing Persons Case Database

erDiagram
  POLICE_STATION ||--o{ MISSING_CASE : registers
  REPORTER ||--o{ MISSING_CASE : files
  MISSING_PERSON ||--|| MISSING_CASE : "subject of"
  MISSING_CASE ||--o{ SIGHTING : receives
  MISSING_CASE ||--o{ CASE_UPDATE : has
  MISSING_CASE ||--o{ POSSIBLE_MATCH : "compared in"
  FOUND_PERSON ||--o{ POSSIBLE_MATCH : "compared in"
  POLICE_STATION {
    int id PK
    string name
    string district
  }
  REPORTER {
    int id PK
    string name
    string phone
    string relation
  }
  MISSING_PERSON {
    int id PK
    string name
    int age
    string description
    string photo_url
  }
  MISSING_CASE {
    int id PK
    int station_id FK
    int reporter_id FK
    int person_id FK
    date missing_since
    string status
  }
  SIGHTING {
    int id PK
    int case_id FK
    string place
    datetime seen_at
  }
  CASE_UPDATE {
    int id PK
    int case_id FK
    string note
    datetime updated_at
  }
  FOUND_PERSON {
    int id PK
    string place_found
    date found_on
    string photo_url
  }
  POSSIBLE_MATCH {
    int id PK
    int case_id FK
    int found_person_id FK
    decimal score
    string review_status
  }