I've encounter this problem so many times and so many times I had to think about pros (+) and cons (-) that I decided to put them down into this post so anytime I will encounter this problem I will be fast updated :D
There are 2 ways to store files:
- into the SQL as BLOBS
- into the file system as normal files
(+) avoid file system permissions nightmare
(+) all CRUD operations can be transactional (if your database supports transactions)
(+) can be replicated
(-) bigger databases (dumps will take longer and will be bigger)
(-) if you want to use rsync or similar tool to backup your web app the whole database will be backed (unless rsync has a way to save only delta of a .sql file)
(-) JOINS with BLOB tables will take longer
BLOB Hybrid solution
A hybrid solution might be to store the BLOB/files into a separate table and use foreign keys to it.
Store as normal file
Usually the file is stored
(+) database is way more smaller
(+) rsync will generate a smaller network traffic (less bandwidth consumption)
(+) database backups will be faster (only the data in the SQL database)
(-) you need to take care of file permissions
(-) it can not be replicated
(-) all CRUD SQL operations will be more complicated as you will need to take care of the files too
(-) need to take care of orphaned files (a cron job to detect them and then to solve the problem)
Links:
http://www.dreamwerx.net/site/article01 - How to use the hybrid solution
