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!
storing and calculating website stats in db
Moderator: General Moderators
-
mechamecha
- Forum Commoner
- Posts: 32
- Joined: Thu May 31, 2007 8:49 pm
I don't understand why you're trying to make a complicated system simply to count how many views a photo has had. Just put a count column in the photo table, and increment it whenever a photo is viewed... eg
The only reason to put the count in another table would be if you want to know when the photo was downloaded, or who downloaded it.
Code: Select all
UPDATE `photo` SET `photo`.`counter` = `photo`.`counter` + 1 WHERE `photo`.`photo_id` = 1-
mechamecha
- Forum Commoner
- Posts: 32
- Joined: Thu May 31, 2007 8:49 pm
onion2k,
THanks for the reply!
I like your solution. It's much simpler. However I also want to save the accumulated total views for each day. This way I could easily compute and display the new number of views a photo has recieved. I think I could use your solution in conjunction w/ an agent that runs daily and stores each row from the photo_views table into another table.
What do you think?
THanks for the reply!
I like your solution. It's much simpler. However I also want to save the accumulated total views for each day. This way I could easily compute and display the new number of views a photo has recieved. I think I could use your solution in conjunction w/ an agent that runs daily and stores each row from the photo_views table into another table.
What do you think?
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Simple solution would be to have the previous solution with a date. A lot depends on the amount of traffic. I would be tempted to store just a photo_id and timestamp. This could be used to actually provide more details such as times when photos were viewed but could lead to a large table if there is high traffic. The other solution would be to create a photo_id/counter/date if it does not exist or update the counter based on date. You could do this by trying an update first. If mysql_affected_rows returns 0, create it.