Re: Tables cities and table user

From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Sun, 10 Feb 2019 11:31:44 +0000
Message-ID: <q3p230$4up$3_at_dont-email.me>

On 09/02/2019 12:43, Chris Elvidge wrote:

> On 09/02/2019 11:40, Luuk wrote:

[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?
>>>
>>>
>>

>> 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.
> 
> 

No. Its simpler.

-- 
The New Left are the people they warned you about.

Received on Sun Feb 10 2019 - 12:31:44 CET