Yes, there are too many extra columns in the Physical Location table to have it also contain the Coordinate and be referenced by other tables that just need a Coordinate. Having Coordinates in their own table keeps the DB more normalized.
- Rawlin On Thu, Jun 14, 2018 at 11:37 AM, Steve Malenfant <[email protected]> wrote: > Using Physical Location to hold the coordinate. Maybe there is just too > much information in that table just to hold coordinate, but it seems sort > of a duplicate. > > On Thu, Jun 14, 2018 at 1:35 PM Rawlin Peters <[email protected]> > wrote: > >> Hey Steve, >> >> Do you mean adding a CoordinateID foreign key in the Physical Location >> table? So that a Physical Location also has a Coordinate? Or tying >> Physical Location to a Cachegroup? >> >> - Rawlin >> >> On Thu, Jun 14, 2018 at 10:41 AM, Steve Malenfant <[email protected]> >> wrote: >> > Rawlin, >> > >> > Anyway we could leverage the Physical Location table for this? Just a >> > thought. >> > >> > Steve >> > >> > On Thu, Jun 14, 2018 at 11:45 AM Rawlin Peters <[email protected]> >> > wrote: >> > >> >> Hey Traffic Controllers, >> >> >> >> Recently I added a Coordinate API to Traffic Ops [1]. With that, we >> >> now have a coordinate table in the database, so we have the ability to >> >> refactor some API backends that use lat/long pairs to use the >> >> coordinate table instead, such as the Cachegroups API. >> >> >> >> My proposal is to keep the Cachgroup API as-is from the client >> >> perspective but update the DB schema by adding a Foreign Key (nullable >> >> because lat/long are optional) to the cachegroup table that references >> >> the coordinate table and removing the lat/long columns from the >> >> cachegroup table. For the API backend this means: >> >> >> >> POST: >> >> 1. create a row in the coordinate table from latitude/longitude in the >> >> request (skip this step if no lat/long in the request) >> >> 2. create a row in the cachegroup table with a FK to the coordinate >> >> row in step 1 >> >> >> >> PUT: >> >> 1. update columns in the cachegroup row >> >> 2. update lat/long in the coordinate row referenced by the cachegroup >> >> >> >> GET: >> >> 1. join the cachegroup and coordinate table on >> >> cachegroup.coordinate_id = coordinate.id to return coordinates in the >> >> response >> >> >> >> DELETE: >> >> 1. delete the coordinate row referenced by the cachegroup >> >> 2. delete the cachegroup row >> >> >> >> One hitch is that we need a unique name for the coordinate created >> >> from the cachegroup POST. This name doesn't have to be returned in the >> >> Cachegroup response, but I was thinking of just forming the name as >> >> cg_<cachegroupName>. >> >> >> >> Any objections, thoughts, or concerns? >> >> >> >> - Rawlin >> >> >> >> P.S. the Delivery Service API (missLat and missLong) could also go >> >> through this same pattern of refactoring too, but first I'll take care >> >> of Cachegroups. >> >> >> >> [1] >> >> >> http://traffic-control-cdn.readthedocs.io/en/latest/api/v13/coordinate.html >> >> >>
