Should I only store fixed-size data elements in a database?

Question

If the amount of data stored within a given field of a database is unknown, and could be very large, should I store it in an external file rather than within a field in the database?

Answer 1

That's why BLOB or Memo field types were designed. They are very good at storing variable length / large objects.

That being said, I would still store certain large objects using the file system instead of using a database. If the objects were very large (multi-MB each) I would consider just placing them on the file system and storing a pointer (filename) in the database.

Answer 2

It depends on your task. There are pluses and minuses of storing large data on the FS instead of the DB. As for the size, you can limit it in the field definition of most databases.

Answer 3

You should choose a database management system which has the capability to handle large data efficiently. The database system might store it within the database file or in an external file linked to from within the database. SQL Server 2008 can do both, transparently; not sure what other systems offer.

Answer 4

Do you need transactional semantics (commit, rollback) for the data? If so, then using the external file system greatly complicates life -- use the DBMS. If you don't need transactional semantics, then the file system may make sense.

Answer 5

I think it might depend on how you were going to use the data. For instance, if I were storing images that would be used in a web site (Flickr or something like that), I would rather store the images as files and keep a reference in the database, rather than store image data in the database and have to reconstitute the image data each time via some HTTP handler. On the other hand, collections of large documents that need to be searched may be more useful if the text were stored in the database.