Database design for photo sharing?
I am using php and mysql. If I want to make a photography site with photos, what's the best database design for uploading and displaying photos. This is what I mean:
domain:
|_> photos
|_> user
Recorded [ http://www.domain.com/user/upload.php]
Photos are stored in file systems and the path to photos stored in a database. So my photos folder will look like: photos / userA / subfolders + photos, photos / userB / subfolders + photos, photos / userC / subfolders + photos, etc.
Public / other people can view their photo at: [ http://www.domain.com/photos/user/?photoid=123]
where 123 is the photo, from there I will query the database to get the path and display the image.
My questions:
-
What's the best database design for a photo sharing website (like flickr)?
-
Will there be any problems if I create a new folder in the "photos" folder. What if hundreds of thousands of users have signed up? What are the best methods
-
What size of photos should I keep? Currently I only store miniature (100x100) and (max.) 1600x1200 px images.
-
What other things should I look out for when designing a photo sharing website?
a source to share
1 - At a minimum, your database should consist of
users: user_id, user_name, pw_hash, salt
photos: photo_id, user_id, photo_path
additional functionality will be added (tags, last_seen, photo_upload_date, photo_views, etc.)
2 - Don't prematurely optimize. i would go with adding a users / users folder for each user and dump the user photos there.
3 - I recommend keeping the original size, thumbnail image and size suitable for your site. so if your layout holds a photo with a maximum width of 850 pixels, resize all photos to a maximum of 850 pixels. If you change the layout settings, you can use the original image and resize it to the new size.
4 - there is a great answer on SO somewhere about image upload safety, but I can't seem to find it. bad to look at.
consider using a CDN: https://stackoverflow.com/questions/72369/whats-the-best-cdn-for-image-hosting-on-a-high-volume-web-site
Good talk about scalability, thumbnail section would be good for you: http://www.youtube.com/watch?v=ZW5_eEKEC28 . this video talks about file-per-directory limiting on ext3 filesystems that might interest you.
a source to share