database: agencies with multiple locations

[josh-chamberlain]

Context

There are cases where multiple localities will share a police department. Thus, we may have localities which aren't represented in our data. Thus, if someone searches for one of those small localities which has no PD, they will not find it in the typeahead.

Let's assume that mapping the jurisdiction of police departments is our job, and allow for the complex nature of jurisdiction.

Requirements

• [ ] agencies and localities should have a linking table
  • there may be odd cases where agencies will have jurisdiction over multiple counties, especially courts; can we use the same linking table?
• [ ] POST and PUT requests will need to change to allow many-to-many agency-to-location mapping.

[maxachis]

I could envisioning us updating the POST and PUT requests to a similar structure that we have with /data-requests/{resource_id}/related-sources, but with one caveat:

• We should still retain the option for adding/requiring an initial location when originally adding an agency (except in the case of Federal jurisdictions).
• We would then modify the PUT request so that location_info can't be updated through this fashion
• To update the location in this manner, you'd need to use the /agencies/{resource_id}/related-locations endpoint, adding and removing relations.

@joshuagraber , what do you think?

[joshuagraber]

@maxachis That generally sounds good to me. One point of clarification, though:

We would then modify the PUT request so that location_info can't be updated through this fashion

Do you mean a PUT request to /agencies/{resource_id}? If so, can you explain why we wouldn't be able to update location info there and would require a separate endpoint? Are there performance or architectural considerations that make that difficult?

[maxachis]

@joshuagraber The main challenge is that location information is now normalized and centralized in the database. I describe the full process and logic here, but here's the brief for the part relevant to us:

• Previously, each component of an agency's locational information was independently specified -- you could specify the state_iso, county_fips, and municipality, and modify each at will.
• However, this made lookups more difficult and promoted inconsistencies -- we ran into an issue recently where agencies were listed with counties which were not in the states they also listed, for example.
• Now, when we add agencies, the locational information provided is checked for inconsistencies and cross-referenced with our locational data to ensure it's a valid location. In the case of localities which have a valid county and state specified but we don't have the locality in our database, we assume it's a locality we don't yet have in our database, and add it.

The issues that arise from this are:

1. It would be difficult to update an agency's locational information only partially -- if in the PUT request you include the state_iso but not county_fips or municipality, do we interpret that as meaning the latter fields are unchanged or that they should be set to NULL? What if you remove the municipality information and say its location is a county? What if the jurisdiction_type changes from State to Federal, which would imply it's not associated with any specific state-level location? I have means of addressing this in my current endpoint design, but it's imperfect and convoluted and I don't like it.
2. If we have multiple locations, these problems remain and compound, because how do we update one of multiple locations, or potentially several at once, within the PUT request?

It strikes me as easier, for both the user and the backend, to simply have it that you can add and remove locations via a separate endpoint, and leave the PUT request solely for non-locational information about the agency.

I do think it makes sense to enable the inclusion of location information in the original POST request, since most agencies will have locational information attached to them. It just becomes quite hinky if we then enable editing through a PUT request.