Limitations of File-Based Storage

While storing data in individual files on your computer or a shared drive seems straightforward initially, this approach quickly runs into significant problems, especially as the amount of data grows or when multiple people need to access it. For example, managing customer information, product inventory, or sales records using only spreadsheets or text files reveals these challenges. Typical difficulties are encountered with simple file-based storage.

Data Redundancy

One of the most common issues is data redundancy. This means the same piece of information is unnecessarily duplicated across multiple files. Imagine a small business storing customer details. The customer's name and address might appear in:

• A file listing all customers (customers.csv).
• A file tracking sales orders (orders_2024_q1.xlsx).
• A separate file for a marketing email list (mailing_list.txt).

Storing the same address in three different places is redundant. It consumes extra storage space, but more importantly, it leads to other problems.

Data Inconsistency

Redundancy often results in data inconsistency. If a customer moves and updates their address, someone needs to remember to change it in all three files (customers.csv, orders_2024_q1.xlsx, and mailing_list.txt). If the update is missed in even one file, the system now holds conflicting information about the same customer. Which address is correct? This inconsistency can lead to errors, such as shipping orders to the wrong location or sending marketing materials to an old address. Ensuring data remains consistent across multiple files manually is difficult and error-prone.

Difficulty in Accessing Data

Retrieving specific information can become a complex task. Suppose you need to find all orders placed by customers living in a particular city. With a file-based system, you might have to:

1. Open the customers.csv file to find which customers live in that city.
2. Open potentially multiple orders files (e.g., orders_2024_q1.xlsx, orders_2023_q4.xlsx, etc.).
3. Manually cross-reference the customer list with the orders in each file.

This process is slow, tedious, and becomes impractical as the number of files and the volume of data increase. Answering even slightly complex questions requires significant manual effort or writing custom programs for each specific query, which is inefficient.

Data Integrity Problems

File systems generally offer limited ways to enforce rules about the data stored within files. For instance:

• How do you ensure that an order_id is always unique across all order files?
• How do you guarantee that the quantity field in an inventory file is always a positive number?
• How do you make sure a date is entered in a consistent format (MM/DD/YYYY vs. YYYY-MM-DD)?

Without mechanisms to enforce such integrity constraints, data quality can suffer due to typos, invalid entries, or inconsistent formats, making the data unreliable. Spreadsheets might offer some basic validation, but it's often applied inconsistently and easily bypassed.

Concurrent Access Anomalies

When multiple users or applications need to access and modify data simultaneously, file-based systems often struggle. Consider two sales agents trying to update the quantity of the same product in an inventory spreadsheet at the exact same time.

• Lost Updates: Agent A reads the quantity (e.g., 10), Agent B reads the quantity (10). Agent A sells 2, writes back 8. Agent B sells 3, writes back 7. Agent A's update is lost because Agent B's write overwrites it. The final quantity should be 5, but it's recorded as 7.
• File Locking: Alternatively, the operating system might lock the entire file when one user opens it, preventing anyone else from making changes until the first user closes it. This avoids lost updates but severely limits productivity, as only one person can work with the file at a time.

These concurrency issues make it hard to build reliable multi-user applications using simple files.

Security and Access Control Issues

Managing who can see or modify specific pieces of data is difficult with individual files. While operating systems provide file-level permissions (read, write, execute), they typically apply to the entire file. It's challenging to grant a user permission to read only certain columns in a spreadsheet (like product names and prices) but not others (like profit margins), or to allow one group of users to update customer addresses while another group can only view them. Managing granular access control across numerous files becomes a significant administrative burden.

These limitations highlight that while file systems are great for storing documents, programs, and unstructured data, they are not well-suited for managing structured information efficiently, consistently, and reliably, especially at scale. This is precisely the gap that database systems are designed to fill.