Flat-file database

A flat file database describes text any of various means to encode a database model (most commonly a table) as a single file (such as .txt or .ini).

Example of a Flat File Model.

Overview

A "flat file" is a plain text or mixed text and binary file which usually contains one record per line or 'physical' record (example on disc or tape). Within such a record, the single fields can be separated by delimiters, e.g. commas, or have a fixed length. In the latter case, padding may be needed to achieve this length. Extra formatting may be needed to avoid delimiter collision. There are no structural relationships between the records.

Typical examples of flat files are /etc/passwd and /etc/group on Unix-like operating systems. Another example of a flat file is a name-and-address list with the fields Name, Address, and Phone Number.

A list of names, addresses, and phone numbers written on a sheet of paper is a flat file database. This can also be done with any typewriter or word processor. A spreadsheet or text editor program may be used to implement flat file databases.

History

The first uses of computing machines were implementations of simple databases. Herman Hollerith conceived the idea that census data could be represented by holes punched in paper cards and tabulated by machine. He sold his concept to the US Census Bureau; thus, the Census of 1890 was the first ever computerized database—consisting, in essence, of thousands of boxes full of punched cards.

Hollerith's enterprise grew into computer giant IBM, which dominated the data processing market for most of the 20th century. IBM's fixed-length field, 80-column punch cards became the ubiquitous means of inputting electronic data until the 1970s.

In the 1980s, configurable flat-file database computer applications were popular on DOS and the Macintosh. These programs were designed to make it easy for individuals to design and use their own databases, and were almost on par with word processors and spreadsheets in popularity. Examples of flat-file database products were early versions of FileMaker and the shareware PC-File. Some of these, like dBase II, offered limited relational capabilities, allowing some data to be shared between files.

Contemporary implementations

FairCom's c-tree is an example of a modern enterprise-level solution, and spreadsheet software is often used for this purpose, but aside from that there are very few programs available today that would allow a novice to create and use a general-purpose flat file database. This functionality is implemented in Microsoft Works (available only for some versions of Windows) and Apple Works, sometimes named Classwork (available for both Macintosh and Windows platforms). Over time, products like Borland's Paradox, and Microsoft's Access started offering some relational capabilities, as well as built-in programming languages. Database Management Systems (DBMS) like MySQL or Oracle generally require programmers to build applications.

Flat file databases are still used internally by many computer applications to store configuration data. Many applications allow users to store and retrieve their own information from flat files using a per-defined set of fields. Examples are programs to manage collections of books or appointments. Some small address book applications are essentially single-purpose flat file databases. As of 2011 one of the most popular flat file database engines is the SQLite, which is part of the PHP5 standard distribution.

XML is now a popular, but ageing, format for storing data in plain text files, but as XML allows very complex nested data structures to be represented and contains the definition of the data, it is very different from the flat-file model. XML is being gradually replaced by JSON, YAML and possibly other structured text formats.

Terms

"Flat file database" may be defined very narrowly, or more broadly. The narrower interpretation is correct in database theory; the broader covers the term as generally used.

Strictly, a flat file database should consist of nothing but data and, if records vary in length, delimiters. More broadly, the term refers to any database which exists in a single file in the form of rows and columns, with no relationships or links between records and fields except the table structure.

Terms used to describe different aspects of a database and its tools differ from one implementation to the next, but the concepts remain the same. FileMaker uses the term "Find", while MySQL uses the term "Query"; but the concept is the same. FileMaker "files", in version 7 and above, are equivalent to MySQL "databases", and so forth. To avoid confusing the reader, one consistent set of terms is used throughout this article.

However, the basic terms "record" and "field" are used in nearly every flat file database implementation.

Example database

The following example illustrates the basic elements of a flat-file database. The data arrangement consists of a series of columns and rows organized into a tabular format. This specific example uses only one table.

The columns include: name (a person's name, second column); team (the name of an athletic team supported by the person, third column); and a numeric unique ID, (used to uniquely identify records, first column).

Here is an example textual representation of the described data:

id    name    team
1     Amy     Blues
2     Bob     Reds
3     Chuck   Blues
4     Dick    Blues
5     Ethel   Reds
6     Fred    Blues
7     Gilly   Blues
8     Hank    Reds

This type of data representation is quite standard for a flat-file database, although there are some additional considerations that are not readily apparent from the text:

• Data types: each column in a database table such as the one above is ordinarily restricted to a specific data type. Such restrictions are usually established by convention, but not formally indicated unless the data is transferred to a relational database system.
• Separated columns: In the above example, individual columns are separated using whitespace characters. This is also called indentation or "fixed-width" data formatting. Another common convention is to separate columns using one or more delimiter characters. There are many different conventions for depicting data such as that above in text. (See e.g., Comma-separated values, Delimiter-separated values, Markup language, Programming language). Using delimiters incurs some overhead in locating them every time they are processed (unlike fixed-width formatting) which may have some performance implications. However, use of character delimiters (especially commas) is also a crude form of data compression which may assist overall performance by reducing data volumes - especially for data transmission purposes. Use of character delimiters which include a length component (Declarative notation) is comparatively rare but vastly reduces the overhead associated with locating the extent of each field.
• Relational algebra: Each row or record in the above table meets the standard definition of a tuple under relational algebra (the above example depicts a series of 3-tuples). Additionally, the first row specifies the field names that are associated with the values of each row.
• Database management system: Since the formal operations possible with a text file are usually more limited than desired, the text in the above example would ordinarily represent an intermediary state of the data prior to being transferred into a database management system.

Practical implementations

• GNU Recutils, a set of tools and libraries to access human-editable, text-based databases called recfiles
• SQLite, software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
• Berkeley DB, a robust flat file database for critical applications which supports ACID transactions
• Borland Reflex
• TextDB, a file-based database designed to handle high loads. (Last updated: 2009)
• Mimesis, an FFDB written in PHP4 that uses multiple files and a heap method of storage.
• MySQL CSV, a storage engine for MySQL 5.x.
• TheIntegrationEngineer How Delimited and Fixed Position or Fixed Width files compare and are used.
• Flat File Checker - Open source data validation application for flat files.
• HXTT JDBC CSV/Text - HXTT JDBC flat file database tool.
• UNIXODBC Text Driver - UNIXODBC included Text Driver; discontinued in upstream - see versions <= 2.2.12 for old code
• CsvJdbc - a JDBC driver for CSV files.
• J-Stels CSV - a JDBC driver for CSV files.