2

BLObs – Should I Store In Database Or in Directory Structure

I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The database being used is MySQL running on a Linux Mint server although much of the development s being done on a Mint laptop for convenience. Part of the reason for moving from the old structure to a database is to avoid errors that have been caused by misnaming of directories and files, and also to extract related information more easily for use in trend analysis and other scenarios. Two of the tables contain BLObs. One has six of which four are infrequently accessed – five of them can be about 2Mb in size but may be larger. The other has four files – three of around 0.1Mb and one of around 4Mb. At a meeting this morning a consultant (cloud/web/database) said that it would be better to keep the BLObs in a file structure with the database holding pointers to them.

Having spent the last few hours Googling I am now started to get confused. Is there a right or wrong way to handle BLObs, and are there any guidelines on which method to choose?

Thank you...

1

I've seen it done both ways. If you want to bulk up your database size just start using BLOBS.

Pro's for storing documents in your database

  • you're already storing your data there so why not a little more? (Only it's a lot more)
  • a certain simplicity of access. Any request to read only has to go one place to access the document or thing

Cons for storing BLOBS in your database

  • datafiles grow quickly, backups are slower. As soon as it takes longer to backup the database than you have available quiet time more sophisticated and expensive options are required
  • in Oracle you cannot easily access BLOBS/CLOBS across a database link so your data in those fields cannot be accessed by other databases

Pro's for using a file system

  • system admins feel backing up a directory is a lot easier than backing up a database
  • same for adding more disc space
  • separation of concerns (although this is illusory: if the database is down you can't find the document anyway and same if the application server is down)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.