I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site.
For example, I have a large number of questions on my site. I have a few topics, and each topic has a number of questions.
Whenever a user answers a question, I store the result in a table containing the tuples: (user_id, question_id, timesAnswered, timesCorrect)
I also have a table called
topics_and_questions containing (question_id(PK), topic_id(PK)) which can be used to determine which topic(s) the question belongs to.
Based on the above, it is possible to determine how many questions a user has answered on a particular topic. However, this would involve searching through the first table for all question_ids answered by the user, then searching the
topics_and_questions table. (The first table could get rather large)
Does it make sense to instead create another table
users_and_topics which contains (user_ID, topic_id, questionsAnswered) and update this whenever a question is answered? It would seem as though this would be bad database practise as data would essentially be replicated (this table can be derived from current tables). However, it could provide performance benefits as it would mean less work for the server. I would expect around 1 request for this information every 5 minutes per user of my website.
I'm using innoDB, MySQL database if it matters