save files in db or in folder?

[hassank1]

I am building a social network site..

so am thinking what's better? to save the uploaded profile pictures binary data into a blob field or to save the pictures into a folder (of course with appropriate permissions) on my website ?

[Mchl]

In most cases it is better to store them into a folder.

[hassank1]

would u plz give some details of the reason?

[Mchl]

Main reason is: it is less strain on database.

 

Reasons why you would need to store files in database are

1. You want operations on files to be logged into binary log

2. You want files to be part of database transactions (as in ACID transactions)

probably some others, but I can't imagine them right now

 

[hassank1]

in ur opinion do u think that I should switch from saving into db to saving into folders?

[Mchl]

I don't know details of your site.

[dbo]

There are pros and cons to both methods. The biggest benefit of storing in the DB (IMO) is that when you backup your database you effectively backup your media as well. It just makes it a lot cleaner/easier to do full backups and restores.

 

From the filesystem side of things you have performance. You don't have to do a lookup for the file and then create it on the fly, you simply immediately return the data making file operations quicker and easier.

 

If your dealing with files of less than 2mb the performance impact is minimal either route you go.

 

I've done sort of a hybrid approach which works fairly well. I store all the uploaded files in a directory that is not readable and create a hash of the filename... without an extension. This means that no one can access this directory, etc. Then I do a read into a variable and serve up the content by echoing out the appropriate mime types.

 

Using this approach I get most (but not all) of the performance related with the filesystem and some of the benefits of security/permission type of stuff via the database. Rather than dealing with file permissions I can create rules that I derive from a permissions system to determine of a user should be able to view the file or not.

 

But in short... the answer is - it depends. I'd suggest sharing some more specifics on the files you plan to store (including sizes), concerns about ease of backup, permission levels on these files, etc etc. Without that information at hand this is really just one of those debates that go back and forth.

[PFMaBiSmAd]

Some more cons of putting files in a database -

 

There is a maximum packet size that limits how much data can be put or gotten in a query that will limit the size of a file that can be placed into a database.

 

Backups of databases using .sql dumps, the most common way of backing up and transferring databases, outputs blob fields using two HEX characters per byte so the size of the blob data is double the actual file size stored in it. If you have 100MBytes of images, you get a 200MByte .sql dump file of that data.