Photos Gallery - Database Design

Question

This is my database design on photo gallery, I like feedback please or what can it be improved?

photos_category
- photocat_id (P)
- name
- slug
(5, 'Cars', 'cars')



photos_albums
- album_id (P)
- photocat_id (F)
- name
- description
- active
(20,5,'BMW','Nice BMW cars',1)

photos
- photos_id (P)
- album_id (F)
- image
(33,20,'bmw_car4.jpg')

bmw_car4.jpg is original size and it will be located at: /gallery/original/bmw_car4.jpg

For frontend (resized):

Thumbnail: /gallery/thumbs/bmw_car4.jpg

Image: /gallery/images/bmw_car4.jpg

Answer 1

Your design seems quite correct to me :

• it's simple and functionnal,
• there is no duplication of information,
• and you can, from one object, find its related informations

I would just, if you plan on having a lot of photos, add some kind of sub-directories scheme, to avoid having too many files in a single directory (/gallery/original/, and the directories for resized images).

You could add some hashing mecanism, based on the upload date (month or day, depending on the amount of images) of the images, or something like that.
For example, you could have :

• /gallery/original/2011/01/ : all images uploaded in january
• /gallery/original/2011/02/ : all images uploaded in february
• and so on

Then, you'll store the path in your photos table, and not only the file's name.