Question
one of my customer ask for a Document Management System for some thousands of document in different format i.e. pdf, doc, docx etc. My question is what is the best way to store this file in database or in file system? How easy to secure a document between the two approach?.
Fast retrieval of the files is the key requirement..
am using mysql if that helps
Regards.
Answer
You might want to store it directly into filesystem.
When using filesystem careful with :
- Confidentiality : Put documents outside of your Apache Document Root. Then a PHP Controller of yours will output documents.
- Sharded path : do not store thousands of documents in the same directory, make differents directories. You can shard with a Hash on the Filename for example. Such as /documents/A/F/B/AFB43677267ABCEF5786692/myfile.pdf.
- Inode number : You can run out of inodes if you store a lot of small files (might not be your case if storing mostly PDF and office documents).
If you need to search for these documents (date/title/etc...) you may want to store metadata into a database for better performances.
FYI, [in this question](https://softwareengineering.stackexchange.com/questions/150669/is- it-a-bad-practice-to-store-large-files-10-mb-in-a-database) MS SQL Server has FILESYSTEM column type (like an hybrid), but at the moment MySQL [doesn't have an alternative](https://stackoverflow.com/questions/10255010/filestream-storage- in-sqlserver-mysql-equivalent).
