My company is in the process of evaluating packages to replace our current business application. All but one of them run on a relational database architecture. The other one runs on a flat file database. Could you please explain to me what the differences are between these two architectures and the pro’s and cons of each? I’ve heard that flat file is a lot faster, but that’s about the only good thing about it. Is this true? Are there perhaps some Internet resources I could check out in this regard?
I can’t give you a definitive answer since I don’t know the specific packages you are looking at or what database engines are being used.
First, let’s discuss the term “flat file.” Every platform has engines that allow you to have very low-level control over traversing a file. On the mainframe, ISAM is very popular. I used Btrieve on a LAN. Basically, they allow you to write applications using commands such as “open file using index x,” “retrieve first record,” “get next record,” etc. These operations are fast. You can use these operations to create a schema of “flat files” that obeys every rule of normalization. But you must create them yourself. So, as an example, if you wanted to enforce the concept of a parent-child relationship, you would write the code to maintain the relationship yourself.
Relational databases use SQL as the language to access data. In SQL, we talk about data in sets and relationships. Rather than saying, “get the first record,” we typically say “select all records that have the following criteria.” In many cases it is much faster to program in such a fashion than the method described for flat files. The speed of the operation itself, though, can be either faster, slower, or anywhere in between compared to the same operation in a flat file.
I think you need to widen your criteria for making a choice. Both flat file and relational databases are simply tools to get a job done. There will be situations most appropriate for each. Some of the things to consider include reliability and support for backups.