Flat File Databases

Flat files are good for simple data, and you will use them in many scenarios. However, if we need to store large amounts of complex data, the flat file system quickly becomes inefficient. Flat files are also prone to some types of errors. One of these common problems is called data inconsistency.

Let’s suppose that, like in the old ledger image above, we have a flat file to keep track of what the gold miners buy in our general store, and we have records like this:

Customer, date, article, cost

Joe “Goldentooth” Smith, 12-23-1897, Cat gut banjo strings, 0.15$        

Some days later Joe comes back to buy a bottle of whiskey for the New Year’s party:

Joe “Goldentooth” Smith, 12-31-1897, Bottle of whiskey, 0.75$        

We need to write the full name again, and that is redundant. A flat file can have a lot of duplications like this that make it inefficient.

Now let’s say Joe has a fight at the New Year’s party and lost his golden tooth, so now he is called Joe “Missingtooth” Smith. He comes back to the store to buy some soup and the record for this purchase is:

Joe “Missingtooth” Smith, 01-02-1898, Can of soup, 0.05$        

Now, in our flat file database, we have the same customer with 2 different names, which will create a data inconsistency. To solve this problem, we would need to search and find all the previous “Goldentooth” entries for this customer and correct them to “Missingtooth.”

Another inconsistency could be created if an employee accidentally writes “Bottle of whisky” instead of “Bottle of whiskey” to record the sale of this same product. Then if we search in the flat file for all of the “Bottle of whiskey” entries, we will miss the one that is spelled differently.

Another problem could arise If we want to add the address of the customer, so the record would look like this:

Customer, address, date, article, cost

Joe “Goldentooth” Smith, Stinky Hut #23, 02-12-1898, Golden tooth, 20.99$        

It would be really difficult to modify the existing file to add that column between the customer and the date and fill all the missing values in the pre-existing records.

Flat file databases can also be in plain text format. A common example of this type of flat file database is a CSV, or comma-separated values, file. Here, there are no cells, as in a spreadsheet instead, information in each row is separated by plain text characters, called delimiters, such as commas.

Compared to relational databases, the advantages and challenges of flat file databases are as follows:

Advantages

• All records are stored in one file
• Simple to set up and use Efficient for smaller businesses and datasets

Challenges

• Difficult to make changes to the record structure
• Inefficient for large-scale record keeping
• Increased redundancy due to duplicate records
• Data inconsistencies are easy to create

Limitations of Flat Files

• Limited functionality: Flat file databases are limited in their ability to perform complex queries, such as joining multiple tables or filtering data based on specific criteria.
• Data redundancy: Since flat file databases consist of a single table, data redundancy can occur if the same information is stored in multiple records.
• Security: Flat file databases are more vulnerable to security risks, such as unauthorized access or data loss, compared to relational databases.