Traditional File Archives and Their Limits
Before databases, every program kept its own files. The problems that followed were not bugs: they were structural. This lesson reproduces them one by one on a real library case.
Beginner
Learning objectives
- Describe how a traditional file archive is organised and why the definition of the data lives inside the programs
- Recognise redundancy and explain how it produces inconsistency
- Distinguish the six classic problems of file-based systems with concrete examples
- Explain why 'putting everything in one file' does not solve them
- State which problems are solved by a DBMS and which are only moved
Prerequisites
- Data and Information
- Information Systems and Computer Systems
Introduction
In the previous lesson the database was defined as the shared, consistent, persistent and controlled memory of an information system. That definition only makes sense against its opposite. This lesson describes the opposite: the traditional file archive, the way organisations stored data before database management systems, and the way many small applications still do.
The point is not historical. Every one of the problems described here reappears, unchanged, in modern systems whenever a fact is copied into two places and nobody is responsible for keeping the copies aligned: two microservices with their own copy of the customer, a spreadsheet e-mailed between offices, a cache that is never invalidated. Learning to recognise the shape of these problems is more durable than learning the name of any product.
How a traditional archive is organised
The organising principle is simple: one application, one set of files.
Each program declares, inside its own source code, the layout of the records it needs:
LOANS.DAT — record layout declared in loans.cob
1-6 loan id
7-12 member code
13-18 copy code
19-26 loan date (YYYYMMDD)
27-34 due date (YYYYMMDD)
35-35 status (O = open, C = closed)
Three properties follow from this layout, and all three are the root of the problems below:
- The meaning of the data is implicit. The file says "positions 7 to 12 hold a member code". It does not say that a member code must exist in another file, that it is six characters long, or that it identifies a person.
- The definition is duplicated in every program. If a second program reads
LOANS.DAT, it must repeat the same offsets. If the offsets change, every program must be recompiled — and the one that is forgotten reads garbage. - The file belongs to the program. Access is granted by file permission, not by meaning: a program that may read the file may read everything in it.
A library with three programs
Consider a library that computerised one department at a time. It has three programs, written in different years:
| Program | Purpose | Its files |
|---|---|---|
PRESTITI |
Registers and closes loans at the desk | LOANS.DAT, MEMBERS.DAT |
CATALOGO |
Catalogues works and copies | WORKS.DAT, COPIES.DAT |
ACQUISTI |
Manages orders to suppliers | ORDERS.DAT, SUPPLIERS.DAT, WORKS.DAT |
Notice two things immediately: WORKS.DAT is used by two programs, and the member's address exists in MEMBERS.DAT while the loan desk also needs the member's name and phone number to send reminders — so a second, smaller copy of the member data appears in LOANS.DAT.
MEMBERS.DAT (used by PRESTITI)
member code | surname | name | address | city | phone | max loans | status
LOANS.DAT (used by PRESTITI)
loan id | member code | member name | member phone | copy code | loan date | due date | status
The duplication is not an accident or a sign of incompetence: it is the rational choice of a programmer who needs the phone number without opening another file and without a mechanism to join them. It is exactly the choice that a database makes unnecessary.
The six problems
1. Redundancy
Redundancy
Redundancy is the presence of the same fact in more than one place.
In the example, the member's name and phone number are stored in MEMBERS.DAT and repeated in every row of LOANS.DAT. With 400 members and 12 000 loans a year, the same phone number is written 12 000 times.
Redundancy is expensive in three currencies:
- Space — the smaller cost, and the one people usually think of first.
- Update effort — correcting a phone number means finding every place it was copied.
- Risk — every copy is an opportunity to disagree with the others.
2. Inconsistency
Inconsistency
Inconsistency is the situation in which two copies of the same fact hold different values.
The member changes phone number. The clerk updates MEMBERS.DAT — the program at the desk does that automatically. But LOANS.DAT keeps the old number in twelve thousand rows, and the reminder service, which reads LOANS.DAT, keeps calling the old number.
Nothing failed. No error was reported. The database — if we may call it that — now holds two answers to the question "what is this member's phone number?", and which one you get depends on which program asks.
This is the most important sentence of the lesson: redundancy does not cause inconsistency by itself; it makes inconsistency possible, silent and permanent. Without redundancy there is nothing to disagree with.
3. Difficulty of access
The data exist, but getting an answer requires writing a program.
The director asks: "Which sections had more than 50 loans last month?" In a database this is one query. In the file archive it is a project:
- Write a program that reads
LOANS.DAT. - For each loan, find the copy in
COPIES.DAT. - For each copy, find the work in
WORKS.DAT. - Group by section and count.
- Handle the fact that
WORKS.DATis also written byACQUISTI, so the layout may have changed.
Because each new question costs a program, organisations stop asking. The archive answers yesterday's questions and blocks tomorrow's — a phenomenon with a precise name: the file archive is not programmable by the user, only by the programmer.
4. Data isolation
Data isolation
Data isolation is the difficulty of combining data that live in different files, because their structure is known only to the program that owns them.
The information needed for the director's question is spread over three files with three layouts, three update histories and possibly three incompatible conventions (date as YYYYMMDD in one file and DD/MM/YYYY in another; status as O/C in one and 1/0 in another). Every combination is a new translation layer, written once and never reused.
5. Integrity problems
An integrity constraint is a rule that the data must always satisfy: "a loan must refer to an existing member", "a member may hold at most five loans", "a due date cannot precede the loan date".
In a file archive these rules can only live in application code:
- Each program must re-implement them.
PRESTITIchecks the five-loan limit; a new web service that also creates loans will check it too — if someone remembers. - They can be bypassed. Any program with write permission can insert a loan for member
999999, who does not exist. - They cannot be verified globally. There is no way to ask the archive "are there loans pointing to non-existent members?" without writing another program.
- Rules that span files are practically unenforceable. "The seat number of a ticket must not exceed the seats of the room of its session" requires reading three files atomically.
6. Atomicity
Transaction
A transaction is a sequence of operations that must be executed as a single, indivisible unit: either all of them succeed, or none of them takes effect.
Closing a loan and freeing the copy for reservation are two writes in two files. If the machine loses power between them, the archive is left in a state that violates its own rules: the loan is closed but the copy still appears on loan, or the reverse. The file archive has no mechanism to undo the first write, because "the first write" is already on disk.
The same problem appears in a much more common form: two clerks, two programs, one file. Each program reads the file, modifies one record and rewrites it. If both read before either writes, the second rewrite silently erases the first clerk's change. This is the lost update, and it is invisible: no error, no message, a fact simply disappears.
7. Concurrency
Two people working at the same time on the same file need coordination. In a file archive coordination is either absent (and updates are lost) or implemented with file locks — which are coarse: locking LOANS.DAT blocks every loan in the library, not just the two records involved.
8. Security
Access control is applied to the file, not to the fact. A clerk who must see the loans of the day receives permission to read LOANS.DAT — and therefore also sees the loans of every other clerk, every member's phone number, and the history of the last ten years. There is no way to say "this user may see only the loans of their own branch", because that sentence is about the meaning of the data, and the file system does not know the meaning.
9. Backup and recovery
Copying files while programs are writing them produces inconsistent backups: a copy in which some records are from 10:00 and others from 10:01. Restoring such a backup re-creates exactly the inconsistencies the organisation was trying to eliminate. Consistent backup requires stopping the system — which for a library, a bank or a hospital is not an option.
Summary table
| Problem | Symptom | Root cause | Solved by a DBMS? |
|---|---|---|---|
| Redundancy | Same fact in many places | No shared definition | Partly — normalisation removes it; deliberate redundancy remains possible |
| Inconsistency | Two different answers to one question | Redundancy without control | Yes — single copy + constraints |
| Difficulty of access | Every question needs a program | No query language | Yes — declarative queries |
| Data isolation | Files with incompatible formats | Structure known only to programs | Yes — shared catalogue of data |
| Integrity | Invalid data accepted | Rules in application code | Yes — constraints enforced centrally |
| Atomicity | Half-completed updates after a crash | No transaction mechanism | Yes — transactions |
| Concurrency | Lost updates, blocked files | No fine-grained control | Yes — concurrency control |
| Security | All-or-nothing file permissions | Permissions on files, not facts | Yes — privileges on data and views |
| Backup/recovery | Inconsistent copies | Copying while writing | Yes — consistent dumps, logs |
Why "one big file" is not the answer
The natural reaction is: "Then let us put everything in one file." It does not work, for three reasons.
- Redundancy does not disappear, it hides. One file can still contain the member's phone number in every loan record. The problem is not the number of files, it is whether each fact has a single home.
- Integrity rules still have no home. A single file has no way to declare that a member code must exist, that a due date must follow a loan date, or that a member may not exceed five open loans. The rules stay in the programs.
- Concurrency becomes worse. One file means one lock for everybody. The library stops when two clerks work at the same time.
The problems of the file archive are not problems of files. They are problems of absent shared meaning: no central definition of what the data are, no central enforcement of the rules, no unit of work that can be undone. The database approach, which the next lesson introduces, is a set of answers to exactly those three absences.
Find the anomalies before they happen
A gym keeps two files:
MEMBERS.DAT
member code | surname | name | address | city | phone | plan | monthly fee | enrolment date
ATTENDANCES.DAT
attendance id | member code | member surname | member name | plan | monthly fee | date | entry time
The gym has 900 members and about 40 000 attendances a year. The desk program writes both files; a statistics program reads only ATTENDANCES.DAT.
- Which facts are redundant? How many times is the monthly fee written in a year?
- Describe two concrete inconsistencies that can arise, and say which program would show each.
- A member changes plan from Basic to Plus. List every action the archive requires, and what happens if one is forgotten.
- The owner asks: "How many attendances per plan in the last 90 days, per weekday?" Can the statistics program answer? What is missing?
- The desk program crashes while writing an attendance after having already updated the member's fee. What is the state of the archive?
- Would merging the two files into one solve these problems? Justify your answer in two sentences.
Show the commented solution
1. Redundancy. Surname, name, plan and monthly fee are stored in MEMBERS.DAT and repeated in every attendance row. With 40 000 attendances a year, the monthly fee is written 40 000 times, plus once per member in the members file. The member's name and surname are written 40 000 times as well.
2. Inconsistencies.
- The member changes plan. If only
MEMBERS.DATis updated, the statistics program (which readsATTENDANCES.DAT) keeps counting that member under the old plan, while the desk program shows the new one. Two programs, two answers. - A member corrects a misspelt surname. If the correction is applied only to
MEMBERS.DAT, the attendance report keeps the wrong surname for every past visit. Note that here the "old" value is not even wrong for the past — which shows that some redundancy is legitimate history, and that distinguishing the two cases requires a design decision, not a rule of thumb.
3. Plan change. The correct sequence is: update the plan in MEMBERS.DAT; update the fee in MEMBERS.DAT; update every attendance row of that member (40 000 rows a year, so realistically the whole file); update the statistics program's expectations if it caches anything. If the last step is forgotten, the archive holds two plans for the same person and the annual report is wrong in a way that nobody will notice until it is questioned.
4. The owner's question. The statistics program can answer only if the plan is stored in the attendance row — which is exactly the redundancy we have just condemned. Without that redundancy it would need MEMBERS.DAT, but that file contains the current plan, not the plan the member had on the day of the visit. So the question reveals a real modelling problem: the plan of a member on a given date is a fact that changes over time. The archive must store it explicitly — but once, in a structure designed for it (in Module 3 this becomes an entity; in Module 6 a relation with its own key), not copied into 40 000 rows. This is the difference between controlled and uncontrolled redundancy.
5. Crash. The archive is left with the fee updated and the attendance missing — or the reverse, depending on the order of the writes. The state violates no file-level rule, so nothing reports it. Only a reconciliation program, written later, could discover it. This is the atomicity problem: the two writes should form one transaction.
6. One big file? No. The redundancy would survive (the fee can still be written in every record), the integrity rules would still have no home, and the single file would need a single lock, serialising the desk. The problem is the absence of a shared definition and of central enforcement, not the number of files.
What this exercise teaches. Every question above maps to one of the six problems, and the fourth shows the most delicate point of the whole course: redundancy must be designed, not eliminated blindly. A fact that changes over time (the plan on a date) must be stored once, with its date — that is a modelling decision, and it belongs to Module 3.
Common mistakes
- "The problem is the file format." Moving from fixed-length records to CSV, JSON or XML changes nothing: the definition is still inside the programs and the redundancy is still uncontrolled.
- "One file, one table, problem solved." See the section above: the number of files is not the issue.
- "Redundancy is always bad." Uncontrolled redundancy is bad. Controlled, declared redundancy is a legitimate performance technique (Module 7).
- "A DBMS solves these problems automatically." A DBMS provides mechanisms. A database with no constraints, no transactions and no privileges reproduces every problem of this lesson.
- "Our application enforces the rules, so we do not need constraints." It does, until a second application, an import script or a manual correction writes to the same data. The rule must live where the data live.
Summary
- A traditional archive is a set of files whose structure is defined inside the programs that use them, with no shared definition of the data.
- Its three structural properties — implicit meaning, duplicated definitions, program-owned files — generate nine problems: redundancy, inconsistency, difficulty of access, data isolation, integrity, atomicity, concurrency, security, backup.
- Redundancy makes inconsistency possible, silent and permanent.
- Atomicity and concurrency are not performance issues: they decide whether a fact can disappear without anyone noticing.
- Security on files is all-or-nothing, because permissions are attached to files and not to the meaning of the data.
- Merging files does not help: the missing pieces are a shared definition, central enforcement and a unit of work that can be undone.
Verification questions
- Why is a modern set of microservices, each with its own copy of the customer, a traditional file archive in disguise?
- A library updates a member's address in one file and not in another. Which problem is this, and which one made it possible?
- Why can a file archive not answer an unexpected question without a programmer?
- Explain the difference between redundancy and inconsistency in one sentence each.
- Give an example of a rule that a single file could not enforce even if it contained all the data.
Show the answers
-
Because each service defines the meaning of "customer" inside itself and keeps its own copy; there is no shared definition and no single home for the fact. The symptoms are the same: a change of address is applied in one place and not in the others.
-
The inconsistency is the two different addresses. The redundancy (the same address stored twice) is what made it possible; without a second copy there would be nothing to disagree.
-
Because the archive has no query language: the structure is known only to the programs, so every new question requires new code. In a database the same question is a declarative query over a shared, documented structure.
-
Redundancy = the same fact stored in more than one place. Inconsistency = two copies of the same fact holding different values.
-
"A loan may not refer to a member that does not exist" — if everything is in one file, a file system can enforce that a field is non-empty, but not that its value appears in another set of records. Rules that relate facts to facts need a mechanism that understands the data, which is what a DBMS provides.
Where to go next
- Introduction to databases: the database approach as the answer to the three absences identified here.
- Database management systems: which mechanism solves which problem, and at what cost.
- Data and information: if you want to revisit the vocabulary these problems are built on.