Basic SQL   «Prev  Next»
Lesson 3 Key differences between relational and flat file databases
Objective Understand the key differences between relational and flat file databases.

Key Differences between Relational and Flat File Databases

The key points to understand about relational and flat file databases are the following:
  1. Relational databases differ from older, non-relational databases because information is stored at random in the relational model, then organized as it is retrieved.
  2. Older databases typically use more proprietary query approaches, often hard-coded applications, as compared with dynamic queries, submitted by both the user and applications.
  3. SQL relies on the foundation of a relational database. The engine you are working with must support the SQL characteristics to querying. Compatibility with SQL is not an assumed capability of older database engines.

Evolution of Database Modeling

The evolution of database modeling occurred when each database model improved upon the previous one. The initial solution was no database model at all using a file system (also known as flat files). You can examine files in the file system of the operating system by
  1. running a dir command in DOS,
  2. an ls command in UNIX, or
  3. searching through the Windows Explorer in Microsoft Windows.
The problem that using a file system presents is no database structure at all.

File Systems

Using a file system database model implies that no modeling techniques are applied and that the database is stored in flat files in a file system, utilizing the structure of the operating system alone. The term flat file is a way of describing a simple text file, containing no structure whatsoever and data is simply dumped in a file.
By definition, a comma-delimited file (CSV file) contains structure because it contains commas. By definition, a comma-delimited file is a flat file. However, flat file databases in the past tended to use huge strings, with no commas and no new lines and data items were found based on a position in the file. In this respect, a comma-delimited CSV file used with Excel is not a flat file.
Any searching through flat files for data has to be explicitly programmed. The advantage of the various database models is that they provide some of this programming for you. For a file system database, data can be stored in individual files or multiple files. Similar to searching through flat files, any relationships and validation between different flat files would have to be programmed and likely be of limited capability.
If you are interested, here is some additional information about other approaches to databases.
In the next lesson, we will use an analogy to drive the relational database point home.

Database Flat File - Quiz

Take a brief quiz to make sure to understand these flat file and relational database concepts.
Database Flat File - Quiz