CSV is one of the most useful formats a Textsmith can have in the toolbox.

It is simple. It is readable. It can be inspected with cat, searched with grep, transformed with awk, sorted with sort, processed with Python or R, and moved between systems without much ceremony.

For a single collection of records, CSV is often exactly what we need.

Suppose we are keeping a list of students:

Last Name,First Name,Age,Sex
Moyo,Tendai,21,F
Ncube,John,23,M
Chirwa,Ruth,20,F
Dube,Peter,22,M

This is perfectly reasonable.

Each row represents a student. Each column describes something about that student. We can ask questions of the data, transform it, produce statistics from it, and publish the results.

But eventually we may ask a more interesting question:

What modules is each student taking, who teaches those modules, and which faculty does each module belong to?

Suddenly our little CSV begins to look rather lonely.

One file is not the same thing as a data model

Imagine that we add another CSV:

Module ID,Module Name,Faculty
CSC101,Introduction to Computing,Science
MAT101,Calculus I,Science
ENG101,Academic Writing,Humanities

And another:

Module ID,Lecturer,Lesson Period
CSC101,Dr Mlambo,Monday 10:00
MAT101,Prof Ncube,Tuesday 08:00
ENG101,Ms Dube,Wednesday 14:00

There is nothing wrong with these files.

In fact, each one is nicely structured.

The difficulty is that the information in them is related.

Module ID is not merely another piece of text. It is the thing that tells us that CSC101 in one file refers to the same module as CSC101 in another.

The CSV format can store that identifier.

What it cannot do is understand the relationship.

That distinction matters.

CSV stores records. Databases model relationships.

This is where the difference between a file format and a database becomes important.

A CSV file essentially says:

Here is a collection of records. Each record has these fields.

A relational database can say something more powerful:

Here are several collections of records, and here are the relationships between them.

For our university example, we might have:

Students
--------
Student ID
Last Name
First Name
Age
Sex

Modules
-------
Module ID
Module Name
Faculty

Lecturers
---------
Lecturer ID
Lecturer Name

Teaching
--------
Module ID
Lecturer ID
Lesson Period

And perhaps:

Enrolments
----------
Student ID
Module ID

Now we are no longer simply keeping files.

We are describing a small world.

A student can be enrolled in many modules.

A module can have a lecturer.

A module belongs to a faculty.

A lecturer can teach several modules.

The relationships themselves have become part of the data.

We could put everything into one CSV

Of course, someone might say:

Why not just put all of this into one enormous CSV?

We could.

It might look something like:

Student ID,Student Name,Module ID,Module Name,Faculty,Lecturer,Lesson Period
S001,Tendai Moyo,CSC101,Introduction to Computing,Science,Dr Mlambo,Monday 10:00
S001,Tendai Moyo,MAT101,Calculus I,Science,Prof Ncube,Tuesday 08:00
S002,John Ncube,CSC101,Introduction to Computing,Science,Dr Mlambo,Monday 10:00

And now we have created another problem.

The information about CSC101 has been repeated.

If Dr Mlambo changes the lesson period, how many rows must we change?

If the name of the module changes, how many records contain the old name?

If 300 students take CSC101, do we really want to store the module name, faculty, lecturer and lesson period 300 times?

This is where the famous database ideas of normalization, keys, and relationships begin to make practical sense.

They are not merely academic terminology.

They are ways of avoiding unnecessary duplication and inconsistency.

The power of the key

Look again at Module ID.

Module ID
CSC101
MAT101
ENG101

We can use Module ID as an identifier.

Instead of copying the complete description of a module everywhere, another record can simply say:

Student ID,Module ID
S001,CSC101
S001,MAT101
S002,CSC101

The database can then use those identifiers to connect the information.

This is the idea behind a foreign key.

And once we have keys connecting tables, we can ask questions that involve several collections of records.

For example:

Which modules is Tendai Moyo taking?

Or:

Which lecturers teach modules taken by Tendai Moyo?

Or:

How many students are enrolled in modules belonging to the Science faculty?

Or:

Which modules have more than 100 students?

Those questions require us to move through relationships.

In SQL, this is where JOIN becomes one of the most important operations we learn.

SELECT students.first_name,
       students.last_name,
       modules.module_name,
       modules.faculty
FROM students
JOIN enrolments
  ON students.student_id = enrolments.student_id
JOIN modules
  ON enrolments.module_id = modules.module_id;

The interesting thing here is that the database is not simply reading one table.

It is following relationships.

This is the point where a Textsmith should stop asking “Which file?”

