storing and calculating website stats in db
Posted: Mon Sep 03, 2007 2:43 am
Hello,
I'm trying to implement a mechanism that tracks the number of times a user's photo is viewed. I would like to then display this number for the users to see. This the design I've come up with:
I'll have a table that looks like this:
CREATE TABLE `photo_views` (
`user_id` bigint(20) NOT NULL
`photo_id` bigint(20) NOT NULL,
`count` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`)
)
Each time a photo link is clicked, the site will store the user_id that the photo belongs to, the photo's id, and a 1 for the count.
In the background, I would have an agent scheduled to periodically run(once a day is sufficient). This agent will traverse the photo_views table and calculate the sum of all 'count' values for each user_id and photo_id. Then it would delete all rows that it used to calculate the sum and write a single row back into the table w/ the calculated sum in the 'count' column. It would also save the sum in another table, let' say its called 'count_photo_views'.
Between the summing and deletion sql operations that the agent performs, I would need to lock the table from modification(I'm assuming this is not too hard to do....).
Every time a user wants to display the number of times a photo is viewed, the site would query the 'count_photo_views' table to get this value.
With this design, I hope to keep the photo_views table as compact as possible and the sql calculation that performs the summation as quick as possible. What do you guys think? Is there a better alternative?
Thanks!
I'm trying to implement a mechanism that tracks the number of times a user's photo is viewed. I would like to then display this number for the users to see. This the design I've come up with:
I'll have a table that looks like this:
CREATE TABLE `photo_views` (
`user_id` bigint(20) NOT NULL
`photo_id` bigint(20) NOT NULL,
`count` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`)
)
Each time a photo link is clicked, the site will store the user_id that the photo belongs to, the photo's id, and a 1 for the count.
In the background, I would have an agent scheduled to periodically run(once a day is sufficient). This agent will traverse the photo_views table and calculate the sum of all 'count' values for each user_id and photo_id. Then it would delete all rows that it used to calculate the sum and write a single row back into the table w/ the calculated sum in the 'count' column. It would also save the sum in another table, let' say its called 'count_photo_views'.
Between the summing and deletion sql operations that the agent performs, I would need to lock the table from modification(I'm assuming this is not too hard to do....).
Every time a user wants to display the number of times a photo is viewed, the site would query the 'count_photo_views' table to get this value.
With this design, I hope to keep the photo_views table as compact as possible and the sql calculation that performs the summation as quick as possible. What do you guys think? Is there a better alternative?
Thanks!