6

I'm writing a php application and was wondering if it's a bad idea to store complete files in the database. Files should be around 100-200kb mostly text files (txt, doc, docx and so on) or small image files. Or is it just a plain wrong idea?

Gabriel
  • 1,820
  • 2
  • 23
  • 31
  • 2
    You may want to check this out: Storing Images in DB - Yea or Nay? (http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) – Daniel Vassallo Mar 16 '10 at 22:55
  • MongoDB has some interesting features for storing files. – benlumley Mar 16 '10 at 22:58
  • @Daniel Vassallo: interesting, i don't think that i will store there that many images and it certainly will not be for any high traffic web page. – Gabriel Mar 16 '10 at 23:07

4 Answers4

4

Advantages:

  1. No need to worry about write permissions on the file store.
  2. No need to try and synchronise files on filestore with rows in the database, avoiding orphaned files or broken links. For instance, you can automically cascade delete files when related content is deleted.
  3. In certain databases (such as Oracle and SQL Server) you can index files and search within them using SQL
  4. No need to worry about unique filenames, folders and it can make uploading simpler in some cases
  5. Easier to protect access to files so only authorised users can see them

Disadvantages:

  1. Performance of serving files often suffers compared to filestore
  2. Can lead to large databases. Care needs to be taken when selecting binary columns.
  3. More work to link to files and serve the contents - you need specialised handlers etc.
Dan Diplo
  • 25,076
  • 4
  • 67
  • 89
3

It really depends on the situation?

  • How the files going to be distributed?
  • Are the files used standalone or are they part of an system which might have its own authorization and authentication logic?
  • Whats your backup strategy?
  • Do you need replication?
  • Do you need to support a lot of I/O?
  • What about caching?

Having said that, I would lean toward some kind of filesystem for documents over a database.

BeWarned
  • 2,280
  • 1
  • 15
  • 22
2

Pro: highly portable.

Con: you can't do anything with it using SQL (indexing, searching, etc) and you'll need to add metadata in other columns (content type, filename, etc) to improve (re)usability and maintainability.

I wouldn't do that. The disk file system is much better suited system for those tasks.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • i'm aiming for portability, because those files won't be edited in any way, maybe they will be deleted but that's it. – Gabriel Mar 16 '10 at 23:03
1

Personally, I like the idea, especially if the DB stores them compressed or you compress them manually.

Among other things, it means you don't need to worry about unique names for files, which saves a lot of complexity.

James McLeod
  • 2,381
  • 1
  • 17
  • 19
  • You still need to identify them uniquely in the database, though. – Cameron Mar 16 '10 at 22:57
  • Indeed. I was not thinking of the trivial case of storing only the file in a database; I assumed some "metadata" like a user name, date, filename, or the like. – James McLeod Mar 16 '10 at 23:02