# Relational Database Architecture and Design Document
## Kampong Speu Provincial Demographic Management System (KSP-DMS)

**Document Code:** KSP-DMS-DOC-DBD-01  
**Database Management System:** MySQL / MariaDB 10.4.32 (XAMPP Default)  
**Storage Engine:** InnoDB (Strict Foreign Keys, ACID Compliance)  
**Default Collation:** `utf8mb4_unicode_ci` (Full Unicode Khmer Character Support)  
**Version:** 1.0 (Draft Proposal)  

---

## 1. Database Architecture Principles

1. **Normalization (3NF):** Core entities are strictly normalized to eliminate update anomalies and data redundancies.
2. **Referential Integrity:** Foreign keys enforce strict relational integrity. Deletions on geographic entities and core persons are soft-deleted or restricted (`ON DELETE RESTRICT`) to prevent orphaned records.
3. **Temporal History Tracking:** Membership in a household is tracked via an associative junction table (`household_members`) with `start_date`, `end_date`, and `residency_status`.
4. **Audit Immutability:** An append-only `audit_logs` table records every mutation with actor, timestamp, old state, and new state.
5. **ACID Transactions:** All workflow transitions, vital event approvals, and household splits/merges execute within isolated database transactions.

---

## 2. Entity-Relationship Overview

```
 [provinces]
      | 1
      |---< [districts]
                 | 1
                 |---< [communes]
                            | 1
                            |---< [villages]
                                       | 1
                                       |---< [households]
                                                  | 1
                                                  |---< [household_members] >---|
                                                                                |
                                       | 1                                      | *
                                       |---< [persons] -------------------------|
                                                  | 1
                                                  |---< [birth_events] (as child/parent)
                                                  |---< [death_events] (as deceased)
                                                  |---< [migration_events] (as migrant)
```

---

## 3. Comprehensive Table Specifications

### 3.1 Geographic Hierarchy Tables
- **`provinces`:** `id`, `code` (e.g. 'KSP'), `name_km`, `name_en`, `created_at`, `updated_at`, `deleted_at`.
- **`districts`:** `id`, `province_id`, `code` (e.g. 'KSP-BM'), `name_km`, `name_en`, timestamps, soft deletes.
- **`communes`:** `id`, `district_id`, `code`, `name_km`, `name_en`, timestamps, soft deletes.
- **`villages`:** `id`, `commune_id`, `code`, `name_km`, `name_en`, timestamps, soft deletes.

### 3.2 Demographic Core Tables
- **`households`:**
  - `id` (BIGINT UNSIGNED, Primary Key)
  - `household_code` (VARCHAR(32), UNIQUE, e.g. 'HH-KSP-BM-00001')
  - `village_id` (BIGINT UNSIGNED, FK -> villages.id)
  - `address_line` (VARCHAR(255))
  - `household_type` (ENUM: 'family', 'single', 'collective', 'institutional')
  - `economic_status` (ENUM: 'standard', 'idpoor_1', 'idpoor_2') - *Synthetic indicator*
  - `head_of_household_id` (BIGINT UNSIGNED, NULLABLE FK -> persons.id)
  - `registration_date` (DATE)
  - `status` (ENUM: 'active', 'archived', 'dissolved')
  - Timestamps, Soft Deletes.
- **`persons`:**
  - `id` (BIGINT UNSIGNED, Primary Key)
  - `person_code` (VARCHAR(32), UNIQUE, e.g. 'P-KSP-0000001')
  - `village_id` (BIGINT UNSIGNED, FK -> villages.id)
  - `full_name_km` (VARCHAR(150), Khmer text)
  - `full_name_en` (VARCHAR(150), Latin text)
  - `gender` (ENUM: 'male', 'female', 'other')
  - `date_of_birth` (DATE)
  - `national_id_number` (VARCHAR(64), NULLABLE, UNIQUE) - *Synthetic demo value*
  - `marital_status` (ENUM: 'single', 'married', 'widowed', 'divorced')
  - `is_alive` (BOOLEAN, DEFAULT true)
  - `current_status` (ENUM: 'resident', 'temporary_absent', 'migrated', 'deceased')
  - Timestamps, Soft Deletes.
- **`household_members`:**
  - `id` (BIGINT UNSIGNED, Primary Key)
  - `household_id` (BIGINT UNSIGNED, FK -> households.id)
  - `person_id` (BIGINT UNSIGNED, FK -> persons.id)
  - `relationship_to_head` (ENUM: 'head', 'spouse', 'child', 'parent', 'sibling', 'grandchild', 'other')
  - `is_head` (BOOLEAN, DEFAULT false)
  - `start_date` (DATE)
  - `end_date` (DATE, NULLABLE)
  - `residency_status` (ENUM: 'permanent', 'temporary_absent', 'moved_out', 'deceased')
  - Timestamps.

### 3.3 Vital Events Tables
- **`birth_events`:**
  - `id`, `event_code`, `person_id` (Child), `household_id`, `mother_id`, `father_id`, `date_of_birth`, `place_of_birth`, `reported_by`, `status` (draft/pending/approved/rejected), `submission_id`, timestamps.
- **`death_events`:**
  - `id`, `event_code`, `person_id`, `household_id`, `date_of_death`, `cause_category`, `place_of_death`, `death_certificate_no`, `status`, `submission_id`, timestamps.
- **`migration_events`:**
  - `id`, `event_code`, `person_id`, `household_id`, `migration_type` ('in', 'out'), `origin_address`, `destination_address`, `migration_date`, `reason`, `status`, `submission_id`, timestamps.

### 3.4 Governance & Audit Tables
- **`data_submissions`:** `id`, `batch_code`, `user_id`, `administrative_level`, `entity_id`, `status` ('draft', 'submitted_commune', 'verified_district', 'approved', 'rejected'), `rejection_reason`, `submitted_at`, `approved_at`, timestamps.
- **`data_submission_items`:** `id`, `submission_id`, `item_type` (model class), `item_id`, `action` ('create', 'update', 'delete'), `payload_json`.
- **`audit_logs`:** `id`, `user_id`, `action`, `model_type`, `model_id`, `old_values` (JSON), `new_values` (JSON), `ip_address`, `user_agent`, `created_at`.
- **`data_quality_issues`:** `id`, `issue_type`, `severity` ('info', 'warning', 'critical'), `model_type`, `model_id`, `description`, `is_resolved`, `resolved_by`, timestamps.
- **`map_boundary_versions`:** `id`, `administrative_type`, `entity_id`, `geojson_data` (LONGTEXT), `version`, `effective_year`, timestamps.
- **`system_settings`:** `id`, `setting_key` (UNIQUE), `setting_value`, `description`, timestamps.

---

## 4. Indexing Strategy for High Performance

```sql
-- Core Geographic Lookups
CREATE INDEX idx_districts_province ON districts(province_id);
CREATE INDEX idx_communes_district ON communes(district_id);
CREATE INDEX idx_villages_commune ON villages(commune_id);

-- Demographic Filtering & Aggregations
CREATE INDEX idx_households_village ON households(village_id, status);
CREATE INDEX idx_persons_village ON persons(village_id, is_alive);
CREATE INDEX idx_persons_dob_gender ON persons(date_of_birth, gender);
CREATE INDEX idx_household_members_lookup ON household_members(household_id, person_id, is_head);

-- Governance & Auditing
CREATE INDEX idx_audit_logs_model ON audit_logs(model_type, model_id, created_at);
CREATE INDEX idx_submissions_status ON data_submissions(status, user_id);
```
