Photo Storage?

[bharanidharanit]

Hi,
I am providing users to store their photos in my website. Which is the best one to store and provide security? file system or database?

[kows]

storing files of any kind in a database is a bad idea. it produces unneeded database strain and would most likely be slower than just storing the files regularly. store them locally on the file system.

[Krokonoster]

storing files of any kind in a database is a bad idea. it produces unneeded database strain and would most likely be slower than just storing the files regularly. store them locally on the file system.

Agree!
But (providing you have some free time) you could always try and see for yourself.
It's been years since I tried, and I'm not sure the same still apply. (the way it performed back then, made me never, ever try again).

From another post:

The overhead using BLOB is a lot less than most people would have you believe, especially if you set it up right. If you use a separate server just running the DB to store binary files then you can in fact use no file-system at all and avoid any overhead from the file-system

That said the easiest/best way unless you have a couple of servers to yourself is storing them in the filesystem

*

Do not store the absolute URL of the file in your DB, just the unique part (and possibly a folder or two), e.g. '2009/uniqueImageName.jpg' or just 'uniqueImageName.jpg' Then in your pages just add the host and other folders onto the front, that way you have some flexibility in moving your images - all you'll need to change is a line or two in your PHP/ASP.net page
*

There is no need to store outside the document root for security - a .htaccess file with DENY FROM ALL will work the same and provide more flexibility
*

No need to 'shunt' images so much for security, just have a getImage.php page or something, and then instead of inserting the actual URL in the src of the image, use something like getImage.php?file=uniqueImageName.jpg Then the getImage.php file can check if the user is authorised and grab the image (or not)
*

Use a name which is guaranteed to be unique (preferably an integer i.e. primary key) when storing, some file-system (i.e. Windows) are case-insensitive, so JoeBloggs.jpg and joebloggs.jpg are unique for the database, but not for the file-system so one will overwrite another
*

Use a separate table for the images, and store the primary key of the image in the users table. If you ever want to add more fields or make changes in future it will be easier - it's also good practice

If you are worried about SEO and things like that, store the image's original file name in another field when you are uploading, you can then use this in your output (such as in the alt tag)

[penagate]

This is nonsense:

If you use a separate server just running the DB to store binary files then you can in fact use no file-system at all and avoid any overhead from the file-system

For that to be true, the database server would also have to be an operating system, in which case it would also be the file system.

[bharanidharanit]

Hi all thankyou,
I can see both filesystem and database for storing photos. Anyone conclude this, which is the best one? filesystem or database?

[Krokonoster]

Krok say File System

[abhijit]

Hi all thankyou,
I can see both filesystem and database for storing photos. Anyone conclude this, which is the best one? filesystem or database?

Unfortunately there's no conclusion. There's endless discussion around this.

If you store on the file system, make sure you also have some kind of security on the folder, that you have on the database.

If you save it in the database, all the security that you have on your tables will ('hopefully') get automatically applied on the table containing the images.

In either case, you need storage space to save the photos.

[SambaNeko]

There's endless discussion around this.

I don't think I've seen anyone here in favor of database, and nor am I. File system.

[abhijit]

I don't think I've seen anyone here in favor of database, and nor am I. File system.

I am personally in favor of the database storage. Some of the reasons I favor have been mentioned in this thread over here.

Here's a blog that also supports the point of view I hold.
http://www.onlineaspect.com/2007/07/...r-file-system/

My suggestion is to try this yourself. Keep a folder with a 100,000 files and a database with 100,000 rows containing images. The fetching with the db is faster. If you experience anything different, do let me know. I had done this test a few years ago after reading Tom Kyte's "Expert One on One". The OS was Windows NT and the database was Oracle 8.1.7

One more link.

[SambaNeko]

Not interested (in trying it myself). You are free to hold whichever preference you like, and both of your links concede that one or the other may be better given the scenario - bharanidharanit, abhijit's links would be a good read to aid your decision based on your needs.

If you experience anything different, do let me know.

It's obviously not my own test, but this guy sounds like he's reading my mind on the subject (comment from the blog post you linked):

Testing the speed of 1 'user' getting 1 image from a database and/or file system containing 3 images, or 1 'user' getting 1 images from a database/file system containing 120,000 images doesn't really make a difference, because it's still only 1 user making 1 connection to retrieve 1 image.

Try testing 1,000 users getting 1,000 images from your database / file system, better still try 1,000 users getting the SAME image fro your database / file system. I think then you'll see slightly different results!

[penagate]

There truly are some silly arguments being tossed about:

For a simple system with few users, you might get away with simply tossing the files into a folder on your Web server. But more advanced systems require a more advanced file-management system. For instance, you need a custom garbage collector to clean up old files, a system to manage duplicate filenames, and a system for tracking descriptions and other metadata about each file.

You need all of these with a database solution too!

And...

Storing files in SQL Server is easy, and it's more reliable and secure. The files are tucked away safely in your database instead of lying around on a Web server where they could be subjected to deletion, moving, renaming, or any number of other seemingly harmless actions that could wreak havoc on an unsuspecting software system. Best of all, the files are backed up automatically as part of your normal database-maintenance routine.

All of those nasty things can happen if they are stored in the database too. And any serious webmaster already has automatic backups of website files.


In my experience (using PHP and MySQL) storing binary data in the database is needlessly complex and involves a lot of overhead. For example, files need to be serialised to be transmitted to and from the database server -- a DBMS can't serve files to the client without going through the web layer.