Flat file database

From Academic Kids

Missing image
DB-database-icon.png


A flat file database is described by a very simple database model, where all the information is stored in a plain text file, one database record per line. Each record is divided into fields using delimiters or at fixed column positions. The data is "flat", as in a sheet of paper, as compared to a more complex model such as a relational database.

Contents

Example

The classic example of a flat file database is a basic name-and-address list, where the database consists of a small, fixed number of fields: Name, Address, and Phone Number. Another example is a simple HTML table, consisting of rows and columns. These types of database are so common that we often see one without thinking of it as a database at all.

Let us explore an example database that stores the users of an imaginary miniature version of Wikipedia, "Pico".

Version 1.0: Plain text

The simplest implementation of Pico is shown below, inline in this text. The data -- the information itself -- has simply been written out in table form:

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

Note that the data in the first column is "all the same" -- that is, they are all id numbers (serial numbers). Likewise, the data in the second column is "all the same" -- names. We have decided Pico users will gang up into teams, and some belong to the Reds and some to the Blues. All these team designations are found in the third column only. These columns are called "fields".

Also note that all the information in, say, the third line from the top "belongs to" one person: Bob. Bob's id is "2"; his name is "Bob" (no surprise!); and his team is the "Reds". Each line is called a "record". (Sometimes, although this is not strictly correct, the word "field" refers to just one datum within the file -- the intersection of a field and a record.)

The first line is not a record at all, but a row of "field labels" -- names that identify the contents of the fields which they head. Some databases omit this, in which case the question is left open: "What is in these fields?" The answer must be supplied elsewhere.

In this implementation, fields can be detected by the fact that they all "line up": each datum uses up the same number of characters as all other data in the same column; extra spaces are added to make them all the same length. This is a very primitive and brittle implementation, dating back to the days of punch cards.

Today, the same effect is achieved by delimiting fields with a tab character; records are delimited by a newline. This is "tab-separated" format. Other ways to implement the same database are:

"1","Amy","Blues"
"2","Bob","Reds"
"3","Chuck","Blues"
"4","Dick","Blues"
"5","Ethel","Reds"
"6","Fred","Blues"
"7","Gilly","Blues"
"8","Hank","Reds"

-- which is "comma-separated" (CSV) format. We could also write:

1-Amy-Blues/2-Bob-Reds/3-Chuck-Blues/4-Dick-Blues/5-Ethel-Reds/6-Fred-Blues/7-Gilly-Blues/8-Hank-Reds/

All are equivalent databases.

There is not much we can do with such a simple database. We can look at it; if we can edit it at all, we can add new records to it; we can edit the contents of any field. We can import the entire database into another tool. Sometimes this is enough, but only for the most basic needs. Beyond those, we turn to a tool designed for the task.

Version 2.0: Excel

Microsoft Excel has a simple database tool; it uses the term "list", but this means the same thing as "flat file database". Note that the data is still organized in rows and columns; the field labels are up top. Clicking a control allows us to sort the database, in this case by team. Note all the Blues are now on top, ahead of the Reds.

Missing image
DB-xl-users-01.png
Excel database
Missing image
DB-xl-users-02a.png
Sorting
Missing image
DB-xl-users-02b.png
Sorted



When we created the database, the id numbers were already in order; but we might as well have created them in any random order, and then sorted the list by name or by id. Excel is a spreadsheet tool, designed for calculations with numbers, often very complex. If our database included a "score" field, and we wished to find the total, the average, or the standard deviation, Excel would, indeed, excel.

Missing image
DB-users-list-01.png
FileMaker database

Version 3.0: FileMaker


Missing image
DB-users-form-01.png
Form view
Missing image
DB-users-form-02.png
Another form layout
The final stage of development of a flat file is shown in a true database management tool, FileMaker. Again, data is shown in rows and columns, with field labels on top. This is called a "list layout" or view. We can also define a "form layout", which shows only one record at a time. For databases with many fields, this may be more convenient. The data itself does not change as we go from form to list layouts; only the way we look at it. We can even design a special form, with unusual formatting; say, to print out as a conference nametag.



