Design help
Hi I'm creating the following basic tables COUNTRIES countries_id name REGIONS region_id countries_id name CITIES cities_id region_id Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? Thanks Neil
Re: Design help
Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design help
Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? On Sun, Apr 21, 2013 at 9:28 AM, Denis Jedig d...@syneticon.net wrote: Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/**Denormalizationhttp://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Design help
Hello, Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? I do not think you need surrogate key for country. Country code is 2 letters, so if you use char(2) charset ASCII you only need 2 bytes for that. That is ok and your queries would be easier to read. You do not need surrogate keys for US states also but if you speak about states in general you may need key (many countries over the world have states) The same is about cities: city name is too big to be used as primary key, and there may be many cities with similar names. Ilya. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
