Database design for sharing photos site?

Question

I am using php and mysql. If I want to do a sharing photos website, whats the best database design for upload and display photos. This is what I have in mind:

domain:
  |_> photos
  |_> user

Logged in user will upload photo in [http://www.domain.com/user/upload.php]

The photos are stored in filesystems, and the path-to-photos stored in database. So, in my photos folder would be like: photos/userA/subfolders+photos, photos/userB/subfolders+photos, photos/userC/subfolders+photos etc

Public/others people may view his photo in: [http://www.domain.com/photos/user/?photoid=123]

where 123 is the photoid, from there, I will query from database to fetch the path and display the image.

My questions:

1. Whats the best database design for photo-sharing website (like flickr)?

2. Will there be any problems if I keep creating new folder in "photos" folder. What if hundreds of thousands users registered? Whats the best practices

3. What size of photos should I keep? Currently I only stored thumbnail (100x100) and (max) 1600x1200 px photos.

4. What others things I should take note when developing photos-sharing website?

Answer 1

1 - At a minimum your database should consist of

users: user_id, user_name, pw_hash, salt
photos: photo_id, user_id, photo_path

fields would be added for extra functionality you wanted (tags, last_seen, photo_upload_date, photo_views, etc...)

2 - Dont prematurely optimize. i would go with adding a users/username folder for each user and dumping the users photos in there.

3 - i recommend keeping the original size, thumbnail, and a size that is suited for your site. so if your layout holds a photo with max width of 850px resize all photos to a max 850px wide. If youre layout changes you can use the original picture and resize them to the new size.

4 - there is an excellent answer on SO somewhere about image upload security but i cant find it. ill keep looking.

think about using a CDN: http://stackoverflow.com/questions/72369/whats-the-best-cdn-for-image-hosting-on-a-high-volume-web-site

good talk on scalabilty, the section on thumbnails would be good for you to watch: http://www.youtube.com/watch?v=ZW5_eEKEC28. this video talks about a file-per-directory limit in ext3 filesystems you may be interested in.