A flat file database is a database designed around a single table. The flat file design puts all database information in one table, or list, with fields to represent all parameters. A flat file may contain many fields, often, with duplicate data that are prone to data corruption. If you decide to merge data between two flat files, you need to copy and paste relevant information from one file to the other. There is no automation between flat files. If you have two or more flat files that contain client addresses, for example, and a client moved, you would have to manually modify the address parameters in each file that contains that client’s information. Changing information in one file has no bearing on other files. Flat files offer the functionality to store information, manipulate fields, print or display formatted information and exchange information with others, through email and over the Internet. Some flat files may be attached to external files, such as text editors, to extend functionality and manage related information.
A relational database, on the other hand, incorporates multiple tables with methods for the tables to work together. The relationships between table data can be collated, merged and displayed in database forms. Most relational databases offer functionality to share data:
Designing flat file databases is simple and requires little design knowledge. Flat files can be developed using just about any database engine. Flat files can be created in relational database engines by not taking advantage of relational design concepts. Designing a relational database takes more planning than flat file databases. With flat files, you may add information, as you deem necessary. With relational databases, you must be careful to store data in tables such that the relationships make sense. Building a relational database is dependant upon your ability to establish a relational model. The model must fully describe how the data is organized, in terms of data structure, integrity, querying, manipulation and storage.
Relational databases allow you to define certain record fields, as keys or indexes, to perform search queries, join table records and establish integrity constraints. Search queries are faster and more accurate when based on indexed values. Table records can be easily joined by the indexed values. Integrity constraints can be established to ensure that table relationships are valid. If you are able to establish a one-to-many relationship in your data tables, you should be using a relational database because a flat file is not sufficient to handle your data processing needs.
Relational databases offer more robust reporting with report generators that filter and display selected fields. Relational databases offer the capability to build your own reporting modules. Most relational databases also offer the capability to import and export data from other software.
There are three primary relational database systems, proprietary, open source and embedded. Proprietary relational databases require the use of proprietary development languages, often times, to complement SQL. Microsoft Access, for example, combines Visual Basic with SQL. Open source databases, such as MySQL, are distributed freely to encourage user development. Embedded, relational databases are packaged as part of other software packages, such as with tax-preparation software packages. The vendor supplies the database, and all manipulation tools, to control the database structure. These databases are, often times, accompanied with tools to provide audit trails of transactions.