Four Common Database File Formats
Some database file formats are human readable, some only
with mental gymnastics in the visual cortex, and some are
not readable at all, no matter how hard you squint.
Plain text, human readable database file formats can be
divided into two general types: Those formatted primarily
to be human readable and those formatted primarily to be
software readable.
Because both are plain text files, they can be read by you
and me. The latter, however, the one primarily for software,
can require concentrated attention to read the data.
When downloading or uploading plain text database files with
an FTP program, do so as ASCII/plain text files instead of
binary. When deciding which FTP mode to use, the content
of the file determines the answer, not the file name
extension.
The information in this article about the different types
of files is rather general. Databases contain varied amounts
and types of information and serve different purposes.
Statements about certain file types may not hold true for
all instances.
Plain Text for Humans
Database files intended for humans are generally multi-line,
visually formatted records. The database might contain, for
examples, a list of names and addresses, a collection of
narratives from an opinion poll, or a file of recipes.
Software can easily append information to the file. Software
might also read and parse the file for certain information.
It can, however, require much coding to programmatically
alter information already stored in the database.
Because it's plain text and intended for humans, manually
updating the information with a plain text word processor
can be easy.
Plain Text for Software
These database files are plain text, usually one line per
record, often referred to as flat file databases. Fields
within the record are separated with a unique character
or sequence of characters.
Some software programs that maintain databases with other
formats can export the information into a flat file format
that other software can import. CSV (Comma Separated Values)
and tab-separated values are examples of flat file databases
that can be imported into most popular spreadsheet software
programs.
Flat file databases are quite common on Internet servers.
When a quick dump of information or activity logs are
required, this type of file is often the quickest and
easiest to implement.
Many flat file databases can easily be read by humans. Some
not so easily. The differences are the value separators used
and the information the fields contain.
Flat file databases are made for software to read. Depending
on the size of the data records (the length of the lines), a
specific record in a 5,000-record flat file might be found
about as fast as a record in a DBM or SQL database (see next
two sections).
Software can easily append information to these types of
files. Modifying existing information, however, can be
a programming challenge. Often a "solution" to modifying
the information is to rewrite the entire file.
(Master Form V4 can create
and append records to pretty much any plain text database
file format you can dream up.)
Databases Created with a Perl DBM Module
Imagine the pleasure of programming when information in a
database file can be manipulated as easily as information
in memory. That's what Perl DBM modules provide. The DBM
modules take care of all file reading, adding, updating
and closing.
(DBM is an acronym for DataBase Manager.)
When using FTP to transfer this type of file, use binary
mode.
In addition to easy programming, a DBM database file has
quick access, faster than most database types except small
flat file databases. When speed is of the essence and files
are or might become rather large (5,000+ records), a DBM
file might be a good way to go.
The number of records a DBM module can easily handle depends
on the amount of information in the record, the size of the
keys, and the DBM module being used. Your programmer might
recommend which DBM module is best for your data
requirements.
See the "Backup/Restore & Export/Import &
Server Move Perl DBM Database Tools" blog post
for information about backing up and restoring DBM files.
SQL Databases
SQL is an acronym for Standard Query Language. This refers
to a programming or pseudo-programming language that uses
consistent commands to interact with the database,
regardless of the operating system or the software being
used -- so long as the software works with SQL.
Sometimes an SQL database is the best choice. This is
especially true if the database will be large, the fields
are of different types -- numbers, blocks of text, and dates
all stored in the same record, for example -- or fields will
be individually accessed or modified many times.
MySQL is a common SQL database interface that is fairly
reliable.
Tools exist to back up and restore SQL database files.
During an inactive period, a flat file database might be
exported and used as a backup file. But it may be best to
use the tools specifically designed for the SQL database
in use.
When moving a domain from one server to another, it is
prudent to make a flat file backup of the SQL database.
Even if both servers have specialized software for just
such a move, different configurations or a mistake could
jeopardize your information. With a good flat file backup,
the information can be imported into the new SQL, maybe
with some coding to accomplish it, but at least it can be
done.
I trust this overview of common database file formats will
be useful to you and help you make informed decisions.
Question:
Did you find this article interesting and understandable? How can it be improved?
Your response is anonymous.
When done typing, click anywhere outside the box. [more info]
Will Bontrager
©2005 Bontrager Connection, LLC
Please note:
Articles on this website are presented "as is". However -
If you have a question about a CGI script, HTML, CSS, PHP, or JavaScript
Ask one of our Experts and you'll have your answer!
Click here for details.
