While the initial reaction might be that all
related data should be saved at the same time, it seems that the very
nature
of much LOB data (large, essentially immutable streams) means that the
very
archival medium (much less things like retention rules) should perhaps
be
different for this sort of data.
I think the difference is less of a theoretical one and more of a
practical one, specifically for backup/recovery reasons.
We have an application where users can add "attachments" to rows in our
database. An attachment can be anything, in fact we describe it in our
sales presentations as "if you can save it on your PC you can save it in
our system." The typical attachment is a word document or pdf, but we've
seen AVIs, MP3s, JPGs, and multi-hundred megabyte zip files. In our
case, as you suggest, the bits are immutable. If you want to upload a
new version go right ahead, but we're keeping the old version too (don't
blame us, it's OHRP and the FDAs fault <G>).
From a backup/restore point of view we're torn on what to do. If we
stick it in the database we have a "complete" backup. However, since the
majority of the storage space used is used for attachments our backups
get larger and larger each time (nightly, plus 15-minute log-files) when
the backed-up bits can't have changed since last time. Even worse than
backup size and time though is restore time. The bigger the backup the
longer the restore, and for the most part, if attachments come back
online "later" than the rest of the application it's not a big deal. I'd
rather say, "hey, you'll have everything but attachments in 20 minutes,
and attachments 2 hours later" than have to say "you won't get anything
for 2 hours". [1]
In our case we've chosen not to store the attachments in the database
but instead store them as files in the file system, and simply store the
filename in the database. We have to backup and restore the db and the
attachment-space separately, but now backups are small and the restore
process is more straightforward. I've run this past several people and
the general consensus is "Databases are for data and file systems are
for files, don't mix the two."
Having said all that, I think you also have to look at the file sizes.
If you're taking about a 20KB image of a product I'd stick it in the
database, but when you get to multi-megabyte files, well, use a file
system.
-Walden
[1] Yes, I'm leaving HA out of this equation.
midrange.com
