Building a database for storing user uploaded images

[TechBoyJK]

I need to build a simple db for storing user uploaded images. Functions needed are pretty straigh forward.

Users will be able to create logical folders, and upload photos to any chosen folder. I've already got the uploading process working, I'm just trying to work out the best way to store the photo info.. Here's my schema so far. Thoughts? I think I have it normalized very well and this layout seems logical. I tried to explain the purpose of columns behind the #


tbl_member_photo_folders

photo_folder_id (int/null/pk) #unique id of each declared folder
member_id (int/fk) #member id of owner
thumbnail_url (varchar 255) #direct url to thumbnail image for folder
created (datetime)
created_ip (varchar 15)
last_modified (datetime)
last_modified_ip (varchar 15)
folder_position (smallint) #order in which folders are displayed
photo_security (smallint) #user defined viewability


tbl_member_photos

photo_id (int/null) primary key #unique id of each photo
member_id (int) foreign key #member id of owner
folder_id (int) foreign key #id of parent folder
public_url (varchar 255) #public url for photo
private_url (varchar 255) #private url for photo (local resource)
photo_file (varchar 255) #photo filename public_url/photo_file creates full path
created (datetime)
created_ip (varchar 15)
last_modified (
last_modified_ip (varchar 15)
photo_position (smallint) #order in which photos are displayed
photo_security (smallint) #user defined viewability



tbl_member_photo_captions

caption_id (int/null) primary key #unique id of caption
profile_photo_id (int) foreign key #id of parent photo
caption (varchar max) #caption content

 

[WannaFly]

I'm not sure what you are looking for - but I have a few suggestions:
1) If you do not need more then one caption per photo, just add the caption to tbl_member_photos.
2) 255 may be too short for url/file paths.
3) Unless you really have a need for a 1M char caption, avoid varchar(max) - set a limit. Helps with indexing/querying if you ever need up.