Re: Tables cities and table user
From: Luuk <luuk_at_invalid.lan>
Date: Sat, 9 Feb 2019 12:40:20 +0100
Message-ID: <5c5ebbf0$0$22343$e4fe514c_at_news.xs4all.nl>
>> 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
>>>
>>
>>
Date: Sat, 9 Feb 2019 12:40:20 +0100
Message-ID: <5c5ebbf0$0$22343$e4fe514c_at_news.xs4all.nl>
[Quoted] 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? > >
[Quoted] [Quoted] 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 CanadaReceived on Sat Feb 09 2019 - 12:40:20 CET