Where to Store External Application Files

You might be in a situation where other types of filessuch as Microsoft Word documents (.doc files), Microsoft Excel spreadsheets (.xls files), images (.jpg or .bmp files), video (.mpeg files), or even sounds (.wav files)are required to support your application. Where should you store these files when they are necessary to your business application? There are two common methods of storing these files.

• Store the files in the file system and reference them in the database.

• Store the files in LOB (large object) columns in a database table.

In either case, the goal is to store some of the metadata about these files in the database so that they can be referenced in your application. If you use files instead of LOBs, you will need to store file paths, file names, and file types in the table. You may also find it beneficial to store searchable information along with the file information. Appropriate searchable information might include keywords, creator's name, editor's name, creation date, or modified date. By adding this information to the table, your users will be able to search more meaningfully for a particular file. Your application can also use indexing on this information to make searching for a file with these criteria much more efficient.

Tip

You can also implement versioning on files in a table. Depending on your implemention, you can store previous and current versions in the same table. However, you might find it more useful to keep only the current version in the table, which will also make querying faster. If you do not need to use the previous versions often, but you do need to keep them for reference, create a second table that contains previous versions with the necessary versioning information. This will allow you to get the current version quickly, yet still be able to reference previous versions when necessary.

When you store the files in the file system, you get benefits similar to those discussed earlier for storing application settings in a configuration file. You are able to access the files quickly and easily using tried and true techniques, such as Windows Explorer in Microsoft Windows. Your users will be able to access these files outside the context of your application (which may or may not be desirable, depending on how the files are being used). If you are working with files in a document repository situation, referencing the files from a table is probably appropriateit keeps the table streamlined for quick searches. However, if your application depends on certain characterics of the file, for instance if you expect Column A in Excel to contain account numbers, letting users have access to these files puts the integrity of your application at risk.

Why would you store the files in the database? If you store the files in the database using a LOB datatype such as BINARY or IMAGE, you can control access to the files. You don't have to concern yourself with file paths and other operating system-specific issues that would arise if you stored the files outside the database and its application. However, your application will need to account for the various external applications required to open the files. Regardless of whether you choose to store the files in a database table or store them in the file system, you should consider referencing and tracking information about them in the database.

Note

If you are wondering if SQL Server can handle storing files in database tables, especially if you need to store multiple previous versions, check out Microsoft's collaboration toolsWindows SharePoint Services (included with Microsoft Windows Server 2003) and Microsoft Office SharpPoint Portal Services. Both of these applications use SQL Server databases to manage files by storing versions of the files in database tables.