Re: Tables cities and table user

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sun, 10 Feb 2019 12:51:41 -0500
Message-ID: <q3pobd$l8t$2_at_jstuckle.eternal-september.org>

On 2/10/2019 6:33 AM, The Natural Philosopher wrote:

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

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

It's not the city - it's the city name, and it is not unique.

But then we already know you know nothing about database design - or programming. That's why you're afraid to use your real name. You don't want people to know you're an out-of-work ditch digger.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================

Received on Sun Feb 10 2019 - 18:51:41 CET