Updating Record "Count" Vs Multiple Records

d418rf

Hi All,

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?

Thanks,
d418rf

bretticus

Yeah, I understand that dilemma. I would agree that I also prefer a record for each occurrence. That way you can build a date profile or other statistics that you don't get with a count. Perhaps the best method is a compromise. In other words, I think I might do both. You can always clean out older records or summarize them after a specific amount of time has transpired. However, you'll have a count that will be there indefinitely getting larger. A thought on a counter I just had would be to write your query in such a way that you INSERT a new record for a new month and UPDATE the count until your business logic determines that you're counting in a new month and the cycle starts over. This way your counting is a little more useful and 12 records a year is pretty minimal as well.

Just some thoughts...

Good luck.

walterquez

I think creating a log table will be the best answer. Create a function that can do something like

save_activity('view', $product_id);
save_activity('basket', $product_id);
save_activity('search', $term);

And have this function save it to a table with a timestamp. It can have fields like; activity, value, created.

With this you can later run report based on date ranges.

d418rf

Thanks for the replies.

It's good to see that you both think my current method is the most suitable (a new insert for each occurrence of an event).

It's a interesting idea you out forward, bretticus; I think I'll insert a new record for each event occurrence. If the speed becomes an issue after a while I can use the idea you mentioned, summarising the data into a table for each time period (e.g. monthly).

Thanks,
d418rf