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:
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.)
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:
- Create a single folder for each user in your
/uploads/
directory when they upload their first file.
- 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.)