01 flat files vs relational databases

From Flat Files to Relational Databases

The World on a Single Sheet: The Flat-File Database

At its simplest, a database is just an organised collection of data. The most basic way to store data is in a flat-file database. Imagine a single spreadsheet, a CSV file, or a simple text file where all the information is lumped together in one large table. For a personal address book or a short list of ten favourite songs, this works perfectly well. Each row represents a complete entry, and each column represents a particular attribute.

However, as soon as the data becomes more complex or grows in volume, this simple structure begins to reveal some serious and dangerous flaws. Storing all your data in one place might seem easy at first, but it creates a significant problem known as data redundancy.

Data Redundancy: The Ticking Time Bomb

Data redundancy is the unnecessary duplication of data within a database. Consider a simple database for a college, stored as a flat file.


In this example, every time a student enrols on a course, we repeat the Course ID, Course Name and we have to specify who is teaching it. If Dr. Turing teaches 30 students, his name and office number are needlessly duplicated 30 times. This isn't just inefficient in terms of storage; it's a "ticking time bomb" that threatens the data integrity of the entire system. Data integrity refers to the overall accuracy, completeness, and consistency of data. If we can't trust our data, any information or knowledge we derive from it is worthless. This leads to three specific types of error, known as data anomalies.

Update Anomalies

What happens if Dr. Turing moves rooms? With the flat-file structure, a database administrator would have to find and manually change every single record for every student Dr. Turing teaches. If they miss even one, the database is now in an inconsistent state. Some records say his room is '204', while others say '205'. Which is correct? The data can no longer be trusted. This failure to update all instances of a piece of data is called an update anomaly.

Insertion Anomalies

Imagine the college hires a new tutor, Dr. Lovelace. How do we add her to the database? We can't. In our current structure, a tutor's details are tied to a student's enrolment. Until a student is assigned to Dr. Lovelace, there is no record we can create to store her details. We cannot add a new tutor without also adding a student. This inability to add data because another piece of data is missing is an insertion anomaly.

Deletion Anomalies

Now, consider a student who is enrolled on a niche course and is the only student taught by a particular tutor. If that student decides to drop the course and we delete their enrolment record, what happens? We might accidentally delete the only copy of that tutor's details from the entire database. The unintentional loss of related data when a record is removed is known as a deletion anomaly.

The Relational Solution and the DBMS

To solve these problems, we use a relational database. The core principle is to "divide and conquer". Instead of one massive table, we decompose the data into multiple, smaller tables, where each table represents a single entity (like 'Students', 'Courses', 'Tutors' and 'Rooms').


Now, a room is stored only once. If Room 204 changes name, we only need to make a single change in the

rooms

table. In a later lesson, we'll discover how the links between the tables ensures this update is instantly reflected for all 30 of his students. This structure eliminates all three anomalies.

This is managed by a Database Management System (DBMS) - specialised software like MySQL, Microsoft SQL Server, or Oracle. A DBMS is responsible for:


By embracing the relational model, we move from a fragile, error-prone filing system to a robust, reliable, and scalable data architecture fit for modern applications.