0

I have a very complicated query which involve more than 10 tables. Some parent tables are loaded only to search on one field. I wonder if I can copy this field in the child table (this data is never updated), so that I don't need to load the parent table in my query.

I've always learned that I should not store the same data in two different place, but maybe it's "allowed" for performance ?

For example, imagine a country table, a city table, and a street table. Having the country code in the street table would speed up some complicated queries.

Thanks

  • Why don't you test? If having a countryID column in the street table helps you index your table and optimize the queries - say by adding an index on (countryID, cityID, streetName) which you can't do otherwise as the columns are spread in 2 or 3 tables - then go ahead. – ypercubeᵀᴹ May 7 '14 at 8:23
1

What you are doing is called denormalization.

The rule of thumb is "normalize till it hurts, denormalize till it works." Denormalization should follow the access pattern of your queries. If you have a common query that goes from joining on 10 tables to just a few with just a small bit of duplication, go for it!

| improve this answer | |
  • OK, thank you very much. I'm glad to see there is a "rule of thumb" that match my feeling :) – Matthieu May 9 '14 at 6:44
  • Especially, you gave me the right keywords to search in google (I'm french and a selflearning in db). There is a bunch of great article about this subject I can now found in seconds, like this one blog.codinghorror.com/maybe-normalizing-isnt-normal – Matthieu May 9 '14 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.