Re: Tables cities and table user

From: Luuk <luuk_at_invalid.lan>
Date: Sat, 9 Feb 2019 20:05:03 +0100
Message-ID: <5c5f242f$0$22344$e4fe514c_at_news.xs4all.nl>


On 9-2-2019 15: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.
> 
> 

<sarcasm>
[Quoted] A properly 'normalized' database should probably also have a number_of_citizens-id, for all those cities which have the same number of citizens
</sarcasm> Received on Sat Feb 09 2019 - 20:05:03 CET

Original text of this message