CODEWITHSUNDEEP
phpmysqldatabase-designgalleryphoto-gallery
Drew
Drew

Reputation: 6862

MySQL Design for User Photo Gallery

I am working on a database design for a basic photo gallery which each user can upload several images.

Right now here is what I have:

photo_gallery

photo_id -    image             - sort_order - user_id
1        -    test.jpg          - 1          - 1
2        -    another_photo.jpg - 2          - 1

And then on my folder structure I would create a new folder like so: images/photo-gallery/ and store the images in there. Now, should I create a folder for each user_id and store their specific images in that folder/

So in this case: images/photo-gallery/1/test.jpg and all of user 1's photos will be there?

Also for re-sizing, I am thinking about using smart image resizer so I can just store the original photo, and if I want to resize it to a certain size, I can just call it using the script like: /image.php?width=200&height=200&image=test.jpg.

Should I be hashing these file names? Am I missing anything else? Any suggestions on how to improve this?

Thanks!

Upvotes: 3

Views: 1975

Answers (2)

jls
jls

Reputation: 11

Now, should I create a folder for each user_id

That's a pretty good solution, another possibility is to create a folder per year and eventually per month depending one the targeted number of photos. For the photo filename I advise to use the database id this will keep you away from character encoding issues and eventually keep the original name in the database.

Also for re-sizing, I am thinking about using smart image resizer

For my point of view, unless you need to keep the full size image, I suggest to store resized photos. The benefits are the following: you can store more photos, you can send photos more rapidly, the photo navigation will be more reactive.

Am I missing anything else?

Well maybe the kind of user permission that could drive the way you store images.

Any suggestions on how to improve this?

I worked on these ideas and created a PhotoBlog that you can find here it's open source so feel free to throw a look in the PHP and JavaScript source code.

Upvotes: 0

Justin ᚅᚔᚈᚄᚒᚔ
Justin ᚅᚔᚈᚄᚒᚔ

Reputation: 15369

Now, should I create a folder for each user_id and store their specific images in that folder?

Yes, it would be a good idea to separate uploads in some way so you don't end up with one directory holding tens of thousands of files. You could separate them by userid, by first letters (e.g. images/t/te/test.jpg), or by hash (e.g. images/0e/0e4fab12.jpg).

Should I be hashing these file names?

It depends on what you're trying to accomplish. Since you're planning to refer to the filenames in a URL, storing the filename with a known set of "safe" characters can be an advantage:

image.php?image=c/ca/cat%20farting%20On%20a%20lemon.jpg
 -- vs --
image.php?image=0a/0a1b2c3d.jpg

If you do this, however, I would suggest extending your database schema to include the original filename:

photo_id | image           | orig_fn           | sort_order | user_id
1        | 0a/0a1b2c3d.jpg | charginLazors.jpg | 1          | 2

You might also consider storing additional metadata about the image, such as the upload date, a caption, etc.

Regarding the folder structure, you can use any number of characters from the filename, though there are some things to consider:

Using a hashing method that creates hexadecimal filenames means your maximum number of subfolders will be a multiple of 16:

  • One character — 16 subfolders
  • Two characters — 256 subfolders
  • Three characters — 4096 subfolders

If you use more than two characters, I'd suggest nesting the folders even further: 0a/0a12/0a12bd31.jpg -or- 0a/12/0a12bd31.jpg. This makes navigating/managing the files a bit more manageable (IMO)

Keep in mind that the more prefixed characters you use, the less files will be in each folder. If you're expecting high volume, you might choose to have more folders with less files per folder.

Upvotes: 4

Related Questions