Separate files vs one database: pros and cons

What the concept means

When you store information, you can keep each piece in its own file or you can put everything in a single database. The first option is called the separate-files approach. The second option is the one-database approach. Both ways let you retrieve data later, but they behave very differently when the data grows.

How the two approaches work

Separate files

1. Decide what kind of data you have.
2. Create a file for each kind.
3. Write records into the appropriate file.
4. When you need a record, open the file that should contain it and scan for the line you want.

One database

1. Design a set of tables that capture the relationships between data items.
2. Load all records into those tables.
3. Use a query language to ask for the exact rows you need.
4. The database engine handles searching, joining, and updating.

Both approaches start with the same raw facts, but the way they are organised and accessed changes after step 2.

A worked example with numbers

Imagine a tiny university that offers two courses: Math (ID 101) and History (ID 102). Three students are enrolled:

• Alice, ID 1, takes Math.
• Bob, ID 2, takes Math and History.
• Carol, ID 3, takes History.

Separate-files version

You might create three text files:

• students.txt – one line per student (ID, name).
• courses.txt – one line per course (ID, title).
• enrollments.txt – one line per enrollment (student ID, course ID).

Each file contains only a few lines, so reading a single file is easy. To find out which courses Bob takes, you open enrollments.txt, look for lines that start with 2, then match the course IDs to courses.txt.

One-database version

In a relational database you would create two tables:

Students	Courses
id	id
name	title

and a third linking table called Enrollments with columns student_id and course_id. All nine rows are stored in the engine. A single query such as SELECT title FROM Courses JOIN Enrollments ON Courses.id = Enrollments.course_id WHERE Enrollments.student_id = 2; returns Math and History instantly.

The numbers are tiny, but the steps illustrate the core difference: with separate files you manually locate and match rows; with a database the system does the matching for you.

Mistakes beginners often make

1. Duplicating data – In separate files it is tempting to repeat the same information in several places. If a course title changes, you must edit every file that contains it. Forgetting one copy creates inconsistency.
2. Forgetting relationships – When you add a new student, you might forget to add a matching line in enrollments.txt. The database would reject the insertion because of a foreign-key rule, but separate files give no warning.
3. Slow searches – Scanning a large CSV file is O(n). As the file grows to thousands of rows, the time to find a single record becomes noticeable. A database uses indexes to make the same search O(log n) or better.
4. Hard to enforce rules – Rules such as “a student cannot be enrolled in the same course twice” are easy to enforce with a unique constraint in a database. In separate files you must write extra code to check each insertion.

Where the idea appears in textbooks

• Student-record systems – Most introductory DB textbooks use a university example similar to the one above. They show how a single relational schema replaces many flat files.
• Inventory management – A small shop might keep a products.txt file and a sales.txt file. The textbook demonstrates that a single inventory database can prevent double-selling the same item and can generate real-time stock reports.

You can read more about the university example in the Relata course, which walks through each table and relationship step by step.

Why the choice matters

Choosing between separate files and a single database is not just a technical preference; it affects how you maintain data over time. With separate files you pay almost nothing in upfront setup, but you inherit hidden costs: manual backups, ad-hoc scripts for consistency, and growing latency as the data set expands. A database requires an initial schema design and a running engine, but it gives you built-in transaction support, concurrency control, and the ability to run complex analytical queries without writing custom parsers.

Scalability considerations

Aspect	Separate files	One database
Size limit	Limited by file-system constraints; very large files become unwieldy.	Handles millions of rows with proper hardware and indexing.
Concurrent access	Hard; only one process can safely write at a time without locking mechanisms.	Designed for many users; row-level locks keep data safe.
Backup & recovery	Simple copy of files, but risk of partial snapshots.	Point-in-time recovery, incremental backups, and replication options.
Query complexity	Linear scans; joins must be coded manually.	Declarative queries, joins, aggregates, and stored procedures.

When a project starts with a handful of records, separate files feel natural. As soon as you need to join data from two sources on the fly—say, list all students with a GPA above a threshold while also showing their major—the database’s query engine saves hours of code and reduces bugs.

Real-world scenario: a library system

A public library tracks books, members, and loans. Using separate CSV files you might have books.csv, members.csv, and loans.csv. To find which members currently have overdue books, you would:

1. Load loans.csv and filter rows where due_date < today.
2. For each filtered row, open members.csv and search for the matching member ID.
3. Optionally, open books.csv to display titles.

Each step requires a separate file read, and the logic must handle missing or duplicate IDs. In a relational database you create three tables with foreign-key constraints and a single query:

SELECT m.name, b.title FROM Loans l JOIN Members m ON l.member_id = m.id JOIN Books b ON l.book_id = b.id WHERE l.due_date < CURRENT_DATE;

The database guarantees that every loan refers to an existing member and book, and the query runs in milliseconds even with tens of thousands of rows.

Check yourself

1. Question: If a course title is updated in the separate-files approach, what must you do? Answer: Edit the courses.txt file where the title is stored.
2. Question: Which approach automatically prevents duplicate enrollment records? Answer: The one-database approach, using a unique constraint on the combination of student ID and course ID.
3. Question: How does a database make searching faster than scanning a flat file? Answer: It builds indexes that allow it to locate rows without reading every line.

Understanding the trade-offs helps you decide when a single database is worth the extra setup effort and when a simple file system might be enough for a tiny project.