# Data Dictionary
## Kampong Speu Provincial Demographic Management System (KSP-DMS)

**Document Code:** KSP-DMS-DOC-DICT-01  
**Version:** 1.0 (Draft Proposal)  
**Date:** November 2026  

---

## 1. Geographic Entity Entities

### Table: `provinces`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Unique surrogate primary key |
| `code` | VARCHAR(10) | No | None | Unique provincial code (e.g. 'KSP') |
| `name_km` | VARCHAR(100) | No | None | Official Khmer name (ខេត្តកំពង់ស្ពឺ) |
| `name_en` | VARCHAR(100) | No | None | Official English name (Kampong Speu Province) |
| `created_at` | TIMESTAMP | Yes | NULL | Record creation timestamp |
| `updated_at` | TIMESTAMP | Yes | NULL | Record last modification timestamp |
| `deleted_at` | TIMESTAMP | Yes | NULL | Soft deletion timestamp |

### Table: `districts`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Unique surrogate primary key |
| `province_id`| BIGINT UNSIGNED | No | None | FK referencing `provinces.id` |
| `code` | VARCHAR(20) | No | None | Unique district code (e.g. 'KSP-BM') |
| `name_km` | VARCHAR(100) | No | None | Official Khmer name (ក្រុងច្បារមន) |
| `name_en` | VARCHAR(100) | No | None | Official English name (Chbar Mon Municipality) |
| `type` | ENUM | No | 'district' | 'district' (ស្រុក) or 'municipality' (ក្រុង) |

### Table: `communes`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Unique surrogate primary key |
| `district_id`| BIGINT UNSIGNED | No | None | FK referencing `districts.id` |
| `code` | VARCHAR(20) | No | None | Unique commune code |
| `name_km` | VARCHAR(100) | No | None | Official Khmer name (ឃុំ/សង្កាត់) |
| `name_en` | VARCHAR(100) | No | None | Official English name |
| `type` | ENUM | No | 'commune' | 'commune' (ឃុំ) or 'sangkat' (សង្កាត់) |

### Table: `villages`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Unique surrogate primary key |
| `commune_id` | BIGINT UNSIGNED | No | None | FK referencing `communes.id` |
| `code` | VARCHAR(20) | No | None | Unique village code |
| `name_km` | VARCHAR(100) | No | None | Official Khmer name (ភូមិ) |
| `name_en` | VARCHAR(100) | No | None | Official English name |

---

## 2. Demographic Entities

### Table: `households`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Primary Key |
| `household_code` | VARCHAR(32) | No | None | Unique business key (e.g. 'HH-KSP-00123') |
| `village_id` | BIGINT UNSIGNED | No | None | FK referencing `villages.id` |
| `address_line` | VARCHAR(255) | Yes | NULL | Local street/group descriptive address |
| `household_type`| ENUM | No | 'family' | 'family', 'single', 'collective', 'institutional' |
| `economic_status`| ENUM | No | 'standard'| 'standard', 'idpoor_1', 'idpoor_2' (Synthetic) |
| `head_of_household_id` | BIGINT UNSIGNED | Yes | NULL | FK referencing `persons.id` |
| `registration_date` | DATE | No | Current Date| Date of official household book issuance |
| `status` | ENUM | No | 'active' | 'active', 'archived', 'dissolved' |

### Table: `persons`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Primary Key |
| `person_code` | VARCHAR(32) | No | None | Unique citizen system code |
| `village_id` | BIGINT UNSIGNED | No | None | Current administrative residence village |
| `full_name_km` | VARCHAR(150) | No | None | Citizen full name in Khmer script |
| `full_name_en` | VARCHAR(150) | No | None | Citizen full name in Latin alphabet |
| `gender` | ENUM | No | None | 'male', 'female', 'other' |
| `date_of_birth`| DATE | No | None | Official Gregorian date of birth |
| `national_id_number` | VARCHAR(64) | Yes | NULL | Synthetic placeholder identification string |
| `marital_status`| ENUM | No | 'single' | 'single', 'married', 'widowed', 'divorced' |
| `is_alive` | BOOLEAN | No | 1 (true) | Vital status flag |
| `current_status`| ENUM | No | 'resident'| 'resident', 'temporary_absent', 'migrated', 'deceased' |

### Table: `household_members`
| Column | Type | Nullable | Default | Description |
| :--- | :--- | :---: | :--- | :--- |
| `id` | BIGINT UNSIGNED | No | AUTO_INCREMENT | Primary Key |
| `household_id` | BIGINT UNSIGNED | No | None | FK referencing `households.id` |
| `person_id` | BIGINT UNSIGNED | No | None | FK referencing `persons.id` |
| `relationship_to_head` | ENUM | No | 'head' | 'head', 'spouse', 'child', 'parent', 'sibling', 'grandchild', 'other' |
| `is_head` | BOOLEAN | No | 0 (false)| Designates active head of household |
| `start_date` | DATE | No | None | Date joined household roster |
| `end_date` | DATE | Yes | NULL | Date left household roster |
| `residency_status`| ENUM | No | 'permanent'| 'permanent', 'temporary_absent', 'moved_out', 'deceased' |
