Image Gallery System - Which Approach is Better?

Question

I am implementing an image upload system in PHP, The following are required:

• Have categories
• Allow users to comment on images
• Allow rating of images

For that, I have 2 approaches in mind:

1. Implement the categorization by folders

Each category will have its own folder, and PHP will detect categories via those folders.

Pros

• Structured look, easily locatable images.
• Use of native PHP functions to manipulate and collect information about folders and files

Cons

• Multiple categorization is a pain
• Need to save the full path in the database

2. Implement the categorization by database

Each image in the database will have a catID (or multiple catIDs), and PHP will query the database to get the images

Pros

• Easily implemented multi-categories
• Only image name is saved

Cons

• Seems more messy
• Need to query the database a lot.

---

Which do you think is better? Or is there a third, completely different, approach that I'm missing?

Just a note, I don't need code, I can implement that myself, I'm looking to find what to implement.

Would love to hear from you.

Answer 1

I believe that the second option is better, a DB is giving you much more flexibility, and I think better performance then file system, if you set the right indexes.

In the filesystem approach you are limited to only 1 category per image, when in the DB you can set multiple categories on an image.

The con that Db is more messy, sorry I can't find a reason way in the db it will be more messy, maybe you mean that the files are not organized on the file system, but you still need to organize the files on the file system and divide them to multiple folders for better performance, and if you want to get all the images that have been uploaded you query the db for all of them, which will be much faster then ls on all the categories folders.
In organize the files in the file system when using the DB approach I mean that you need to divide them to several folders, actually it depends on how you predict the upload of the images will be:

1. If you predict that the upload will be spread on long time then I think that better to put the files in directories per range on time(day, week, month) example if I upload an image now it will go to "/web_path/uploaded_photos/week4_2012/[some_generated_string].jpg"
2. If you don't know how to predict the uploads, then I suggest you will divide the files into folders on something generic like the first two letters in MD5 hash on the image name, for example if my file name is "photo_2012.jpg" the hash will be "c02d73bb3219be105159ac8e38ebdac2" so the path in the files system will be "/web_path/uploaded_photos/c/0/[some_generated_string].jpg"

The second con that need to query the DB a lot is not quite true, cause you will need the same amount of queries on the file system which are far more slower.

Good luck.

PS Don't you forget to generate a new file name to any image that have been uploaded so there will be no collisions in different users uploaded same image name, or the same user.