Re: Tables cities and table user

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

On 09/02/2019 14:38, Luuk wrote:
> On 9-2-2019 15:18, Jerry Stuckle wrote:
>> 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?
>>>>>>
[Quoted] >>>>>> 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
[Quoted] >>> ID), and with a different country ID.

>>>

>>> City
>>> ID    Name    CountryId
>>> 42    London    49
>>> 43    London    53

>>>
>>>

>>> Country
>>> ID    Name
>>> 49    UK
>>> 53    Canada

>>
>> 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.
>>
> 
> I do not think it's true.
> 
> If those UK people decide to rename 'London' after the brexit, this 
> could be done real easy, without the need top update any id.
> 
> 

I cannot conceive of a unique city belonging to more than one country.

So there is no need for many-to-many links

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

Received on Sun Feb 10 2019 - 12:33:33 CET