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 12:21, Chris Elvidge wrote:
>>> 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
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