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