Re: Tables cities and table user

From: Luuk <luuk_at_invalid.lan>
Date: Sat, 9 Feb 2019 14:03:26 +0100
Message-ID: <5c5ecf6d$0$22349$e4fe514c_at_news.xs4all.nl>

On 9-2-2019 13:43, Chris Elvidge wrote:

> On 09/02/2019 11:40, Luuk wrote:

[Quoted] >> On 9-2-2019 12:21, Chris Elvidge wrote:

>>> On 09/02/2019 10:51, The Natural Philosopher wrote:
[Quoted] >>>> 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
[Quoted] >> 43    London    53

>>
>>

>> Country
>> ID    Name
>> 49    UK
[Quoted] >> 53    Canada

> 
> I was just trying to make the point that it's not as simple as it first 
> looks.
> 
> 

No, it's even simpler.

Every city has it's own unique id. This has nothing to do with the name of this city.

Besides that a 'property' of this city is that is is located in a country. That's why a cuontry-id is added to the record of the city table.

If you want to store the number of residents in that city you can also add that as a field.

finally you could get something like:
CREATE TABLE `city` (

   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `Name` char(35) NOT NULL DEFAULT '',
   `CountryCode` char(3) NOT NULL DEFAULT '',
   `District` char(20) NOT NULL DEFAULT '',
   `Population` int(11) NOT NULL DEFAULT '0',

   PRIMARY KEY (`ID`),
   KEY `CountryCode` (`CountryCode`),
   CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 (frm WORLD example database) Received on Sat Feb 09 2019 - 14:03:26 CET