Re: Tables cities and table user
Date: Sun, 10 Feb 2019 12:50:23 -0500
Message-ID: <q3po90$l8t$1_at_jstuckle.eternal-september.org>
On 2/9/2019 9:38 AM, Luuk wrote:
> 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? >>>> >>>> >>>
>>> 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. > >
Yes, it is. You have London, England and London, Canada (Ontario). Two cities with the same name - that's one multi.
You also have London, England and Liverpool, England - a second multi.
But what is in the table is a city name. If you want to say it's a city, then you should have both the city name and the country name in the row.
By the same argument, what happens if they decide to rename the UK (or England) after BRExit?
But even if they do change the city name, the only change you need to the database is to add a new row for the city name and alter the link table to point to the new city name. All of the rows referencing the city will be using the id in the link table - which does not change.
Basically you have
City:
City id (Primary key)
City name
Country
Country id (Primary key)
Country name
City-Country
City-Country id (Primary key)
City id
Country id
[Quoted] Any reference will use the key in City-Country.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sun Feb 10 2019 - 18:50:23 CET