and start asking:

What are the things I am keeping track of, and how are they related?

That is a different question.

Suppose we are keeping track of books.

A simple CSV might be enough:

Title,Author,Year
The Hobbit,J.R.R. Tolkien,1937
1984,George Orwell,1949

But what if we want to keep track of:

• books
• authors
• publishers
• editions
• readers
• loans
• libraries

Suddenly the world we are describing has relationships.

One author can write many books.

One book can have many editions.

One edition can be held by several libraries.

A reader can borrow many books.

A book can be borrowed many times.

We have crossed an important boundary.

The question is no longer simply:

How do I store these records?

It becomes:

How do I represent the relationships between these records?

CSV is still useful

None of this means that CSV is bad.

Quite the opposite.

CSV remains an excellent interchange format.

It is wonderfully transparent:

head students.csv

We can inspect it.

grep 'CSC101' enrolments.csv

We can search it.

We can process it with awk, Python, R, or other tools.

We can put it under Git.

We can send it to somebody without asking whether they own a particular database system.

The problem is not that CSV has suddenly become inadequate as a file format.

The problem is that we have started asking it to represent something larger than a collection of independent records.

There is another clue: repeated information

One of the easiest warning signs is duplication.

Suppose our CSV contains:

Student,Module,Faculty,Lecturer
Tendai Moyo,CSC101,Science,Dr Mlambo
John Ncube,CSC101,Science,Dr Mlambo
Ruth Chirwa,CSC101,Science,Dr Mlambo
Peter Dube,CSC101,Science,Dr Mlambo

We have repeated Science and Dr Mlambo.

That repetition isn’t necessarily wrong. Sometimes denormalized data is useful, particularly for reporting.

But if this is our master data, we should start wondering whether these facts really belong in every student-module record.

Perhaps they belong somewhere else.

And that is a clue that our data has structure which deserves to be represented explicitly.

Another clue: updating becomes dangerous

Imagine that CSC101 moves from the Science faculty to another faculty.

With a carefully designed database, we change the faculty associated with CSC101.

With a large collection of CSV records containing duplicated information, we may need to find every occurrence and update it.

This is where the simplicity of text files can become deceptive.

A file can be easy to edit while the data represented by the file is difficult to maintain.

The file is simple.

The relationships are not.

The database is another tool in the Textsmith’s forge

The Unix toolbox teaches us to choose tools according to the shape of the problem.

We don’t use grep because it is fashionable.

We use it because the problem is searching text.

We don’t use awk because it is old.

We use it when records and fields need processing.

And we shouldn’t use a database simply because databases are “professional.”

We use one when the problem has become relational.

A useful progression might therefore look like this:

Plain text
    |
    v
CSV
    |
    |  multiple related collections
    |  identifiers
    |  repeated information
    |  relationships
    v
Relational database

This isn’t a rigid ladder.

A CSV file can contain millions of records and still be perfectly appropriate.

A SQLite database can contain only a few thousand records and still be the better representation.

The important question is not:

How much data do I have?

It is:

How much structure does my data have?

SQLite is an especially interesting bridge

For the Textsmith, SQLite is worth paying particular attention to.

It gives us a relational database without requiring us to set up a database server.

The entire database can live in a file.

That feels remarkably close to the Unix and plaintext way of thinking:

students.db

is just another artifact that can live alongside our other project files.

We can query it with SQL.

We can script it.

We can export data from it.

We can import CSV files into it.

And because it is a relational database, we can express relationships between our collections of records rather than trying to simulate those relationships with filenames and repeated fields.

It is, in many ways, a natural next step for someone who has already learned to think in files and transformations.

The real lesson

The important lesson isn’t:

CSV is bad; databases are good.

That would miss the point.

CSV is excellent at what it does.

It represents tabular data simply and portably.

But there is a moment when our data stops being merely a table and starts becoming a network of related facts.

That is the moment to look beyond CSV.

As Textsmiths, we should learn to recognize that moment.

When we have students and modules, modules and faculties, modules and lecturers, students and enrolments, books and authors, customers and orders, or products and suppliers, we are no longer merely dealing with isolated rows.

We are describing relationships.

And relationships need a model.

A CSV can carry the pieces.

A relational database can describe how those pieces belong together.

That distinction is one of the most important steps in growing from someone who can manipulate data into someone who can reason about data.

The Textsmith’s question therefore changes:

“Which file contains this information?”

becomes

“What things am I keeping track of, and how are they related?”

Once we start asking the second question, we have begun to think like a database designer.