Improve SQL strategy - denormalize in object-children-images case
- by fesja
Hi,
I have a Tour object which has many Place objects. For the list of tours, I want to show some info of the tour, the number of places of the tour, and three Place's images. Right one my SQL queries are (i'm using Doctrine with Symfony on MySQL)
get Tour
get Tour 1 places
get Tour 2 places
get Tour 3 places
...
get Tour n places
If I have a three Tour list, it's not so bad; but I'm sure it can get bad if I do a 10-20 tour-list. So, thinking on how to improve the queries I've thought of several measures:
Having a place count cache
Storing the urls of three images on a new tour field.
The problem with 2. is that if I change the image, I have to check all the tours to update that image for another one.
What solution do you think is best to scale the system in a near future? Any other suggestion.
thanks!