s5.07.01 - flat files vs relational databases

From Flat Files to Relational Architecture

The Flat-File Paradigm

At its most fundamental level, a database is an organised collection of data structured for programmatic retrieval and updating. The simplest storage architecture is a flat-file database. In a flat-file model, all records and fields are held within a single, continuous two-dimensional structure - such as a comma-separated values (CSV) text file or an isolated spreadsheet table. Each line or row represents a distinct record, and individual columns represent specific data fields.

While a flat file is sufficient for simple, personal datasets (such as a private contact list), it fails catastrophically when applied to multi-user or enterprise systems. As transactional volume and entity complexity increase, storing all attributes across a single file introduces severe architectural flaws, most notably data redundancy.

Data Redundancy and the Threat to Data Integrity

Data redundancy is the unnecessary, repeated storage of the same data item across multiple records in a system. Consider a college registration database maintained as a single flat file:


In this redundant structure, every time a student enrols on a course, the corresponding

Tutor Name

and

Room Name

are duplicated. If a lecturer instructs forty students, their name and office location are stored forty separate times.

Redundancy wastes secondary storage space, but its most dangerous consequence is the degradation of data integrity. Data integrity guarantees that data stored within an information system remains accurate, complete, and consistent throughout its entire lifecycle. In a redundant flat file, data updates inevitably produce three critical failure modes known as data anomalies.


An update anomaly occurs when an item of duplicated data is modified in some records but left unaltered in others. If a lecturer moves to a different room, every single student enrolment record featuring that tutor must be identified and altered. If an administrative script or user updates thirty-nine rows but misses the fortieth, the database enters an inconsistent state. Conflicting records report different office locations for the same individual, destroying trust in the system. There are now two truths.


An insertion anomaly occurs when a user cannot record legitimate data about a particular entity because it is dependent on attributes from another entity that do not yet exist. If the college recruits a new tutor who has not yet been assigned any enrolled students, the system cannot store their details; the flat-file record requires a

Student ID

and

Student Name

to form a valid row entry. The new tutor's metadata cannot exist independently.


A deletion anomaly occurs when the removal of one piece of data causes the unintended, catastrophic loss of entirely separate information. If the sole student enrolled on an elective subject withdraws from college, purging that student's record deletes the only existing instance of the

Course ID

,

Course Name

, and assigned tutor details from the database.

The Relational Solution and the DBMS

To eradicate data redundancy and modification anomalies, data architects utilise the relational model. Rather than aggregating all attributes into a single table, the relational approach decomposes data into distinct, specialised tables, where each table represents a single entity (such as

Student

,

Course

, or

Tutor

).

This approach is championed by Dr. E.F. Codd's foundational work in the 1970s.


Each fact is recorded exactly once in exactly one place. If a tutor changes name or title, only one row in the

Tutor

table requires alteration. The updated value is immediately and consistently reflected across all related queries throughout the system.

The Database Management System (DBMS)

Relational databases are orchestrated by a Database Management System (DBMS) - enterprise software such as PostgreSQL, MySQL, Oracle Database, or Microsoft SQL Server. The DBMS provides critical architectural capabilities: