Flat-file database

<h2 id="overview">Overview</h2>
<p>Plain text files usually contain one <a href="/facts/Record_(computer_science)/1fvSgenB">record</a> per line.<a class="footnote-ref" id="fnref:1" href="#fn:1"><sup>1</sup></a>
</p><p>Examples of flat files include <a href="/facts/%2fetc%2fpasswd/zumE69MQ">/etc/passwd</a> and <a href="/facts/%2fetc%2fgroup/23lndCN4">/etc/group</a> on <a href="/facts/Unix-like/YAKrAxzd">Unix-like</a> operating systems. Another example of a flat file is a name-and-address list with the fields <i>Name</i>, <i>Address</i> and <i>Phone Number</i>.
</p><p>Flat files are typically either delimiter-separated or fixed-width.
</p>
<h3>Delimiter-separated values</h3>
<p>In <a href="/facts/Delimiter-separated_values/NyHMsLTN">delimiter-separated values</a> files, the <a href="/facts/Field_(computer_science)/jt622EDb">fields</a> are separated by a character or string called the <a href="/facts/Delimiter/wRAQxIKV">delimiter</a>.
Common variants are <a href="/facts/Comma-separated_values/dv1pSR1r">comma-separated values</a> (CSV) where the delimiter is a comma, <a href="/facts/Tab-separated_values/TRyPhWXD">tab-separated values</a> (TSV) where the delimiter is the tab character), space-separated values and vertical-bar-separated values (delimiter is |).
</p><p>If the delimiter is allowed inside a field, there needs to be a way to distinguish delimiters characters or strings that are meant literally. For example, consider the sentence "If I have to, I'll do it myself.". To encode it in CSV, there needs to be a way to prevent the comma from splitting the field. Several <a href="/facts/Delimiter/wRAQxIKV">strategies to prevent delimiter collision</a> exist.
</p>
<h3>Fixed-width formats</h3>
<p>With fixed-width formats, each field has a fixed length with extra <a href="/facts/Space_character/0Um7Qfkq">spaces</a> added as needed. The fixed lengths can be predefined and known ahead of time (i.e. stated in the format's specification), or parsed from a <a href="/facts/Header_(computing)/DSbn3tM1">header</a>.
</p><p>With predefined lengths, fields are limited to a maximum length. The need for longer fields may appear sometime after the format is defined. Possible workarounds include abbreviating phrases, replacing values with links (e.g. a URI pointing to the value), and splitting a file into multiple files.
</p><p>With delimiter-separated formats, determining the field boundaries requires finding the delimiters, which incurs some <a href="/facts/Computational_overhead/e2jgPrHr">computational overhead</a>. This is not needed for fixed-width formats. However, fixed-width formats can lead to unnecessarily large file sizes if fields tend to be shorter than the lengths reserved for them.
</p>
<h3>Declarative notation</h3>
<p>Delimiters can be used alongside a notation stating the length of each field. For example, 5apple|9pineapple specifies the length (5 and 9) of each field. This is called <a href="/facts/String_literal/Fj41vtVm">declarative notation</a>. It has low overhead and trivially avoids delimiter collisions, but it is brittle when edited manually.
</p>
<h2 id="history">History</h2>
<p><a href="/facts/Herman_Hollerith/rxpnvpgz">Herman Hollerith</a>'s work for the <a href="/facts/United_States_Census_Bureau/heKtzoXX">US Census Bureau</a> first exercised in the <a href="/facts/1890_United_States_census/RWFwbtDd">1890 United States census</a>, involving data tabulated via hole punches in paper cards,<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a> is sometimes considered the first computerized flat-file database, as it included no cards indexing other cards, or otherwise relating the individual cards to one another, save by their group membership.
</p><p>In the 1980s, configurable flat-file database <a href="/facts/Computer_application/YQuj7Kog">computer applications</a> were popular on the <a href="/facts/IBM_PC/yhcJGMPL">IBM PC</a> and the <a href="/facts/Apple_Macintosh/8GhnXTK0">Macintosh</a>. These programs were designed to make it easy for individuals to design and use their own databases, and were almost on par with <a href="/facts/Word_processors/BNpRK4WC">word processors</a> and <a href="/facts/Spreadsheet/0cvc1GeB">spreadsheets</a> in popularity. Examples of flat-file database software include early versions of <a href="/facts/FileMaker/AckzyhXv">FileMaker</a> and the <a href="/facts/Shareware/TAhIvJXt">shareware</a> <a href="/facts/PC-File/KBJlb577">PC-File</a> and the popular <a href="/facts/DBase/9U0YCWEo">dBase</a>.
</p><p>Flat-file databases are common and ubiquitous because they are easy to write and edit, and suit myriad purposes in an uncomplicated way.
</p>
<h3>Modern implementations</h3>
<p>Linear stores of <a href="/facts/NoSQL/vvynRKfd">NoSQL</a> data, <a href="/facts/JSON/bs1rUAhd">JSON</a> data, primitive spreadsheets (perhaps comma-separated or tab-delimited), and text files can all be seen as flat-file databases because they lack integrated indexes, built-in references between data elements, and complex data types. Programs to manage collections of books or appointments and <a href="/facts/Address_book/JdYNCHD7">address books</a> may use single-purpose flat-file databases, storing and retrieving information from flat files unadorned with indexes or pointing systems.
</p><p>While a user can write a table of contents into a text file, the text file format itself does not include a concept of a table of contents. While a user may write "friends with Kathy" in the "Notes" section for John's contact information, this is interpreted by the user rather than a built-in feature of the database.  When a database system begins to recognize and codify relationships between records, it begins to drift away from being "flat," and when it has a detailed system for describing types and hierarchical relationships, it is now too structured to be considered "flat."
</p>
<h2 id="example-database">Example database</h2>
<p>The following example illustrates typical elements of a flat-file database. The <a href="/facts/Data/AUBFN97o">data</a> arrangement consists of a series of columns and rows organized into a <a href="/facts/Table_(information)/vQcp2QSb">tabular format</a>. This specific example uses only one table.
</p><p>The columns include: <i>name</i> (a person's name, second column); <i>team</i> (the name of an athletic team supported by the person, third column); and a numeric <i>unique ID</i>, (used to uniquely identify records, first column).
</p><p>Here is an example textual representation of the described data:
</p>
id    name    team
1     Amy     Blues
2     Bob     Reds
3     Chuck   Blues
4     Richard Blues
5     Ethel   Reds
6     Fred    Blues
7     Gilly   Blues
8     Hank    Reds
9     Hank    Blues

<p>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:
</p>
<ul><li>Data types: each column in a database table such as the one above is ordinarily restricted to a specific <a href="/facts/Data_type/fcx5hgVL">data type</a>. Such restrictions are usually established by convention, but not formally indicated unless the data is transferred to a <a href="/facts/Relational_database/pEKGmJLy">relational database</a> system.</li>
<li>Separated columns: In the above example, individual columns are separated using <a href="/facts/Whitespace_(computer_science)/Q5xRIaSz">whitespace</a> characters. This is also called indentation or "fixed-width" data formatting. Another common convention is to separate columns using one or more <a href="/facts/Delimiter/wRAQxIKV">delimiter</a> characters, such as a <a href="/facts/Comma-separated_values/dv1pSR1r">tab or comma</a>.</li>
<li>Relational algebra: Each row or record in the above table meets the standard definition of a <a href="/facts/Tuple/BI1HIxL8">tuple</a> under <a href="/facts/Relational_algebra/7tjTFXdf">relational algebra</a> (the above example depicts a series of 3-tuples). Additionally, the first row specifies the <a href="/facts/Tuple/BI1HIxL8">field names</a> that are associated with the values of each row.</li>
<li>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 <a href="/facts/Database_management_system/VYcpkevb">database management system</a>.</li></ul>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Passwd/zumE69MQ">/etc/passwd</a>, a commonly used flat file, used to detail users in Unix</li>
<li><a href="/facts/Comma-separated_values/dv1pSR1r">Comma-separated values</a> (CSV file format)</li>
<li><a href="/facts/Tab-separated_values/TRyPhWXD">Tab-separated values</a> (TSV file format)</li>
<li><a href="/facts/Berkeley_DB/lWwNrclz">Berkeley DB</a> (typical flat-file database)</li>
<li><a href="/facts/Awk/lAQ0leyj">Awk</a> (classic flat-file processor)</li>
<li><a href="/facts/Recfiles/zdtNQK0j">Recfiles</a> (plain text database file format)</li></ul>

Wikimedia Commons has media related to Flat file models.

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>Fowler, Glenn (1994), "cql: Flat-file database query language", WTEC'94: Proceedings of the USENIX Winter 1994 Technical Conference on USENIX Winter 1994 Technical Conference <a href="http://static.usenix.org/publications/library/proceedings/sf94/full_papers/fowler.a" target="_blank">http://static.usenix.org/publications/library/proceedings/sf94/full_papers/fowler.a</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>Blodgett, John H.; Schultz, Claire K. (1969). "Herman hollerith: data processing pioneer". American Documentation. 20 (3): 221–226. doi:10.1002/asi.4630200307. ISSN 1936-6108. <a href="https://onlinelibrary.wiley.com/doi/abs/10.1002/asi.4630200307" target="_blank">https://onlinelibrary.wiley.com/doi/abs/10.1002/asi.4630200307</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
</ol>

Flat-file database open-in-new

Flat-file database