Storing rich text documents

Question

This is a follow-up to another question I asked earlier today. I am creating a desktop app that stores rich text documents created in WPF (in a RichTextBox control). The app uses SQL Compact, and up until now, I had planned to store each document in a binary column in the database.

I am rethinking that approach. Would it be better practice to store each rich text document in the file system, rather than saving it to the database? I figure I could put the documents in the same folder with the database, then store a relative path to each document in its database record, along with other information about the document (tags and so on).

I'd like to know some pros and cons of that approach, along with ideas of what is generally considered best practice for this sort of thing. Thanks for your help.

Answer 1

Personally I tend to use the filesystem

Pro DB

• Can search using SQL search features (will probably be a bit wonky with RTF becuase of the control codes)
• Backup the MDF file & you've backed all the documents in one place
• Can easily implement versioning
• Easier to keep file data & stuff that references it in sync

Pro Filesystem

• Loadable by external apps (and people)
• A corrupt DB kills all your documents
• Searchable via filesystem tools/indexers
• Less complex IO code needed
• Familiar to user
• The path can point anywhere (i.e on another machine/another logical drive)
• More portable IO code

Answer 2

Not sure why it has not been mentioned before but have you looked at the FILESTREAM data type that is available in SQL Server 2008 and above?

It combines the benefits of file system storage with the benefits of DB storage. Here is a link to a MS white paper http://download.microsoft.com/download/a/c/d/acd8e043-d69b-4f09-bc9e-4168b65aaa71/SQL2008UnstructuredData.doc

Another very strong point with filestream from my point of view is that is does not eat into the size limit of the express editions of SQL Server which can be very handy