Data model
24 tables in third normal form. The water_sources view rebuilds the original flat records from them.
basins 9 rows
- id PK integer
- name text
extraction_type_classes 7 rows
- id PK integer
- name text
extraction_type_groups 13 rows
- id PK integer
- name text
- class_id FK → extraction_type_classes.id
extraction_types 17 rows
- id PK integer
- name text
- group_id FK → extraction_type_groups.id
lgas 123 rows
- id PK integer
- name text
- region_id FK → regions.id
management_groups 5 rows
- id PK integer
- name text
management_types 12 rows
- id PK integer
- name text
- management_group_id FK → management_groups.id
organizations 842 rows
- id PK integer
- name text
payments 7 rows
- id PK integer
- name text
- type text
quality_groups 6 rows
- id PK integer
- name text
quantities 5 rows
- id PK integer
- name text
recorders 1 row
- id PK integer
- name text
regions 21 rows
- id PK integer
- name text
scheme_management_types 11 rows
- id PK integer
- name text
schemes 764 rows
- id PK integer
- name text
source_classes 3 rows
- id PK integer
- name text
source_types 7 rows
- id PK integer
- name text
- class_id FK → source_classes.id
sources 10 rows
- id PK integer
- name text
- type_id FK → source_types.id
subvillages 2,526 rows
- id PK integer
- name text?
- ward_id FK → wards.id
- region_code integer
- district_code integer
wards 1,284 rows
- id PK integer
- name text
- lga_id FK → lgas.id
water_points 2,720 rows
- id PK integer
- name text
- date_recorded text
- recorder_id FK → recorders.id
- amount_tsh real
- gps_height integer
- longitude real
- latitude real
- num_private integer
- basin_id FK → basins.id
- subvillage_id FK → subvillages.id
- population integer
- public_meeting integer?
- funder_id FK → organizations.id
- installer_id FK → organizations.id
- scheme_id FK → schemes.id
- scheme_management_type_id FK → scheme_management_types.id
- permit integer?
- construction_year integer
- extraction_type_id FK → extraction_types.id
- management_type_id FK → management_types.id
- payment_id FK → payments.id
- water_quality_id FK → water_qualities.id
- quantity_id FK → quantities.id
- source_id FK → sources.id
- waterpoint_type_id FK → waterpoint_types.id
water_qualities 7 rows
- id PK integer
- name text
- quality_group_id FK → quality_groups.id
waterpoint_type_groups 5 rows
- id PK integer
- name text
waterpoint_types 6 rows
- id PK integer
- name text
- group_id FK → waterpoint_type_groups.id
water_sources view
- id integer
- amount_tsh real
- date_recorded text
- funder text
- gps_height integer
- installer text
- longitude real
- latitude real
- wpt_name text
- num_private integer
- basin text
- subvillage text
- region text
- region_code integer
- district_code integer
- lga text
- ward text
- population integer
- public_meeting integer
- recorded_by text
- scheme_management text
- scheme_name text
- permit integer
- construction_year integer
- extraction_type text
- extraction_type_group text
- extraction_type_class text
- management text
- management_group text
- payment text
- payment_type text
- water_quality text
- quality_group text
- quantity text
- quantity_group text
- source text
- source_type text
- source_class text
- waterpoint_type text
- waterpoint_type_group text