| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2006-09-06 : 21:31:26
|
| I have a large database that ultimatly stores contract information for individuals. In addition to storing the data details, we are now going to store the actual pdf files( or scanned copies) of the contract. Should I store each pdf in a sql table for each contract in a image datatype, or should I just store the path (i.e. \contracts\PDF1.pdf) in the db and save it on the local hd? Please be aware there will be 10's of 1,000's of these contracts roughly about 40-50k a piecePlease let me knowThanks |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-06 : 21:43:41
|
| It's basically the same as storing images, and the general consensus is that you should store externally and provide a link (e.g. file path) to the location.HTH,Tim |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-06 : 22:28:40
|
| There are advantages to storing in the database.1. It is easier to make sure you have backups of all the images.2. It is easier to make sure that you have transactional integrity.3. It is easier to make sure the files are not changed or deleted without going through the application.CODO ERGO SUM |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-06 : 23:50:02
|
| I agree with MVJ. Another reason to is that if you don't then you must replicate your security and backup model on your file system.Of course, the other reason is that it's data - why would you store it anywhere else other than a database? |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-09-07 : 01:10:29
|
| Is that the verdict then, the DB is the better bet, or are there advantages of storing in a folder? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-07 : 09:21:13
|
quote:
1. It is easier to make sure you have backups of all the images.
Depends on your recovery model. Text and image data operations are not fully logged except in full recovery mode. Also, it's very difficult to back up a single (changed) image if they're all stored in the database, even harder to restore one. Backing up files is a very simple process and you have a great deal more flexbility than with database backups (folder structures, archiving, compressing, incremental backups)quote:
2. It is easier to make sure that you have transactional integrity.
Yep, ya got me here, although I don't think this is a deciding factor. Unless you absolutely need to coordinate saving a file AND database data, this isn't an issue.quote:
3. It is easier to make sure the files are not changed or deleted without going through the application.
Just as long as nobody with the right access to your database can clobber them. This includes your developers. And hey, filesystem permissions are not all that hard to set up.BTW, how is the PDF going to be viewed? I'm not aware of any reader software that can pull a bitstream directly from SQL Server and render it. In all likelihood, the PDF data will need to be saved to a file before it can be used anyway. And are you absolutely certain you want a single app to be the only method of delivery? How do you email a PDF to someone?Plus you have to put in a mechanism to take a PDF file and upload it into a SQL Server image column. I just don't see the utility in doing extra work to put a file in the database when you (have to) have a file anyway. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-07 : 09:50:08
|
| We've decided to store files in the filesystem both times there was that decision to make:I've worked 2 different places where there were (very) large amounts of files stored in filesystems. In one case the files were images in another case they were various file formats of financial documents. With all the various methods of delivery and viewing of these documents that were in place at one time or another, keeping them in the filesystem was the more flexible (and better) choice for us. In the case of the financial documents we also parsed a good bit of the data contained in the documents and stored that as traditional data elements. btw, I'm a developer and I never had access to the "real" files in both cases or the production databases for that matter. A wise move knowing the kind of mistakes I'm capable of :)Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-07 : 11:22:31
|
| I am not saying there are not good reasons for keeping files in the filesystem. I just wanted to point out the issue is not completely one-sided, and that they should be aware of the trade-offs.For an application that contains contract information, transactional integrity and security may be more important than ease of programming.I have also seem a number of problems with products we purchased where the external image store got out of sync with the database. I don't even want to think about how we would go about doing a recovery to a point in time and coordinate a SQL database and external image store.CODO ERGO SUM |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-07 : 16:21:15
|
| I find this topic intriguing and also relevant to something I'm working on. For my purposes, storing the images and/or files in the DB is not an option, since MSDE has pretty small db size limit, so the BLOBS are definitely going into the filesystem, but... (oh, there is always one of those) is there a way to have the users, via the application, select and copy the files to a certain location, wherein the app can subsequently retrieve them from same, to display a picture on a form, for instance, or serve up the file to acrobat, without the individual users actually having read/write/modify rights to the folder in question? It might be useful for something like a very basic document-control system. This is mostly just idle curiousity, but I'd still like to know. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-09-07 : 16:23:22
|
| Thanks for all the informationI'm going to go the filesystem route. It just fits my needs a little better.If anyone is pondering the same question, read the articalthat madhivanan recommended.http://www.aspfaq.com/show.asp?id=2149It pointed out some information that I overlooked in weighing the pros/cons of each.Thanks |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-09-07 : 17:36:50
|
quote:
when your database really goes south, to the point where even the backup is useless, you still have the files on the filesystem (though their usefulness is questionable, depending on how much related data was kept in the database). Which is arguably better than losing all of your data *and* all of your files.
If this really is a valid point, should it then only apply to BLOB's?I like (and use) the idea of having BLOB's (images, Word files and such) in the DB, I admit it may not not be the best solution in all cases, but my greatest point has been that I can have one easy and unified access to them and thier data through a DB connection. We host a series of DBs and have costumers hooked up with VPN, and all I have to 'worry' about is the access to the SQL server (one TPC port). If disaster strikes I just have to install the backup DB on any given SQL server, no file copy (which takes FOREVER on high volumes), no creation of shares, no file access maintenance (which is NOT stored nor saved with the DB).I guess it all comes down to if the BLOB's are a natural (sub)part of a database contents or if the databese is 'just' a way to access the BLOB's (key word access to a image archive).-- The Heisenberg uncertainty principle also applies when debugging |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-07 : 17:50:19
|
Wouldn't it be nice to have a File data type in the database?Imagine the power.. Select ImageFile.AsByteStream() as ImageBytes, ImageFile.CreatedDate as DateCreatedfrom ImageFileswhere ImageFile.Extension = 'jpg' and ImageFile.Size < 20000 DavidMProduction is just another testing cycle |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-07 : 19:03:25
|
Well David, they were originally going to have that in Yukon, but it was dependent on WinFS. Essentially the data would be an independent file but the relational engine was built directly into the filesystem, so you could query ANYTHING on disk as if it were part of the database. You probably knew that already  but in case no one else did. Don't know if the current WinFS build does that but I do know it's pruned off quite a few features from what was promised.I have to check my Yukon Beta 1 CD and see if the docs stated that. I know I read it somewhere. |
|
|
|
|
Image blobs or pointer when storing PDF?