| Lesson 2 || Relational vs. flat file databases |
| Objective ||Understand the Difference between Relational and Flat File Databases. |
Understand the Difference between Relational Databases and Flat Files
Prior to using SQL, typical systems used a flat file
approach. A flat file is a computer file that can
- only be read or written sequentially.
- consists of one or more records.
- Each record contains one or more field instances.
- Each field instance can contain a data value, or be omitted.
Some definitions state that all records must be of the same type and this restriction is usual when discussing a flat file database.
However, most usages allow a flat file to have more than one record type.
History of Flat Files
Flat files date back to the earliest days of computer processing. Originally flat files were stored on punch cards, paper tape, or magnetic tape and these are inherently sequential.
Flat files are still widely used, even for files stored on a disk. One reason is that sequential access is faster than indexed access, (also known as random access or direct access).
Flat files are often used to transmit data between batch processing systems, especially on mainframes.
Flat File Database
A flat file database contains information that, as often as possible, is stored in the order in which it is needed.
The approach of a flat file database is contrary to the approach of a relational database, in which how information is stored does not matter; how it is retrieved does.
Flat files use techniques such as keeping track of pointers from one set of information to another.
These pointers, called linked lists in many approaches, are difficult for the developer to maintain. Any time someone adds information to the database, the pointers have to be updated to show the next piece of information and the previous set of information.
The management of these pointer updates is challenging for database developers when working with legacy flat file technology.
Relational databases do not use flat file approaches. Instead, relational databases focus on storing information efficiently for the operating system and database package.
This is separate from the emphasis on providing a means for easy information retrieval from the system. Data can be accessed or organized in different ways without the need to make accompanying changes to the tables.
Characteristics of a Flat File
A flat file is like a large container of all your data, similar to a single table where you store all the data. The data can be structured in nature consisting of multiple rows and columns with each column corresponding to an attribute.
For example, a single record can be written into the flat file as multiple data items delimited with space or comma. A CSV file is a good example of a flat file
to store records as an in the case of an email list. Usually you will store fixed size records in a flat file.
One disadvantage of a flat file is that it does not give you any structure or relations if you are planning to build a database with multiple flat files.
On the other hand, with a RDBMS, one can
- relate multiple tables,
- create parent child relationships,
- enforce constraints,
- index the data of a particular table.
In the case of a flat file
, users will be directly working on the flat file. They will consider a flat file as a single container of all the data and will potentially work with read()/write()/ and seek() operations to access and manipulate data records in the file.
If we are planning to implement a table like concept, then it has to be done at the file level where we say that each flat file
corresponds to a single table.