When a spreadsheet stops working

The sheet that worked for two years

Rukmini runs a tuition centre in Hyderabad. She keeps one spreadsheet: one row per payment, with the student’s name, phone number, class, month, amount and payment mode. For two years and about 3,000 rows it is fine. Then four things happen in the same month.

A student’s family changes their phone number. That number appears in 34 rows. Rukmini fixes 31 of them.

Her assistant types “Class 9” in one row where everyone else has typed “Grade 9”. The class-wise total is now wrong by eleven students, and nothing looks broken.

The file takes forty seconds to open. Two people edit it at the same time and one of them loses a morning’s work.

A payment gets entered for a student who left last year. Nothing stops it. Nothing even notices.

Only one of these is a size problem. The other three are the same problem wearing different clothes: the same fact is stored in many places, and nothing forces the copies to agree.

One kind of thing per table

A database splits that sheet into tables. A table holds one kind of thing, one row per thing, with named columns that have declared types.

CREATE TABLE students (
  id          bigserial     PRIMARY KEY,
  full_name   text          NOT NULL,
  phone       text,
  class_level int           NOT NULL,
  joined_on   date          NOT NULL
);

CREATE TABLE payments (
  id          bigserial     PRIMARY KEY,
  student_id  bigint        NOT NULL REFERENCES students(id),
  paid_on     date          NOT NULL,
  amount      numeric(10,2) NOT NULL,
  method      text          NOT NULL
);

Read what each line actually buys.

PRIMARY KEY means every student has one permanent id. Names change, spellings vary, two families are both called Reddy. The id is the thing the rest of the system points at.

The phone number now lives in exactly one row. Change it once and every payment ever made is instantly attached to the new number, because payments never stored a phone number at all.

NOT NULL means the database refuses a student with no name. Not a warning. A refusal.

REFERENCES students(id) means a payment for student 9,999 who does not exist is rejected. This is the rule the spreadsheet could never enforce, and it is the reason databases stay trustworthy while spreadsheets rot.

numeric(10,2) rather than a floating-point type, because money in binary floating point drifts. Rupees, naira and pesos all deserve exact arithmetic.

int on class_level means “Grade 9” typed into that column is an error, immediately, at the moment someone types it. Not a silent wrong total three months later.

What you get that a sheet cannot give you

Types and constraints. Bad data is rejected at the door instead of being cleaned up later. Later never comes.

Transactions. A refund that moves money out of one row and into another either fully happens or fully does not. There is no state where half of it landed.

Concurrency. Forty people can write at once. Nobody overwrites anybody.

Scale. Ten million rows, answered in milliseconds, with an index. You will see how in a later lesson.

A query language. This is the big one. A spreadsheet answers the questions you designed it to answer. A database answers questions you had not thought of when you built it. “Which students who joined before June have paid every month since?” is one query against the tables above and impossible against the sheet.

The honest part

A spreadsheet is not a failure mode. Under a few thousand rows, with one person writing and a throwaway question to answer, a spreadsheet is faster than a database and you should use it. Anyone who tells you otherwise is selling something.

The moment to move is specific: when more than one person writes, or when the same fact appears in two rows, or when you need the data to still be trustworthy in a year. Rukmini crossed all three lines in one month and did not notice any of them, because crossing them does not produce an error message. It produces a number that is wrong.