Binary (image) storage in DB v.s. File Storage

[DJBirdi]

I just wanna get a feel for the opinions that exist out there about this subject. I've worked with everything from small to very large image storage requirements within PHP and MySQL.

What are some of the issues/usage requirements that you guys look at when considering building mysql apps that include things like user ability to upload images?

Personally I only prefer to use the file system for EXTREMELY SMALL image requirements since I hate dealing with file permissions and like to control everything myself. On the plus side, I have found file systems to be more efficient and faster than mysql binary storage.

A third option, that I've recently experimented with, and have to say am quite happy with is a hybrid of both. I store file paths in the db but the images themselves stay in the file system.

Any thoughts?

[veedeoo]

I favored the hybrid of both approach. When you move to a new server, you can zip the files and off you go.

One drawback with the blob storage is the rigamarol to display the blasted thing. Using base64/data approach for <img>, means that you aren't able to cache it. I'd advise people along the lines of store filenames - original (or upload name) and stored (or safe name - e.g. username_timestamp.ext).

[cereal]

In addition read this thread:

• http://www.daniweb.com/web-development/databases/threads/473598/is-better-store-images-in-a-blob-or-in-directories

[DJBirdi]

Ahh perfect! It does seem the hybrid approach has an overwhelming support. Cheers guys!

I'd advise people along the lines of store filenames - original (or upload name) and stored (or safe name - e.g. username_timestamp.ext).

diafol, you say original AND safe name. Why both? Why not rename the upload name to username_timestamp.ext to begin with?

[JorgeM]

Renaming the file is common because you want to prevent duplicate names and that's possible because you have no way of knowing what the future file names will be that are uploaded.

You typically want to store the original name in your table because when you may want to stream the file back to the user and default the save to the original name.

For example, what I typically design with regards to a file upload is allow the user to upload the file, then I grab the original file name and content type of the file. I then generate a random 128bit GUID. write the file to disk with this GUID.

If I display a link on the site for the file, the link I present is not actually the location of this file with the GUID name. I point to a file say... get-file.aspx?fileId=1. Then in get-file.aspx, read the querystring, apply logic (business rules, security, etc) then lookup up the file details in the fileInfo table and create my response headers, stream, etc...

So when this happens, the user sees a save dialog box and the default name presented is the one that I saved in the database table.

Oh and since this is a PHP thread it would be get-file.php?fileId=1.