In the process of rewriting my site (including reorganizing and updating the db structure) and I'm looking to optimize in a few areas.
I am looking for some thoughts on the best way to store data then retrieve and display it efficiently. To get things started lets discuss a often debated topic!
Here is an example to illustrate my first basic question:
Picture it: A Questions & Answers Section
DB: You have a table for Questions, one for Answers.
SITE: In the list of questions it shows the question title (the question, who posted it, when it was posted etc.) it also shows the current number of answers. You then click to go view the answers.
Things to consider: tens of thousands of requests per day
Now for my question:
Is it best to calculate the number of answers whenever the list of questions is generated (through a join) or is it best to keep a field (column) in the question record for the number of answers (and update this number when an answer is added or deleted)? The first is more intensive but always accurate, the second is more efficient but relies on manual updating which can lead to inaccurate data...
This question applies to so many different parts of a web application. I've done both over the years, but honestly have mixed feelings about them both. One offends my sense of speed, the other my need for a clean, accurate database.
Which is best and why? Perhaps there is even a third (and forth) method that I haven't considered?
Thanks in advance for you thoughts.