Geography: Tracking Active Status over Time

[lbaertlein1]

Problem: When a geography element (e.g. Region/Province/District/Cluster) is edited, its previous values are not retained – e.g. if Kandahar district is split into two districts, one that is created by editing the prior district name from “Kandahar” to “Kandahar East”, only “Kandahar East” is retained in the system. Pulling data from a past campaign, prior to the split, will label the district as “Kandahar East”. Similarly, if a geography element is ‘archived’ or ‘de-archived’, there is no record retained of when it was active or archived. Thus, there is no way to extract a set of elements that were active during any specified campaign. This makes measuring reporting completeness difficult, as there is no reliable record of how many or which clusters were active during any specific campaign – and therefore no way to measure how many clusters were expected to have data reported.

Proposed Solution: Create new geography tables that they contain one row per area per active time period. The current geography tables could continue to be used as-is, but could be linked to the new table by geography uuid. These new tables could be used to obtain a list of clusters active at any given point in time or during any given campaign. The active time periods should be automatically updated whenever the area’s active/archived status is changed. An example diagram of this new table for clusters and how it could be linked to the current cluster table is below, but the revisions would be needed for region, province, and district as well.

Suggested New Table:
Suggested new table, and connection to current cluster table: In this example, the following scenarios are described:
• Cluster 1: Created on 1/1/2024. Archived on 3/1/2024. Re-activated on 5/1/2024, and is still active today.
• Cluster 2: Created on 1/1/2024. Archived on 3/1/2024. Re-activated on 5/1/2024. Archived again on 7/1/2024. Re-activated again on 9/1/2024. Archived again on 10/1/2024, and is archived today.
• Cluster 3: Created on 1/1/2024 and is still active today.
• Cluster 4: Created on 1/1/2024 and archived on 3/1/2024, and is archived today.