Re: Tables cities and table user
From: Chris Elvidge <chris_at_mshome.net>
Date: Sat, 9 Feb 2019 12:43:23 +0000
Message-ID: <q3mhtc$p15$1_at_dont-email.me>
>
> 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 12:43:23 +0000
Message-ID: <q3mhtc$p15$1_at_dont-email.me>
On 09/02/2019 11:40, 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
I was just trying to make the point that it's not as simple as it first looks.
-- Chris Elvidge, EnglandReceived on Sat Feb 09 2019 - 13:43:23 CET