Almost all
SQL based projects (ex:
LAMP) require a way to store files.
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
Store them as BLOBS(
+) 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 solutionA hybrid solution might be to store the BLOB/files into a separate table and use foreign keys to it.
Store as normal fileUsually 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