List:General Discussion« Previous MessageNext Message »
From:Denis Jedig Date:April 21 2013 8:28am
Subject:Re: Design help
View as plain text  
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
Thread
Design helpNeil Tompkins21 Apr
  • Re: Design helpDenis Jedig21 Apr
    • Re: Design helpNeil Tompkins21 Apr
      • RE: Design helpIlya Kazakevich21 Apr