Re: Tables cities and table user
From: Luuk <luuk_at_invalid.lan>
Date: Sat, 9 Feb 2019 15:38:55 +0100
Message-ID: <5c5ee5cb$0$22340$e4fe514c_at_news.xs4all.nl>
>
> 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.
>
Date: Sat, 9 Feb 2019 15:38:55 +0100
Message-ID: <5c5ee5cb$0$22340$e4fe514c_at_news.xs4all.nl>
On 9-2-2019 15:18, Jerry Stuckle wrote:
> On 2/9/2019 6:40 AM, 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? >>> >>> >> [Quoted] [Quoted] >> The city names would be in the table twice (with their own unique ID), >> and with a different country ID. >> >> City [Quoted] [Quoted] >> ID Name CountryId [Quoted] >> 42 London 49 >> 43 London 53 >> >> >> Country >> ID Name [Quoted] >> 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. Received on Sat Feb 09 2019 - 15:38:55 CET