Re: Tables cities and table user
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sat, 9 Feb 2019 09:18:32 -0500
Message-ID: <q3mnfa$q8b$1_at_jstuckle.eternal-september.org>
>
> The city names would be in the table twice (with their own unique ID),
> and with a different country ID.
>
> City
> ID Name CountryId
> 42 London 49
> 43 London 53
>
>
> Country
> ID Name
> 49 UK
> 53 Canada
Date: Sat, 9 Feb 2019 09:18:32 -0500
Message-ID: <q3mnfa$q8b$1_at_jstuckle.eternal-september.org>
[Quoted] On 2/9/2019 6:40 AM, Luuk wrote:
> On 9-2-2019 12:21, Chris Elvidge wrote:
>> On 09/02/2019 10:51, The Natural Philosopher wrote: >>> On 09/02/2019 09:50, ^Bart wrote: >>>>> SELECT id_city,name,'IT' from cities_it >>>>> union all >>>>> SELECT id_city,name,'UK' from cities_uk >>>> >>>> I didn't understand what happen in the cities table when you use the >>>> union all command... >>>> >>>>> or, even better (before someone else askes this question): >>>>> Why do you have mulitple tables with city names? >>>> >>>> Because I think to store all cities from more or less ten countries >>>> or the entire world it's too heavy to to it just in one table! >>>> >>> >>> I dont think so. >>> >>> You need a table of countries and a table of cities. >>> >>> In the city record you have a field that points to the country unique ID >>> >>> If you are worried about lookup speed, index it >>> >>> >>>> ^Bart >>>> >>> >>> >> >> Ah, but how do you differentiate between (e.g.) London, UK and London, >> Ontario, Canada? St Petersburg, Russia and St Petersburg, Florida, USA? >> >>
>
> The city names would be in the table twice (with their own unique ID),
> and with a different country ID.
>
> City
> ID Name CountryId
> 42 London 49
> 43 London 53
>
>
> Country
> ID Name
> 49 UK
> 53 Canada
[Quoted] That would fail normalization rules.
What you have is a many-to-many (many city names related to many country names). The solution here is to have a link table with city_id and country_id.
This allows you to search for all country names containing a city name, or all city names in a country name.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sat Feb 09 2019 - 15:18:32 CET