Files in database or filesystem?

[Sic]

Obviously, this is to be taken on a case-by-case basis, but I was having a chat with a guy at work the last couple of days and he's convinced that storing files in databases is the absolute best. Personally, I disagree. As far as I can see, there are pros and cons for each, but I can't understand why he's so for it. Am I missing something?

Database:
Pros.
-Secure. Users are unable to peruse your files unless you say they can
-Portable. Don't have to worry about where all your files are if they're right there in the database
-Easier to normalise data

Cons.
-Relatively slow, compared with the filesystem (I would've thought)
-Pointless, unless you need the 2 pros (in my mind)
-Large amounts of database taken up with files would logically slow indexing/searching (this may not be true!)

File system:
Pros.
-It's where your files go!
-Better for dealing with larger files
-Quicker
-Easier to manage physical files - ie. if you just want to switch one of your files, delete, upload, rename and you're done!

Cons.
Basically the pros of database, although some degree of security can be obtained through apache and if you're organised, portability shouldn't really be too much of an issue.

I've had a look around and I can't find anything concrete (mostly people saying "why would you want to store files in the database?!" - not helpful!) - was wondering if people could share their thoughts/preferences on the subject.

Anything in this post is subject to scrutiny - file management isn't a speciality area of mine and I'm very open to persuasion!

 

[shine]

You've covered it pretty well. I only store files in db's when security is a major concern otherwise store on the fs with a link in the db to the file location.

 

[zinc]

Recently I've been looking into this area of storing files and looking round the internet I havent found no one better than the other except secuirty on the DB, some people say use DB some people say file system.

But for the file system approach it just scares me that I'll miss something, so I've been reading as many php secure file storage/upload tutorials as possible.

Has anybody got experience developing secure php uploading scripts and any recommended tutorials that I may have missed?

 

[Haircut]

In terms of speed, that all depends on the architecture of your system.

I've worked with a system where we had the web servers and the database servers in the same physical location, but the SAN was offsite somewhere else.
There was a 1Gbps link between the web server and the database, but only only an 8Mbps link between that location and the SAN.
Sounds crazy that they would design it like that I know, but the SAN was primarily used to service another location.

In that case it made perfect sense to use the database as our storage mechaniasm as retrieving files from there was actually quicker than getting them from the SAN.

 

[Shoseki]

My attitude is :

Data storage is data storage. Don't consider "files" as the only form of "data storage".

For some types of data, files have standards (both in terms of format and/or compression) that greatly benefit storage. These mostly include media (audio/video/flash). Other types of data can just as easily be stored in a database as they can be in file (text/html). The main benefit is that storage in a database gives content management options not available to a file (which is kind of, all or nothing).

I am currently converting my website to run fully in Joomla using as much database as possible so that I can maximise on this "content management" stuff. Files are files at the end of the day, they aren't dynamic, they aren't very manageable apart from sftp/scp.

So really, decide what type of content you want to store, and how you want to store it. Its possible to use SS scripting to make your media hidden anyway (make it higher level than your htdocs/.htaccess restricted and have it serve on demand from a PHP script as an example).

 

[whitecrook]

If you store your files in a database, where do you store the database??

 

[~J~]

A few years ago I developer an application that allowed people to record messages on their computer and send it to people within their organisation.

I originally looked at storing the files within the database but ran into to immediate problem and foresaw one potential problem.

The one I ran into immediately was in my particular instance, the files saved were WAV and/or MP3 files. This meant that my program had to access the database, seek the file, download the file, launch the respective application and play the file.

This took time. BUT, by having the files saved on the server and downloading them, the media application kicked in straight away and 'streamed' the files.

So for my case, storing the files on drive rather in database was a perfect option.

The long-term problem was the database itself, again in my case it was to my application requirements and so each record containing a WAV file varied from 3Mb to 20Mb in size. Even when the converter was run intelligently to convert the WAV's to MP3's, it still resulted in a big file.

But we did one HUGE test of pushing about several thousand files onto the system and even though we'd applied indexing, we were noticing some significant processing power in SQL Query Analyser.

My Personal thoughts are, and I emphasise personal, if you're storing files long term then I'd put them in the database as they are perhaps more secure, can be grouped in the database backup and of course migrated throughout the system.

But if the files your saving are going to be retrieved on a regular basis, and/or your saving files on a regular basis I'd personally store them on a disk.

 

[Shoseki]

If you store your files in a database, where do you store the database??

The database is abstracted away from you.

They might end up being in an enormous file either on a single server, or a multiple servers, but a database has multiple ways to back itself up, load balance etc. A file is just a file.

Also, you usually don't access a database directly, you access it through a database service that performs access on your behalf via SQL or some other mechanism.

 

[Moredhel]

My webcomic manager started off storing files in the database. Once I hit about 3000 strips it really started to chug. Glad I changed it, it now has over 10,000 strips!