Choosing between storing documents in the database or the file system

Summary

Documents, such as property pictures or activity attachments, can be either be stored in the database or in the file system (usually a folder on the harddisk or on a network share).

Generally speaking, it is recommended to store the documents in the database for single-user installations and on a network share for multi-user installations.

More Information

A document in RealtyWare is any file that is attached to a contact, property or activity. Examples of documents are property pictures, email messages and text files.

By default, RealtyWare stores these documents directly in the database when using a local database file and in the file system or a shared network folder when using a network database.

Documents are compressed before storing them in the database to reduce the amount of data that needs to be transferred to/from the database and to partially compensate for the maximum database size limitations imposed by some database engines.

When storing the documents in the database, these advantages and disadvantages should be taken into consideration:

Everything is stored in one place, simplifying management of the data.
Enhanced and simplified security.
Microsoft SQL Server Compact 3.5 has a mximum database size limitation of 4 GB.
Microsoft SQL Sever 2005 Express Edition has a maximum database limitation of 4 GB.
Microsoft SQL Server 2008 Express has a maximum database size limitation of 10 GB.
It is usually slower to fetch a document from the database then directly reading it from the harddisk or a network share.
A larger database usually also means that queries against this database will take more time to execute, leading to slower performance.

When storing the documents in the file system or a shared network folder, these advantages and disadvantages should be taken into consideration:

Usually better performance, specially with an elevated number of documents.
Database size is kept to a minimum, thus making it unlikely that the maximum database size limitations are ever reached.
The documents can be easily accessed by third party applications, such as indexing services, search appliances and other programs.
Increased complexity as the data is spread between two different places.
All users must have read and write access to the directory or network share where the documents are stored.

As a general rule, it is recommended to:

Use Microsoft SQL Server Compact 3.5 for single-user databases and store the documents directly in the database.
Use Microsoft SQL Server 2008 Express for multi-user databases and store the documents on a network share.

- or -