Re: Tables cities and table user

[Quoted] On Sun, 10 Feb 2019 12:50:23 -0500, Jerry Stuckle <jstucklex_at_attglobal.net> wrote:

>Basically you have
>
>City:
> City id (Primary key)
> City name
>
>Country
> Country id (Primary key)
> Country name
>
>City-Country
> City-Country id (Primary key)
> City id
> Country id
>
>Any reference will use the key in City-Country.

[Quoted] [Quoted] A country can have multiple cities with the same name. [Quoted] Even a province / state (and maybe even county) can have more than one city with the same name.

The introduction of a relation table City-Country assumes a n:m relationship between the two, which is only true when City name is the only attribute in City.

I would drop City-Country.

City:
  City id (Primary key)
  latitude
  longitude
  State id references StateProvince(state id)   City name
  Population
  ... other City attributes   

StateProvince
  State id (Primary key)
  State name
  Country id references Country(Country id)   ... other State attributes

Country
  Country id (Primary key)
  Country name
  ... other Country attributes

Perhaps the best identifier for a city is the location (latitude,longitude) of the city center. Alas, these are float values and thus cannot be reliably compared for equality. A solution could be to truncate latitude and longitude to an accuracy of the radius of the smalles city and converting them to integer values. Then City could have primary key (lattrunc,lontrunc).

It would be a good idea to use already available keys.

https://www.nationsonline.org/oneworld/countrycodes.htm

https://www.unece.org/cefact/locode/welcome.html

-- 
Regards,
Kees Nuyt