Saving attachments to database: blob vs path reference

Question

I am in a process of designing a system where one can create an entry and then add attachments to that entry, which is then saved into a database. However I am of two minds on how to implement the attachment handling.

I am battling between two approaches:

1. Save the attachments to the database directly as blob field type
2. Save to the database the directory path into the file and save the actual file to the server

Now as far as I can see both of these approaches has pros and cons.

1st approach would keep all the data in the same place so I can easily move it elsewhere if I want to. Also it keeps things consistent as far as "where is the data" guestion is concerned. On the other hand I have heard that saving things like that is very resource hog and the database size will grow extensively.

2nd approach would allow the database to keep to the easily managed data fields like text and numbers while the more heavier stuff is on the server. This would also allow a more direct access to files if necessary. On the other hand the data is seperated and more difficult to keep track of and would also need to introduce file system access in order to retrieve the said files.

I am leaning towards the number 1 approach but to this end I am hoping an answer to the following:

How would saving attachments directly to database impact on the database size and application performance?

Answer 1

Don't store documents as blobs in the database. Store paths. RDBMS is not a document store. MySQL is not designed for this purpose and storing documents inside MySQL will make the database size unnecessarily huge and data retrieval almost impossible, taking backups will take a very long time and if something happens to your database all your files go with it. File store is almost always a better choice for file storage than a RDBMS.

Answer 2

I have a similar problem, I think the approach I take is depending on the type of attachment that is needed In my case what I did is open the attachment which is mostly text and simply place the data as in in the DB (instead of the DB simply storing the attachment)

This allows me to look also inside the content in an easy way (of course this relates to the number of updates that you expect)