Re: Tables cities and table user
Date: Wed, 13 Feb 2019 14:04:09 -0500
Message-ID: <q41pn7$vka$1_at_jstuckle.eternal-september.org>
On 2/11/2019 12:10 PM, Kees Nuyt wrote:
> 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.
>
> A country can have multiple cities with the same name.
> 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
>
There was no indication the OP needed all of that extra information such as population. And people don't search by longitude/latitude.
Yes, you could have multiple cities with the same name in a country. However, that can easily be resolved by adding a state/province column to the link table. You cannot just add a state/province column to the city table because many countries, especially smaller ones, do not have this political division. And even those which do do not always use them in mailing addresses.
Advantages of doing it this way are one can easily search by city, state/province or country or any combination of the three.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Wed Feb 13 2019 - 20:04:09 CET