Missing image
DB-sort-users-01.png
Sorting by team
Sorted
Enlarge
Sorted
This kind of implementation allows us to both sort and "find" records. If we sort records by team; we get the same result as we did in Excel -- it's the same function. We can sort on any field, or on more than one field, and choose which field has priority. If we sort on a number field, we sort from 0 to 9; if we sort on a text field, from A to Z. We can sort in ascending order, descending order, or (if previously defined) based on an ordered value list, such as "Days of the Week". (Technically, we are already getting into relationships, because that value list is really a kind of database itself!)



Missing image
DB-users-find-01a.png
Finding "Fred"
Missing image
DB-users-find-01b.png
"Fred" Found
We can find, say, the record of the user whose name is "Fred".



Missing image
DB-users-find-02a.png
Finding "Reds"
Missing image
DB-users-find-02b.png
Found 3 records; 1 shown
Missing image
DB-users-find-02c.png
All 3 found records shown

If we find all records of the Reds team, we will only see the first match in form layout; but we can switch to list layout to see all 3 matches at once. We can also do complex operations, such as finding all Blues team members, then sorting the found records by name.



Missing image
DB-df-users-01.png
Defining fields for a database
Missing image
DB-df-id-options-users-01.png
Defining options for a field
An advantage of a database tool is that it is specifically designed for database management. We can add, delete, or edit records or individual units of data. We can add additional records to the file explicitly, via a "New Record" command; we can define certain processes to take place when this happens. We can add additional fields, too, extending the structure. We can choose to control what kind of data may be stored in a given field. For instance, id is defined to hold only a serial number, which is assigned automatically when a new record is created.


This is about the limit of what a simple flat file can do. For more advanced applications, please see Relational database.

Implementation

It is possible to write out by hand, on a sheet of paper, a list of names, addresses, and phone numbers; this is a flat file database. This can also be done with any typewriter or word processor. But many pieces of computer software are designed to implement flat file databases.

Historical implementations

The first uses of computing machines were implementations of simple databases. Herman Hollerith conceived the idea that any resident of the United States could be represented by a string of exactly 80 digits and letters -- name, age, and so forth, padded as needed with spaces to make everyone's name the same length, so the database fields would "line up" properly. He sold his concept, his machines, and the punched cards which both recorded and stored this data 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.

Throughout the years following World War II, primitive electronic computers were run by governments and corporations; these were very often used to implement flat file databases, the most typical of which were accounting functions, such as payroll. Very quickly, though, these wealthy customers demanded more from their extremely expensive machines, which led to early relational databases. Amusingly enough, these early applications continued to use Hollerith cards, slightly modified from the original design; Hollerith's enterprise grew into computer giant IBM, which dominated the market of the time. The rigidity of the fixed-length field, 80-column punch card driven database made the early computer a target of attack, the butt of jokes, an idol worshipped, and a mystery to the common man.

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 offered limited relational capabilities, allowing some data to be shared between files.

Contemporary implementations

