1

In one of the tables in the schema I am working on, I need to deal with couple-of thousand "data-sheets" which are mostly PDF documents, and sometimes graphic-image files like PNG, JPG etc. The schema models a Electronics Distributor's portal, where new products get added to their portfolio frequently.

These documents (data-sheets) are added, at the time of introduction of a new product, but they need updates from time to time (s.a. due to newer version of the document, not the product itself), so I'd think the update to be an asynchronous procedure.

Given this, should I keep only the file-name/path of the data-sheets (& similar documents) in my table, with the actual file being on filesystem, or should I take the blob approach. I am almost certain that it should be the former approach, but still wanted to take community advise, and see if there are some pitfalls to watchout for.

bdutta74
  • 2,798
  • 3
  • 31
  • 54
  • 2
    possible duplicate of [Storing a file in a database as opposed to the file system?](http://stackoverflow.com/questions/8952/storing-a-file-in-a-database-as-opposed-to-the-file-system) – Eugene Mayevski 'Callback Nov 01 '11 at 07:50
  • Thanks @EugeneMayevski'EldoSCorp. That does answer my question. I wonder, how I can close this question now. – bdutta74 Nov 01 '11 at 17:17
  • Either delete it or wait until somebody else (4 more people) vote to close it. – Eugene Mayevski 'Callback Nov 01 '11 at 18:11
  • Don't see an obvious way to delete this question. Unless it is against SO policies, I'd prefer to keep the question. Sometimes questions reworded differently, help others have better luck with search results. – bdutta74 Nov 02 '11 at 03:53

1 Answers1

1

For completeness, let me just mention that some databases allow you to have a "hybrid" of these two approaches, for example Oracle BFILE or MS SQL Server FILESTREAM.

There is also an interesting discussion at Ask Tom on storing files in Oracle BLOBs (in a nutshell: "BLOBs are better than files").

BTW, you don't necessarily need to chose one over another... If you can afford storage overhead and you are operating in a read-mostly environment, you could store the "master" data in the BLOB for integrity but "cache" that same data in a file for quick read-only access. Some considerations:

  • You'd need to make sure the file is updated/removed if BLOB is updated/removed.
  • Consider creating/updating the file on-demand.
  • Consider evicting old files from the "cache" even if corresponding BLOBs still exist.
  • Consider using several "caches" (e.g. if you have a middle tier and is distributed to multiple physical machines, each machine could have its own file cache).
  • And finally, you'd need to make sure all this works robustly in a concurrent environment.

So, this is not the simplest approach but, depending on your needs, may be a good tradeoff between integrity, performance and implementation effort.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Good point. Unfortunately, I am on MySQL, which if I am not mistaken, doesn't have such a feature and thus would require complete application layer logic to turn disk representation of the file (s.a. PDF or image) into a BLOB, and vice-versa. – bdutta74 Nov 05 '11 at 20:04