File-based databases have been around since the dawn of computing. We’ve always needed to have a way of storing records of the same kind of information. In the PC world, we ended up with very popular file-based databases. For instance, we’ve probably all heard of the DBase file format. That legendary file format alone has led to several offshoots.
Of course, referring to databases as “file-based,” isn’t very precise since almost every database is file-based at some level. Indexed Sequential Access Method (ISAM) is the more technically correct way of referring to a file-based database. It refers to a technique for managing databases using fixed length records, indexes, and file system locks. It’s a classic item of study in most computer science programs.
However, for a decade or more, we’ve known that this method of building databases has several drawbacks. For that reason, most databases today are built on relational database technologies. Certainly, this is the case of large databases or those with mission critical needs. As organizations continue to reevaluate their database needs, they look to migrating older ISAM-based systems to relational database technologies.
ISAM basics
ISAM databases are relatively simple in their operation. You open a table that you want to work in. You read through the file from the first record all the way to the last record. With the addition of indexes, you read sequentially through the index. The index points you to the correct record in the table file. The program reads the record, does its processing, and moves on.
ISAM files make perfect sense to most programmers since you process one record at a time until all of the records are processed. This slow methodical process follows the basic procedural thinking that every programmer is taught, whether it’s through school or through books and self-teaching.
ISAM also has the advantage of being cheap. In most cases, the licensing costs on a per-user basis for ISAM-based databases are very inexpensive. This is due in part to the less complex nature of these systems and because of the need to keep some competitive advantages over a relational database.
This process is not without its faults. In a networked environment, each client machine must manage its connections to the set of files independently. This makes inserting records a challenging process. It also means that bad timing of a client failing can leave the tables in an inconsistent state. Typically, recovering from these inconsistent states takes a great deal of time. Also, because each client must read each record and transfer it across the network, ISAM databases have the reputation of being very hungry for network bandwidth.
Relational database basics
In a relational database, the tables are traditionally contained within one file. More often than not, there is a central database server that is responsible for controlling access to the data. The client programs make requests to which the server responds.
The benefit is that the centralized server can control access to the database and isolate the underlying data from problems with individual clients. Other typical benefits of a relational database are an online backup method, reliable transactions that either complete or do not modify the database. These reliability characteristics are often a requirement of the line-of-business or mission critical systems that run on relational databases.
An ISAM-based database might require that every file be backed up before batch processing can be restarted. However, a relational database can maintain the state of the database when the backup starts and provide a valid backup even if batch-processing programs are started. Obviously, the performance won’t be the same as if the database wasn’t being backed up; however, it can dramatically increase the amount of time available to run batch programs.
Relational databases have the additional benefit of being more mainstream in today’s world. The delivery of Microsoft SQL Server at very competitive prices and the inclusion of the basic engine in Microsoft’s newer Office suites and operating systems make it a natural choice for people who are looking for a way to upgrade their operations to a point where they can find staff knowledgeable in their technologies. Finding staff with professional relational database experience is easier than finding staff with professional ISAM database experience these days. Keeping closer to what other organizations are using for database technology can be a very compelling reason to change.
These are not the most dramatic differences with a relational database. Relational databases mean the ability to use a query language, such as SQL. SQL is a set-based language, which allows you to select information from multiple tables at once. You describe the collection of information that you want back and then process that information. This means that relational databases are, on the whole, much more considerate of network bandwidth than their ISAM cousins. This can be a big difference if all of the clients of the database aren’t in the same physical location.
Of course, no cake can be had and eaten too, so, naturally, there are some disadvantages to the relational database system. The first disadvantage is just the shear cost. A relational database will cost more to operate than a similar ISAM database. Relational database software and maintenance costs are higher and the software to back up relational databases is typically an add-on piece of backup software that must be purchased separately. In environments with little or no relational database use, the cost of retraining processes on the new system will be unavoidable, thereby raising the bar for costs the first year even further.
The right answer
There is no one right answer when it comes to whether your solution needs to be built on ISAM technology or, more importantly, if it’s time to migrate from ISAM technology to a relational database. However, if you can bear the costs, a relational database has serious advantages in terms of reliability and disaster recovery.
