About this article
Thank you for visiting the site. This article is part 3 of the “IT Fundamentals” primers in the Architecture Crash Course for the Generative-AI Era series, and it covers database basics.
In part 1 I introduced the database as “the restaurant’s store room”. This time I go one level into what is inside that store room. Nothing difficult, though — read it as a discussion of “how a shop keeps its membership list and its order book” and you will be fine.
The point of this article
- A database is "a ledger that survives a crowd reading and writing at once"
- Information is split into tables by role and joined by ID, not piled into one giant table
- The crux is the guarantee that a set of writes either all happen or none do (a transaction)
What a database actually is
Roughly, a database is “a purpose-built store where information is organised, kept, and retrieved quickly when needed”.
Everything a web service handles — member names and addresses, stock levels, order history — lives in a database. The reason you can close the browser and see the same information tomorrow is that the record is safely in a database on some server.
As mentioned in part 1, “if it is just tables of information, would a spreadsheet not do?” is a fair question. The insides of a database really are a collection of tables, so the idea is close. But the moment you add thousands or tens of thousands of people reading and writing simultaneously, a spreadsheet is out of its depth. A database is the specialist built for that condition. That is the starting point.
Information is split across tables
So what does the inside of the store room look like?
The most widely used databases in modern web services keep information as tables. The interesting part is that rather than cramming everything into one enormous table, you split it by role.
For an online shop, for example, you hold a “membership list” table and an “order book” table separately.
Why split them? Because writing the same information repeatedly makes corrections a nightmare. If every line of the order book carried “Sato, Tokyo…” including the address, then when Sato moves you would have to hunt down and rewrite every past order. Keep the list and the book apart, joined by a “member ID”, and the address is corrected in exactly one place.
That idea — “split the tables, join by ID” — is the root of the topic the main series argues about at length under “data modelling”.
SQL is the request slip you hand the storekeeper
To get information back out of those separate tables, you use a dedicated language called SQL.
Roughly, SQL is “the format for a request slip to the storekeeper”. “From the membership list, find everyone living in Tokyo”, “add this one order to the order book” — requests like these, written to a fixed format. The program (the kitchen) hands the slip to the database (the store room) and uses what comes back to finish the dish.
Names like PostgreSQL and MySQL appear in the main series; those are product names of databases you can make SQL requests to. Think of them as brands of storage company and you have enough.
”Both or neither”: transactions
The part of database machinery I find most interesting is the idea of a transaction.
Consider a bank transfer. Moving ten thousand yen from A to B is actually two writes: “reduce A’s balance by ten thousand” and “increase B’s balance by ten thousand”.
Now suppose the server fails immediately after A’s balance is reduced. B has no money and A is ten thousand short. That must never be allowed to happen.
A transaction turns a set of writes into a strict choice: either all of them succeed, or none of them ever happened. Fail halfway and the partial changes are rolled back automatically. No half-finished state is left behind.
When you meet ACID in the main series, read it as “this ledger is guaranteed never to end up half-written”. For any service handling money or stock, the presence of that guarantee is what decides which product you can use.
There are stores that are not tables
What I have described — databases that manage information as tables — is properly called a relational database (RDB). It is the modern default, but there are other shapes.
NoSQL, when the main series mentions it, means “the umbrella term for every storage approach that does not insist on tables”. Think of purpose-built stores, some specialised in handling colossal volumes at extreme speed. But to give away a conclusion the series repeats: most services should simply start with an RDB. Specialist stores are something you add once you genuinely need one.
Why searching millions of rows is instant: the index
Suppose you have a membership list of a million people and you want to find Sato. Reading from row one would take a very long time — and yet searches on real web services return instantly. That is not only because computers are fast.
The trick is that databases keep an index. Just like the alphabetical index in a fat dictionary, it is a separate guide saying “members with this name are roughly around here”. With an index, a few narrowing steps find the right row out of a million.
Conversely, searching on a field you forgot to index means turning every page from the beginning. Nobody notices while the data is small; then one day the data grows and it surfaces as “search has become impossibly slow”. When the main series says “add an index” or “it is doing a full scan”, read it as “is there an index, or are we checking every row?”.
Common misconceptions about databases
A few misunderstandings beginners tend to carry.
First, the feeling that “pressing delete removes the data”. Real services rarely destroy data outright; a common design is to mark it “deleted” and hide it (a soft delete), so mistakes can be undone and incidents investigated. On top of that, copies of the whole store (backups) are taken regularly. That is why data from a service you left does not vanish instantly.
Second, the assumption that “a service has one database”. In practice, alongside the day-to-day trading ledger (the RDB described here), you normally also run a separate store for sales analysis, and a shelf holding the most-used information close at hand (a cache). Doing your year-end accounts next to the till would get in the way of trading, so analysis happens in a back room. The “data platform” articles in the main series are about how to build that back room.
Third, the idea that “picking the most powerful database keeps you safe”. As you can probably guess by now, real performance is largely decided by how the tables are split and how the indexes are laid out. However grand the warehouse, if the shelving is a mess, finding things is slow.
Mapping to the terms used in the main series
| This article’s analogy | Term in the main series | Meaning |
|---|---|---|
| Ledger, book | Table | The table information is kept in |
| One line of the book | Record (row) | One item of data |
| A column (name, address) | Column | A field of the data |
| Member ID | Primary key, foreign key | Identifies a row and joins tables |
| Request slip to the storekeeper | SQL, query | A request sent to the database |
| A dictionary’s index | Index | Machinery that makes search fast |
| Both or neither | Transaction, ACID | The no-half-written guarantee |
| A copy of the book kept elsewhere | Backup | A duplicate against disaster |
How this connects to the main series
Databases are handled mainly in the “Data Architecture” category — which product, how to design the tables, how to separate the analytical store. All of it extends this article’s “how to keep the books”.
Related articles
Summary
This article covered database basics through the analogy of a membership list and an order book.
A database is a purpose-built ledger that survives a crowd reading and writing at once, and the basic shape is splitting information into tables by role and joining them by ID. Operations where failure is unacceptable, like money, are protected by transactions — “all of it, or none of it”. With those three points, the database parts of the main series get much easier.
The next primer looks inside the kitchen: “programs and APIs”.
Back to series TOC -> ‘Architecture Crash Course for the Generative-AI Era’: How to Read This Book
I hope you’ll read the next article as well.
Also popular with readers
📚 Series: Architecture Crash Course for the Generative-AI Era (6/95)