Working with WideImage today to create an image/media management system inside of my application's framework.
Do any of you have suggestions for the best way to organize a database schema to reference images in a file system?
My current approach is to run sha1_filename on the image to generate a filename, then store the image to the FS in an image folder path above the root.
I'm creating two sub folders to house the images based on the sha1_filename, for example:
83d8281665383fa968f34a91e7539b947d3a59d1.jpg is stored as
IMAGES_ROOT/83/d8/83d8281665383fa968f34a91e7539b947d3a59d1.jpg
My current reference table SQL looks something like this:
CREATE  TABLE IF NOT EXISTS `images` (
`id` INT(20) NOT NULL COMMENT 'image id' ,
`filename` VARCHAR(255) NOT NULL COMMENT 'relative path to file on FS' ,
`size` INT(8) NULL COMMENT 'file size' ,
`width` INT(5) NULL ,
`height` INT(5) NULL ,
`metadata` TEXT NULL COMMENT 'image meta' ,
`mime_type` VARCHAR(45) NOT NULL COMMENT 'mime type\n' ,
`caption` TEXT NULL COMMENT 'image caption' ,
`uploaded_by` INT(5) NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NULL ,
`live` TINYINT(1) NULL DEFAULT 1 COMMENT 'publicly visible?' ,
`deleted` TINYINT(1) NULL DEFAULT 0 COMMENT 'shown in listings' ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB
Eventually I'm going to add tags and  categories, but that can also be done later.
Thoughts?