Unanswered: Updating Record "Count" Vs Multiple Records
My first post in these forums, I hope I can give something back to others in the future
I have a question regarding database practice for activity tracking.
I would like to keep track of how many times a product has been viewed, added to basket, how many times a search term has been used etc.
I've never been able to decide the best way of doing this. The way I see it, there are two ways of doing this:
* Increment a "count" column to keep a running total of the number of times the event has occurred.
* Insert a new record every time the event occurs then use a view or determine the COUNT() on-the-fly.
I've tended to opt for the latter option as this allows most flexibility in terms of keeping additional info alongside the event entry such as when the event occurred, which user produced the event. The problem though is the table rapidly gets very large, exponentially larger than the former option.
So what method do people think is best in most situations?