Data Management

Make room for images in databases

There are a few options available to you when deciding where images will reside in your Web site. You can put them in the database, link them externally, or use in a second database. Here are the pros and cons of each option.

Few of us still have the luxury of working with just text and numbers. As the Internet and multimedia applications expand possibilities, so grows the complexity of the types of data you must store and manipulate. Visit any professional Web site and you’ll find animation, graphics, image files, and dynamic data being bandied about like candy. Keep in mind that a good site always makes these tricks look easy. One of the design decisions your Web site team will face is choosing where to store all those image files. In this article, I’ll discuss the pros and cons of storing those images in the application database.

Storage solutions
For the most part, you have three possible storage solutions from which to choose:
  • Store the image files in the database using the appropriate datatype. This method requires the least amount of maintenance and worry, but significantly increases the size of your database.
  • Link to externally stored image files. Your database will be much smaller, but be prepared to support your choice. In addition, this solution requires a bit of development expertise—there’s no magic linking tool that points to the right image file in the right folder.
  • Store the image files in a second database and link to them in a client/server type arrangement. You will still have to maintain links, but all your images can be treated as one big file.

For your consideration
Many factors will affect your decision and, frankly, there isn’t a clear winner in the debate. Ask most IT project managers for a preference and they almost always respond with, "It depends." Table A lists the issues you’ll have to weigh when making this decision. When you total the plus signs, storing the image with the database wins two to one. Let’s take a minute to discuss these factors and see if storing can maintain that lead.
Table A
Issue Storing Linking
Size of database - +
Back up tasks + -
Recovery tasks + -
Security + -
Scalability + -
Accessibility to other applications - +

The size of a database drastically increases when you store images in the application database. In a simple test using an Access database, I found that a database with just the Employees table (from the Northwind sample database that comes with Access) and a simple bound form for display was 144 KB. When I embedded or linked the employee photo images, using the Access-specific OLE Object datatype, the size of the database ballooned to 940 KB. After adding just nine images, this database was approximately six times larger than it was without the images. Such an increase may be unexpected because it’s twice the size of the actual images. The nine images together equal 369 KB (41 KB each). Yet, the size of the database increased by 796 KB when I stored the images in the database.

Regardless of size, both backup and recovery tasks are simpler if images are stored in the database. Since files are backed up and recovered together, there’s very little chance that the images and corresponding data will be out of sync with one another, or that links to the images will need additional repairs, unless you’re dealing with a serious corruption problem.

When safeguarding sensitive or confidential data it makes sense to store image files in the database. Images inside the database will be easier to protect than individual files. System file security is more difficult to implement. Plus, you have to secure the database and the individual files. Store the images in the database and let the system’s security protect everything.

The developer with a small application and a limited number of images might not care about scalability, but the developer working with thousands of images may be a slave to it. Moving a small number of image files would be a tedious task for sure, but moving thousands would prove difficult at best. Database storage completely eliminates the nuisance and the almost inevitable task of repairing broken links that could occur at any time for any number of reasons.

Accessibility probably isn’t as large an issue as some of the others I’ve reviewed. If you regularly open your image files in third-party software, linking will definitely improve your ability to work with those files. You simply can’t work with those files inside Access using a third-party product. Sharing those image files with another application could also be a problem and linking directly (and easily) to the images simply may not be possible. I recommend that you link to the image file if applications share the same images.

There’s one last consideration that I didn’t add to the table because the advantages and disadvantages seem equally balanced. If you plan to rarely, if ever, modify the image files beyond adding new ones or deleting old ones, database storage presents no challenge. And launching the database to update one image file may not seem like a big deal until you realize that doing so will probably require administrative permission. If your developer relies on a specific department or person for database maintenance, he or she might wait a while for the request to be processed—updating a single image file will probably be low on a list of priorities. On the other hand, the capability to use SQL INSERT, UPDATE, and DELETE statements on data could definitely simplify any maintenance tasks a developer might have. SQL could update images in bulk, seriously reducing the time the developer spends on maintenance tasks.

Fear of commitment
At the risk of being accused of not having an opinion, I believe there’s no cut-and-dried winner in this debate. I’ve merely discussed the major issues that you’ll face when designing your image-displaying database. After weighing the discussions in this article, ultimately, your system and your unique situation will point you to the ideal solution for you.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks