Yesterday you ordered an iced latte in the coffee shop’s app, then closed it. This morning you open it again, and an “Order the same again” button is waiting, with your name and exactly the same drink. Apps don’t have memories like people do. So who remembered?
The short answer is a database. But before we get to that word, we need to understand why the web page itself can’t remember anything at all.
Part 1Web pages are very forgetful
Every time you open a web page, the browser builds it again from the files it receives. Anything that happens while you use it, like the number of coffees in your cart, is kept in your device’s temporary memory. That’s a very fast workspace, but it is wiped the moment you close the tab or press refresh.
Think of numbers written on a whiteboard during a meeting. When the meeting ends, the cleaner wipes the board. If nobody copied them into a notebook, those numbers are gone forever. A web page is that whiteboard.
Stop and think
If a web page forgets everything when you refresh, where did yesterday’s order go, so that you can still see it today?
Try it yourself below. The left side is data that lives only in the web page. The right side is data sent to the server and saved there. Put some coffees in the cart, save some and not others, then press refresh.
Try it · temporary vs. permanent memory
SimulationIn the web page (a variable in memory)
Cart: 0 cups
Empty
On the server (database)
Saved: 0 cups
Nothing saved yet
Add two or three coffees, save some and not others, then press refresh.
A common belief
“Our coffee order history is stored in the app on our own phone.”
Part 2A database is the strictest notebook in the world
- DatabasePermanent, organized storage
- A program that runs on a server. Its job is to keep data permanently and in order, find what is asked for quickly, and refuse data that breaks the rules it was given. Widely used examples are PostgreSQL, MySQL and SQLite.
A database is not a box you can toss anything into. It’s more like a records room with many cabinets. Each cabinet holds only one kind of document, and every document has to be filled in on the same form.
Deep dive · for the extra curiousWhy not just keep the data in a plain file on the server?
Part 3Tables, rows and columns
Most databases that websites use store data in tables, which look a lot like a sheet in Excel. Our coffee shop might have a customers table for customers and an orders table for orders.
- Table · Row · ColumnWhere data is kept, piece by piece
- A table holds one kind of data, such as all the customers. A row is one piece of data, such as one customer. A column is a property every row must have, such as name or city.
id | name | city ---+------------------+----------- 1 | Somchai Jaidee | Chiang Mai 2 | Malee Srisuk | Bangkok 3 | Anan Kaewmanee | Khon Kaen
Tables are there to be asked questions. Try asking the shop’s customers table yourself. Right now the shop has five customers. Press the questions one at a time: the rows that answer light up and the others fade. Don’t worry about memorizing the syntax yet. Just notice how each kind of question picks out rows.
Ask the table
The coffee shop’s five customers
← Choose a question above
| id | name | city |
|---|---|---|
| 1 | Somchai Jaidee | Chiang Mai |
| 2 | Malee Srisuk | Bangkok |
| 3 | Anan Kaewmanee | Khon Kaen |
| 4 | Preeya Boonma | Chiang Mai |
| 5 | Somchai Rakdee | Phuket |
Rows that answer the question light up; the others fade
Goal: Try all 4 questions
Part 4Four things you can do with data
However complicated an app is, it only does four kinds of things with a database: create something new, read what’s there, change it, and delete it. Programmers shorten these four words to CRUD.
- CRUDThe four things you do with data
- Short for Create, Read, Update and Delete: the four basic actions almost every system performs on data.
- Create — signing up as a new member or ordering a coffee adds a new row to a table.
- Read — looking at your order history or searching the menu reads data without changing anything.
- Update — changing your delivery address edits a value in a row that already exists.
- Delete — deleting an account or canceling an item removes a row from a table.
Train your eye before moving on. Everything you do in the coffee shop’s app is, behind the scenes, one of these four actions. Some are easy; some have a trap.
Sort them
You tapped it. What does the database do?
01Sign up for the first time with your phone number
02Scroll through the whole drinks menu
03Change your delivery address to your new workplace
04Press the “Order the same again” button
05Check how many reward points you have
06Ask to delete your account from the shop’s app
Goal: Sort all 6 situations correctly
Programs on the server give the database orders in a language called , which reads like short English sentences. You don’t need to memorize the syntax now. Just see which CRUD action each command matches.
-- Create: add a new customer
INSERT INTO customers (name, city) VALUES ('Wilai Thongdee', 'Rayong');
-- Read: read the customers who live in Chiang Mai
SELECT * FROM customers WHERE city = 'Chiang Mai';
-- Update: customer number 2 has moved
UPDATE customers SET city = 'Nonthaburi' WHERE id = 2;
-- Delete: delete order number 104
DELETE FROM orders WHERE id = 104;Stop and think
Notice the word WHERE in the UPDATE command. What would happen if you forgot to write that part?
Part 5Two keys that keep data from getting mixed up
Our coffee shop might have two customers named “Somchai,” one in Chiang Mai and one in Phuket. If one day the Chiang Mai Somchai asks to change his phone number, which row do we edit?
Stop and think
How can you tell two customers with the same name apart, without ever picking the wrong one?
- Primary KeyA row’s one-of-a-kind ID
- A column whose value is different in every row, used to identify that row with no chance of confusion. It’s usually a number the database hands out automatically, counting upward.
Now, each order needs to know whose it is. One way would be to type the customer’s name into every order. But if one day the customer changes their last name, we’d have to hunt down and fix hundreds of rows, and if we missed any, the data would contradict itself. A better way is to store only the customer number in the order, and let that number point back to the customer.
- Foreign KeyA link to a row in another table
- A column in one table that stores the primary key of another table, to say which row this one is related to. For example, the customer_id column in the orders table stores the id of the customer who owns the order.
Follow the key yourself. Below is the orders table. Notice that there isn’t a single customer name in it, only the numbers in the customer_id column that point back to the customers table in Figure 1.2.
Ask the table
All of the shop’s orders
← Choose a question above
| id | customer_id | menu | price |
|---|---|---|---|
| 101 | 1 | Iced latte | 60 |
| 102 | 2 | Hot Americano | 50 |
| 103 | 1 | Iced mocha | 65 |
| 104 | 3 | Iced green tea | 55 |
| 105 | 5 | Hot latte | 55 |
| 106 | 1 | Iced Americano | 55 |
Rows that answer the question light up; the others fade
Goal: Try all 3 questions
LabOpen the drawers with your own hands
Below is a simulated database for the coffee shop, with two drawers: customers and orders. Every time you do something, you’ll see the tables really change, see the SQL a real program would send, and read an explanation of how the database is thinking. Try these missions in order.
- Create a new customer, and notice which id they get even though you didn’t fill one in.
- Create an order whose customer_id is
99, which doesn’t exist, and read what the database says. - Press the name Somchai Jaidee in the table, and see which orders point to him.
- Delete Somchai without ticking the CASCADE box, then try again with it ticked.
- Create another new customer, and see whether Somchai’s id gets reused.
- Search for “iced,” then try the “Let the browser edit the database directly” button.
LAB 03 — Data Memory Vault
The data drawers
SELECT *
FROM customers;What happened?
Drawer · TABLE
customers shop customers
| id (Primary Key) | name | city |
|---|---|---|
| 1 | Chiang Mai | |
| 2 | Bangkok | |
| 3 | Khon Kaen | |
| 4 | Phuket |
Try itPress a customer’s name in the top table to see which orders in the bottom table point back to them.
Drawer · TABLE
orders coffee orders
| id (Primary Key) | customer_id (foreign key pointing to customers.id) | item | total ฿ |
|---|---|---|---|
| 101 | Iced latte | 65 | |
| 102 | Hot Americano | 55 | |
| 103 | Blended cocoa | 70 | |
| 104 | Thai iced tea | 50 | |
| 105 | Cappuccino | 60 | |
| 106 | Espresso | 45 |
Who may write to the database?
Browser vs. server
Try changing the price of order #101 (Iced latte, 65 baht) by three routes, and see where each request is stopped.
Part 6What just happened in the drawers
If you finished every mission, you just watched a database do three things that most people never realize it does every second.
One: it hands out ID numbers by itself and never allows a duplicate. A new customer gets the number after the highest one ever handed out, not a number that happens to be free.
Two: it protects relationships. The order for customer number 99 was refused because nobody owns it, and deleting Somchai was stopped because three orders still pointed to him. The delete only succeeds once you say clearly that those orders should be deleted too.
Three: it never reuses old numbers. Even though Somchai has been deleted, number 1 will never be given to anyone else.
Stop and think
Why won’t the database reuse the number of someone who was deleted, even though that number is now free?
Part 7Why the database must hide behind the server
In the lab, you pressed a button and the table changed instantly. It might look as if the browser can edit the database by itself. In the real world, that must never happen. The browser never talks to the database directly. It sends a request to the server, and the server decides whether to do what it asks.
In the lab, the three buttons in the “Browser vs. server” box simulate these routes. Raw SQL from the browser is refused immediately. A customer asking the server to change a price is refused for not having permission. A staff member passes the checks, so the server passes the command on to the database, which still checks its own rules once more.
The lab in this chapter can run in your browser because it is a simulation for learning. The data lives only in your own memory, no one else can see it, and it disappears when you refresh, which is this chapter’s very first lesson.
SYSTEM SUMMARY
Chapter summary
- 01A web page keeps data only temporarily, and it’s gone on refresh. Data that must be remembered has to be sent to a database on the server.
- 02A database stores data in tables. A row is one piece of data, a column is a property every row must have, and every column has rules.
- 03There are four actions on data — Create, Read, Update, Delete (CRUD) — and you give them in the SQL language.
- 04A primary key is each row’s unique ID number. A foreign key is a column that points to the primary key of another table.
- 05The database refuses data that breaks the rules, points to something that doesn’t exist, or would leave orphans behind.
- 06The browser must never talk to the database directly. Every request has to go through a server that checks identity, permission and data first.
End of chapter
Check your understanding
Answered 0/4
Nothing is graded and nobody sees your answers but you. Every choice comes with an explanation — right ones and wrong ones.
Now you know that a website remembers you because a strict notebook is hidden behind the server. In the next chapter, we’ll see how several tables work together when one customer has many orders and one order has many menu items, and how to ask questions across tables at once.
Before you move on, try explaining it in your own words. The system doesn’t read or grade your answer, but putting it into your own sentences is the best way to find out whether you really understand.
Explain it in your own words
What you write stays on your screen. It is not sent or saved anywhere, and nothing is graded.
Learning path
0/5
- Ask the customers table and see which rows answer (not done yet)Figure 1.2: try all 4 questions
- Sort what the app does into CRUD (not done yet)Figure 1.3: sort all 6 situations correctly
- Follow the link key from an order to its owner (not done yet)Figure 1.4: try all 3 questions
- Answer every question in the end-of-chapter quiz (not done yet)Answer them all. Wrong answers are fine and don’t affect the checkmark
- Explain it in your own words (not done yet)Write at least 30 characters, then press 'Compare with a sample explanation'
This list records what you actually did in this browser — it is not a certificate that you understand. The best test is to explain it to someone else. You can clear the record in the Machine Lens, DATABASE tab.