What a database is and why a site needs one

A database is an organized store watched over by a guard program, the DBMS. A relational database keeps its data in tables linked by keys.

Where a site’s data lives

A site made of plain HTML files keeps everything in the markup itself: to change a price, you open the file and edit it. While edits are rare and one person makes them, that is a perfectly good life (static sites live like this, as part 8 shows). But as soon as data changes often or arrives from visitors, it has to be written somewhere and read from somewhere: requests from a form, orders, appointments, comments.

The simplest place is a file: plain text, CSV or JSON (we covered JSON in part 5). Any program can open a file, read it, change it and write it back. On a small site with one editor that is enough. Let’s see where the trouble starts.

When a file is no longer enough

Below, two editors change the same number. Look at the file first, then at the database, and compare the last step.

A file on disk A database

Editor A hasn’t read yet

File 10 seats left

Editor B hasn’t read yet

Step 1 of 5. The file says: seats left for your service: 10.

The file just sits on a disk. It doesn’t know two people will edit it.

Back Next Start over

Text alternative: in the file, two editors read 10 and both write 9, so one booking is lost. In the database, the second editor waits until the first has confirmed the edit, then reads 9 and writes 8: both edits are kept. The numbers in the diagram are invented.

In this example the file lost an edit and didn’t even notice. But that is only the first of its problems. Here are all five that push people from files to databases.

1. Simultaneous edits. Shown above: the last write overwrites the one before.
2. Integrity. In a file of services, each one has a “category” from another file. Someone deletes a category, and the services that pointed to it stay behind. The file won’t notice.
3. Search. To find yesterday’s requests, a file has to be read from start to finish. With ten requests nobody notices, with a million it hurts.
4. Permissions. Anyone with access to a file can read and change it. You want something else: an editor changes services, an accountant reads requests, a visitor sees nothing.
5. Failures. If the server switches off in the middle of a write, the file is left half written.

One tool solves all five.

What a database is

A database is an organized store looked after by a special program. That program is called a DBMS, a database management system. People often say “database” for both at once: PostgreSQL, MySQL and SQLite are DBMSs, and what they hold are databases.

What a DBMS does that a file doesn’t:

• Stops two edits from overwriting each other. It has locks and transactions for this (lesson 4 covers them).
• Checks the rules. “This field can’t be empty”, “this number is already taken”, “you can only point to a category that exists”.
• Searches quickly, because it can build search aids called indexes (also lesson 4).
• Hands out permissions: who may read and change what.
• Survives failures. If the power goes out in the middle of a write, then after the restart the data is either entirely old or entirely new.

Notice that a DBMS doesn’t have to be big and far away. SQLite is a database that lives in a single file and runs right inside your program, with no separate server. It sits in your phone and in your browser. SQLite’s own documentation says it suits sites with modest traffic, while for many simultaneous writes a server such as PostgreSQL is better. So “file versus database” isn’t a question of location. It is a question of whether there is a guard.

Check yourself: how is SQLite in one file different from a JSON file?

Inside SQLite a guard program is at work: it checks the rules, locks simultaneous writes, searches by index and finishes a write even if the power goes out. A JSON file does none of this, it is just text.

Tables, rows and columns

The most common kind of database is called relational (from relation). All its data lives in tables. A table looks like a familiar spreadsheet, only stricter.

id	title	price	category_id
1	Portrait session	150	1
2	Reportage	900	1
3	Photos for your website	400	2

• A table holds records of one kind (all the services).
• A row is one record: one service with all its values.
• A column is one field of every record, and it has a type: a number, text, a date, yes/no. The database won’t let you write the word “negotiable” into a price. This is the main difference from a spreadsheet cell, which accepts anything.

A primary key is a column whose value names a row unambiguously and never repeats. Usually it is a number, id, which the database hands out itself. Why not the name? Someone may correct it, and then every link to the row breaks; besides, two services can have the same name. A number means nothing, so it never changes.

A foreign key is a column that holds the key of a row in another table. In the example category_id points to a row of the categories table. That is how tables get related (hence “relational”). And the database makes sure a link never leads nowhere.

There are three kinds of relationship, and you’ll meet all three in your project:

• One to many: one category has many services, and a service is in one category. The foreign key goes on the “many” side.
• Many to many: a person runs many services, and a service can have several hosts. For this you add a third table of pairs, “person, service”.
• One to one: rarer, for example a profile attached to an account.

How to find your project’s entities

An entity is a kind of “thing” the site has to remember, with many instances. Each entity becomes a table. You can find them in the brief in four steps.

1. List the nouns in your brief: services, people, proofs, requests, sections.
2. Keep those that will have many instances and a life of their own. A “request” qualifies: there are many, each with its own date. The “tone of the site” doesn’t: there is only one.
3. Give each entity a key and fields. Take the fields from the brief: for a service they are the name, the description and the price.
4. Draw the links: “a request refers to a service”.

Requests deserve a word of their own. A request from a form is almost always a separate entity, not a field of some other one. A service has many requests, each with its own date and contact details, and they are also a visitor’s personal data: a name and a phone number can identify a living person. The law sets requirements for such data (in Europe the GDPR, in the UK the UK GDPR, and in a growing number of US states their own privacy laws), so it is kept separately and under rules. Lesson 7 of this part covers that. For now it is enough to mark such fields on the schema.

When you don’t need a database

An honest conclusion of this lesson: your project may not need a database. If the site has five services that change twice a year, and requests go to email through an intermediary service, you can keep the services right in a file (part 8 shows how, with Markdown), and nobody stores the requests on your side. The site you build in this course is made exactly like that: it has no database, and that is a deliberate decision.

A database is worth adding when at least one of three things is true: visitors write data; several people work on the data at the same time; or there is so much data that you need to search and combine it (filters, sorting, reports).

Practice

You’re in the era of 2010. Sites on a CMS can already keep articles and comments in a database, while small business owners still track requests in a spreadsheet and copy them to the site by hand. To avoid the double work, start with your project’s data schema.

The page has a schema table. The first row (services) is filled in as a model. Complete the rest: people, proofs and requests. For each, give the key, the fields and the links, and mark whether the entity holds personal data.

What was wrong with a spreadsheet instead of a database

In the 2000s the spreadsheet looked like a universal solution: understandable, at hand, everything visible. But it has the same five troubles as a file, and one more: no types and no links. That is how a “customer database” came to hold three records for one person with three spellings of the surname, and a “date” column held a date in one row and the word “yesterday” in the next. Relational databases introduce rules precisely so that this can’t happen. Spreadsheets remain an excellent tool for calculations and small lists. They just aren’t for data that a site reads and writes by itself.