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.