Today, there are few programs designed to allow novices to create and use general-purpose flat file databases. This function is implemented in Microsoft Works (available only for some versions of Windows) and AppleWorks, sometimes named ClarisWorks (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 pre-defined set of fields. Examples are programs to manage collections of books or appointments. Some small "contact" (name-and-address) database implementations essentially use flat files.

XML is now a popular 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 would be incorrect to describe this type of database as conforming to the flat-file model.

Example implementation

Some example graphic screenshot images were taken from mockup implementations using Microsoft Excel or FileMaker Pro for Macintosh. These are not true flat file databases, but are simply made to look like one. It is easier to use the more powerful tool. Always keep in mind that the idea of a flat file is separate from any implementation of one.

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 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 MySOL uses the term "Query"; but the concept is the same. FileMaker "files" are equivalent to MySQL "tables", 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 database implementation.

See also

Navigation

Academic Kids Menu

  • Art and Cultures
    • Art (http://www.academickids.com/encyclopedia/index.php/Art)
    • Architecture (http://www.academickids.com/encyclopedia/index.php/Architecture)
    • Cultures (http://www.academickids.com/encyclopedia/index.php/Cultures)
    • Music (http://www.academickids.com/encyclopedia/index.php/Music)
    • Musical Instruments (http://academickids.com/encyclopedia/index.php/List_of_musical_instruments)
  • Biographies (http://www.academickids.com/encyclopedia/index.php/Biographies)
  • Clipart (http://www.academickids.com/encyclopedia/index.php/Clipart)
  • Geography (http://www.academickids.com/encyclopedia/index.php/Geography)
    • Countries of the World (http://www.academickids.com/encyclopedia/index.php/Countries)
    • Maps (http://www.academickids.com/encyclopedia/index.php/Maps)
    • Flags (http://www.academickids.com/encyclopedia/index.php/Flags)
    • Continents (http://www.academickids.com/encyclopedia/index.php/Continents)
  • History (http://www.academickids.com/encyclopedia/index.php/History)
    • Ancient Civilizations (http://www.academickids.com/encyclopedia/index.php/Ancient_Civilizations)
    • Industrial Revolution (http://www.academickids.com/encyclopedia/index.php/Industrial_Revolution)
    • Middle Ages (http://www.academickids.com/encyclopedia/index.php/Middle_Ages)
    • Prehistory (http://www.academickids.com/encyclopedia/index.php/Prehistory)
    • Renaissance (http://www.academickids.com/encyclopedia/index.php/Renaissance)
    • Timelines (http://www.academickids.com/encyclopedia/index.php/Timelines)
    • United States (http://www.academickids.com/encyclopedia/index.php/United_States)
    • Wars (http://www.academickids.com/encyclopedia/index.php/Wars)
    • World History (http://www.academickids.com/encyclopedia/index.php/History_of_the_world)
  • Human Body (http://www.academickids.com/encyclopedia/index.php/Human_Body)
  • Mathematics (http://www.academickids.com/encyclopedia/index.php/Mathematics)
  • Reference (http://www.academickids.com/encyclopedia/index.php/Reference)
  • Science (http://www.academickids.com/encyclopedia/index.php/Science)
    • Animals (http://www.academickids.com/encyclopedia/index.php/Animals)
    • Aviation (http://www.academickids.com/encyclopedia/index.php/Aviation)
    • Dinosaurs (http://www.academickids.com/encyclopedia/index.php/Dinosaurs)
    • Earth (http://www.academickids.com/encyclopedia/index.php/Earth)
    • Inventions (http://www.academickids.com/encyclopedia/index.php/Inventions)
    • Physical Science (http://www.academickids.com/encyclopedia/index.php/Physical_Science)
    • Plants (http://www.academickids.com/encyclopedia/index.php/Plants)
    • Scientists (http://www.academickids.com/encyclopedia/index.php/Scientists)
  • Social Studies (http://www.academickids.com/encyclopedia/index.php/Social_Studies)
    • Anthropology (http://www.academickids.com/encyclopedia/index.php/Anthropology)
    • Economics (http://www.academickids.com/encyclopedia/index.php/Economics)
    • Government (http://www.academickids.com/encyclopedia/index.php/Government)
    • Religion (http://www.academickids.com/encyclopedia/index.php/Religion)
    • Holidays (http://www.academickids.com/encyclopedia/index.php/Holidays)
  • Space and Astronomy
    • Solar System (http://www.academickids.com/encyclopedia/index.php/Solar_System)
    • Planets (http://www.academickids.com/encyclopedia/index.php/Planets)
  • Sports (http://www.academickids.com/encyclopedia/index.php/Sports)
  • Timelines (http://www.academickids.com/encyclopedia/index.php/Timelines)
  • Weather (http://www.academickids.com/encyclopedia/index.php/Weather)
  • US States (http://www.academickids.com/encyclopedia/index.php/US_States)

Information

  • Home Page (http://academickids.com/encyclopedia/index.php)
  • Contact Us (http://www.academickids.com/encyclopedia/index.php/Contactus)

  • Clip Art (http://classroomclipart.com)
Toolbox
Personal tools