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 <dj@stripped> wrote:
> 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
>> 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
> Denis Jedig
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql