Storing User-uploaded Images

Question

What is the usual practice for handling user uploaded photos and storing them on the database and server?

For a user profile image:

1. After receiving the image file from user, rename file to <image_id>_<username>
2. Move image to /images/userprofile
3. Add img filename to a table users containing their profile details like first_name, last_name, age, gender, birthday

For a image for a review done by user:

1. After receiving the image file from user, rename file to <image_id>_<review_id>
2. Move image to /images/reviews
3. Add img filename to a table reviews containing their profile details like review_id, review_content, user_id, score.

Question 1: How should I go about storing the image filenames if the user can upload multiple photos for a particular review? Serialize?

Question 2: Or have another table review_images with columns review_id, image_id, image_filename just for tracking images? Will doing a JOIN when retriving the image_filename from this table slow down performance noticeably?

Question 3: Should all the images be stored in a single folder? Will there be a problem when we have 100K photos in the same folder?

Is there a more efficient way to go about doing this?

Answer 1

Short answer: no need store the image filename in the database. Just rename the file they upload to theirusername.jpg and store it in /uploads/users/. Longer answer below.

---

Question 1: How should I go about storing the image filenames if the user can upload multiple photos for a particular review? Serialize?

You could:

1. Comma-separate the filenames and store them in a single images field in the review table. When you retrieve images for display, split the images string by comma and loop through that array to lay out the images. (If there's only one image, this will still work.)

2. Create a separate table called images or review_images and store each image on its own row, as you suggest in your second question.

Question 2: Or have another table review_images with columns review_id, image_id, image_filename just for tracking images? Will doing a JOIN when retriving the image_filename from this table slow down performance noticeably?

You're unlikely to take a noticeable performance hit for using a simple JOIN. Any small performance hits you do take could be negated or reduced by caching queries and serving static HTML.

Question 3: Should all the images be stored in a single folder?

I would suggest that you either:

1. Create a single folder for each user in your /uploads/ directory when they upload their first file.
2. Create a new folder each month (automatically as part of your upload script), and upload images there. This is what WordPress does by default.

This reduces the chance of hitting directory limits on the number of files.

Will there be a problem when we have 100K photos in the same folder?

Possibly, depending upon the filesystem your server uses. There are limits on the number of files you can have in a single directory. FAT32, for example, has a limit of 65,535 files per folder. See "How many files in a directory is too many?" on Stack Overflow. Note that using folders as I suggest above reduces the danger of hitting directory limits.

Is there a more efficient way to go about doing this?

You don't need to store image data in your database at all if you follow a simple convention. When a user uploads a profile image, for example, you could rename it to profile.jpg, then store it in /uploads/users/username/. Now you only need the user's username to grab their profile image. You don't need to store a reference to the image in the database any more. (Or you could just name the image theirusername.jpg and store it in /uploads/users/ -- pick whatever convention makes sense to you.)

Likewise, when the user uploads images for a review, you could store those images in a folder called /uploads/reviews/13/. The number 13 at the end would be the review ID -- the unique ID of that review as it's stored in the database table. To display those images, the review ID is all you'd need to retrieve those images. (To find out what images are in a folder, you can scan the directory. With PHP, you use scandir(), for example.)

Answer 2

Store the information about the image in the database (username, image id, filename) and grab this with a JOIN.

This thread on Stack Overflow discusses the same idea, although I couldn't find the exact thread I wanted to reference, which gives better explanations. Michael Andrews explains in his blog some of the actual practice of how to implement a scheme like this...

The basic idea is that you store the image by hashing the image contents and using that hash as the filename.

• Let's say your user uploads 'LazyCat.jpg' as their image.
• The SHA1 hash for that image could be 'ac17c9e81fc3789ab6b8c1f3325d789e9aa3daaf'
• On your server, you have an image directory 'images' Under images are the subdirectories for the first few digits of the hashes, e.g. '/images/0000', '/images/0001/', ..., '/images/a3df/', ..., '/images/ffff/'
• Follow this pattern (to keep number of files in a directory smaller) and save your file in the corresponding directory, in this case '/images/ac17/ac17c9e81fc3789ab6b8c1f3325d789e9aa3daaf'
• In your database, save the user id and corresponding filename.

If multiple people upload the same image it only get's stored once on the server. Whenever you have no more references in your database to that filename, you are free to delete it.

Answer 3

I would keep the native image name (append numbers to stop them overwriting each other) and keep them all in one folder such as "uploads". Sometimes I make the folder not publicly accessible but accessible through a handler to give me more control over it.

I will never store images in the database but I do have a relational database and I store records of each upload, user ID of who uploaded it, file name etc.