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/**Denormalization<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 > >
