What is the best way record monthly user uploads to our web application?

Question

Our web application allows users to upload photos, it uses a PHP script on a LAMP server running on Amazon EC2 to handle image uploads, Amazon S3 to store the files and a MySQL database for recording data about the image (user id, filename, tags etc).

I intend to have an image_uploads table in the database with a colum for each month and a row for each user. That way I can track trends in uploads. There may be video uploaded too, so I would also have a video_uploads table. Additionally users may belong to a number of groups and I want to track monthly uploads per group, so would probably have a group_uploads table also. This means that for each file uploaded there would be 2 sql update queries - update image_uploads and update group_uploads.

1. $filesize = $_FILES['Filedata']['size'];
2. UPDATE image_uploads SET data_in_bytes = (data_in_bytes + $filesize) WHERE month = $current_month ... (for example - i know this is not correct sql)

Alternatively there could be a long table that records data about each upload (date, user, size, media). That way I can always query the database for the info i want - eg monthly uploads per user = sum of data_in_bytes WHERE user = ## and month = currentMonth

What are the best practices for this kind of task?

regards

Answer 1

I would keep a master log table with all of the uploades and then query the table for the dates that you want:

SELECT *
FROM logs
WHERE upload_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

Answer 2

Hi undefined.

May I ask why you need a separate group_uploads table?

Why not have a table with a schema like this: uploads(user_id, group_id, filename, type, size, date). Where "group_id" could be NULL if the user is allowed to upload files for himself (not associated to a group) and "type" would be either "video" or "image". This approach seems to be a bit more flexible in case if you want to add other file types (documents, mp3, etc).

With this schema, queries for various filters (filter by user, user and not group, user and group, by type, by date) should be simpler.

Hope that helps.