Hi,
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?
+1
A:
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
2010-03-16 22:57:03
You still need to identify them uniquely in the database, though.
Cameron
2010-03-16 22:57:48
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
2010-03-16 23:02:22
+1
A:
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
2010-03-16 22:57:44
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
2010-03-16 23:03:47
+1
A:
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
2010-03-16 23:11:15
+1
A:
Advantages:
- No need to worry about write permissions on the file store.
- 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.
- In certain databases (such as Oracle and SQL Server) you can index files and search within them using SQL
- No need to worry about unique filenames, folders and it can make uploading simpler in some cases
- Easier to protect access to files so only authorised users can see them
Disadvantages:
- Performance of serving files often suffers compared to filestore
- Can lead to large databases. Care needs to be taken when selecting binary columns.
- More work to link to files and serve the contents - you need specialised handlers etc.
Dan Diplo
2010-03-16 23:21:01