Currently, our agencies table has multiple locational references:
state_iso, which references state_names (soon to be renamed via #443 )
county_fips, which references counties, which in turn references state_names via state_iso
county_names, which is essentially the same name as the county in counties referenced by counties_fips
county_airtable_uid, which references the old airtable identifier of county
municipality, which is by definition a subdivision of the given county.
This is a lot of redundant logic that is also sometimes redundantly referencing other redundant logic, which increases the surface area for possible errors.
Additionally, we're looking to incorporate #26 , which would involve users subscribing to specific locations (which can be on the state level, county level, or locality level). Doing that in the current setup will add to the confusion.
We also have a materialized typeahead view which references locations, and which currently references the state table, the counties table, and the agencies table, which is the only place we have distinct localities listed.
Requirements
[x] Rename state_names to us_states, to meet #443 's requirement
[x] Add an integer-based serial id primary key to us_states, us_states.id
[x] Add an integer-based serial id primary key to counties, counties.id
[x] Update counties so that it references the state by the new us_states.id
[x] Create a localities table, which includes an integer-based serial id primary key, a name, and a foreign key referencing counties.id
[x] Populate localities with all distinct state/county/municipality combinations in agencies
[x] Create a locations table, which includes a primary key id id, an enum column type indicating whether it is state, county, or locality level, and a foreign key id reference_id which can point to eitherus_state.id, counties.id, or localities.id
[x] Update locations so that it includes references to all states, counties, and localities rows
[x] Update agencies so that it only has one foreign key, referencing location_id. Remove state_iso, county_fips, county_names, county_airtable_uid, andmunicipality`
[x] Create locations_expanded view, which will, regardless of the level, include a state_iso, counties_name, and locality_name column (with those columns not needed for that location type being null). This will be used in cases where we need to reference all of this information (as in existing get requests)
[x] Update the typeahead_locations materialized view so that it derives its information from locations_expanded
[x] Update the \agencies GETlogic so that the columns pull from thelocations_expanded` view
[x] Update the \agenciesPOST logic so that:
[x] if it is given a locality, it checks to see if that locality already exists in the localities table, and adds it if not, and also adds an entry to the geo table
[x] An error is thrown if a county is provided without a state, or a locality without a county or state
Tests
Update existing tests which will surely have broke because of this
Add new tests to account for the additional logic.
Docs
Documentation should remain mostly unchanged, with the exception of GET/agencies logic removing references to the removed columns, and adding the location_id column.
Context
agenciestable has multiple locational references:state_iso, which referencesstate_names(soon to be renamed via #443 )county_fips, which referencescounties, which in turn referencesstate_namesviastate_isocounty_names, which is essentially the same name as the county incountiesreferenced bycounties_fipscounty_airtable_uid, which references the old airtable identifier of countymunicipality, which is by definition a subdivision of the given county.agenciestable, which is the only place we have distinct localities listed.Requirements
[x] Rename
state_namestous_states, to meet #443 's requirement[x] Add an integer-based serial id primary key to
us_states,us_states.id[x] Add an integer-based serial id primary key to
counties,counties.id[x] Update
countiesso that it references the state by the newus_states.id[x] Create a
localitiestable, which includes an integer-based serial id primary key, a name, and a foreign key referencingcounties.id[x] Populate
localitieswith all distinct state/county/municipality combinations inagencies[x] Create a
locationstable, which includes a primary key idid, an enum columntypeindicating whether it isstate,county, orlocalitylevel, and a foreign key idreference_idwhich can point to eitherus_state.id,counties.id, orlocalities.id[x] Update
locationsso that it includes references to allstates,counties, andlocalitiesrows[x] Update
agenciesso that it only has one foreign key, referencinglocation_id. Removestate_iso,county_fips,county_names, county_airtable_uid, andmunicipality`[x] Create
locations_expandedview, which will, regardless of the level, include astate_iso,counties_name, andlocality_namecolumn (with those columns not needed for that location type being null). This will be used in cases where we need to reference all of this information (as in existing get requests)[x] Update the
typeahead_locationsmaterialized view so that it derives its information fromlocations_expanded[x] Update the
\agenciesGETlogic so that the columns pull from thelocations_expanded` view[x] Update the
\agenciesPOSTlogic so that:localitiestable, and adds it if not, and also adds an entry to thegeotableTests
Update existing tests which will surely have broke because of this
Add new tests to account for the additional logic.
Docs
GET/agencieslogic removing references to the removed columns, and adding thelocation_idcolumn.Open questions