List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:April 21 2013 2:19pm
Subject:Re: Design help
View as plain text  
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:

> 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
>
>

Thread
Design helpNeil Tompkins21 Apr
  • Re: Design helpDenis Jedig21 Apr
    • Re: Design helpNeil Tompkins21 Apr
      • RE: Design helpIlya Kazakevich21 Apr