Why Databases and the Relational Model

Table of Contents

1. The Problem: Data That Outgrows Files
2. Spreadsheets vs Database Tables
3. Tables, Rows, and Columns
4. The Relational Idea: Tables That Point at Each Other
5. SQL: Say What You Want, Not How to Get It
6. The Three Kinds of SQL Statements
7. What PostgreSQL Is: Client and Server
8. Other Databases You Will Meet
9. The Plan: shopdb
10. Try It Yourself

1. The Problem: Data That Outgrows Files

Imagine you start a small online shop. On day one you keep orders in a text file:

2026-01-05, Sokha Chan, Wireless Mouse, 1, 15.00
2026-01-05, Sokha Chan, USB-C Cable, 2, 5.50
2026-01-20, Sokha Chan, Mechanical Keyboard, 1, 79.00

This works for ten orders. By the time you have ten thousand, the questions start:

• How much did Sokha spend in total? You have to read every line.
• Sokha changes her name after getting married. You must find and edit every line she appears on, and if you miss one, your data now disagrees with itself.
• Two staff members add orders at the same moment. One overwrites the other's change, or the file is corrupted halfway through a write.
• Someone types Wirelss Mouse. Nothing stops them.
• The computer crashes while saving. Is the file in a good state? Nobody knows.

You can solve each problem by writing code: a parser, a search function, locking, validation, backups. Doing that for every project is slow, and you would be rebuilding the same machinery again and again.

A database is that machinery, already built, tested, and fast. It stores data in a structured way, lets many users read and write at the same time, checks the data against rules, survives crashes, and answers questions quickly even with millions of records.

In short, a database gives you five things that a plain file does not:

2. Spreadsheets vs Database Tables

Many people's first database is a spreadsheet, and spreadsheets are great. For a personal budget or a quick calculation, use one. But look at what happens when you track orders in a single sheet:

Sokha's name and email are repeated on every row. If her email changes, you must edit three rows (or three hundred). The sheet does not stop you from typing sokha@exmple.com on one of them. The price of the mouse is stored in every order, and nothing says that two rows for the same product must agree. And a sheet has no way to say "qty must be a positive whole number" that a determined person cannot paste over.

A database table looks a lot like one sheet, but it comes with three big differences:

1. Every column has a type and rules. A column declared as a whole number will refuse the text abc. A column can be required, unique, or limited to a range.
2. Tables are separate and connected. Instead of repeating Sokha's details on every order row, you store her once in a customers table and let each order point at her.
3. You query, you don't scroll. You do not filter and copy cells by hand. You write a question and the database finds the answer, even in millions of rows.

The second point is the heart of this whole series, so let's look at it closely.

3. Tables, Rows, and Columns

A relational database stores data in tables. A table has:

• Columns (also called fields or attributes): the named, typed pieces of information, such as name or email.
• Rows (also called records or tuples): one item per row, such as one customer.

Here is a small customers table:

The table has four columns and three rows. A few things to notice:

• Each row has an id, a number that identifies that customer and no one else. Names can repeat (two people can be called Dara Kim), but an id never does. This identifying column is called the primary key. We study keys in chapter 6.
• All values in one column have the same type. You will not find a number in name or a name in id.
• The order of rows is not part of the table. A database does not promise to give rows back in the order you inserted them unless you ask for a specific order.

Some words you will hear again and again:

The schema is worth remembering. It is the blueprint of your database. The data changes every minute, but the schema changes rarely and carefully.

4. The Relational Idea: Tables That Point at Each Other

The word "relational" does not mean a table is "related" to a friend. It comes from a branch of math (relation, which is basically a set of rows) used by Edgar Codd at IBM in 1970. The practical idea is much simpler:

Store each fact in one place, and connect tables using shared values.

Let's fix the spreadsheet from section 2. Split it into three tables. First, customers (each person once):

Second, products (each product once, with its price):

Third, the order lines, which hold only numbers that point to the other two tables:

Read the first line of the last table: customer 1 bought product 1, quantity 1. To see names, the database joins the tables: it follows customer_id to the row in customers with that id, and product_id to the matching row in products. A column that holds another table's key is called a foreign key.

Now see what we gained:

• Sokha's email lives in exactly one row. Change it once and every order sees the new value.
• A typo cannot create a ghost customer, because the database can refuse a customer_id that does not exist in customers.
• The mouse price is stored once. Change the price in one row.
• The tables stay small. Numbers are cheaper to store than repeated text.

This is the essence of the relational model: small, focused tables connected by keys. Joining is how you put them back together when you ask a question. We spend all of chapter 7 on joins.

You do not have to learn the math behind it. You only need to learn to think in tables: "What are the things in my problem? What facts belong to each? Which things point at which?"

5. SQL: Say What You Want, Not How to Get It

To talk to a relational database you use SQL (Structured Query Language, said "sequel" or "S-Q-L", both are common). SQL has been the standard for 50 years, and knowing it carries over between databases and jobs.

Here is a question in plain English: "Which products cost more than 20 dollars?" In SQL:

SELECT name, price
FROM products
WHERE price > 20;

Typical result, once the table has our data:

        name         | price
---------------------+-------
 Mechanical Keyboard | 79.00
 SQL Cookbook        | 32.00
 Clean Code          | 28.50
 Desk Lamp           | 22.00

Compare this with how you would do it in JavaScript:

const result = [];
for (const p of products) {
  if (p.price > 20) {
    result.push({ name: p.name, price: p.price });
  }
}

The JavaScript tells the computer how: loop, test, push. The SQL tells the database what you want: the names and prices, from products, where the price is more than 20. This style is called declarative. You describe the result and the database decides how to find it. It may walk the whole table, or jump straight to the answer using an index (chapter 11). You do not care, and that freedom is powerful. When the table has ten million rows, the database picks a smarter plan without you changing your query.

SQL reads almost like English, which makes it easy to start. Two small conventions to know now:

• Statements end with a semicolon (;).
• By convention, SQL keywords are written in uppercase (SELECT, FROM) and names in lowercase. SQL does not require it, but it makes queries easier to read, and we follow it throughout this series.

6. The Three Kinds of SQL Statements

SQL statements fall into a few families. Knowing the family tells you what a statement can do to your data.

Think of a filing cabinet. DDL builds the cabinet and the folders (the structure). DML puts papers into folders, edits them, and removes them (the content). DQL looks at what is in there without changing anything.

A fourth group, transaction control (BEGIN, COMMIT, ROLLBACK), groups several changes into an all-or-nothing unit. Another, access control (GRANT, REVOKE), decides who can do what. Both come in chapter 12.

Here is how the series follows this map:

• Chapter 3 is mostly DDL: define the tables.
• Chapter 4 is DML: fill them and change them.
• Chapters 5 to 9 are DQL: ask better and better questions.
• Chapters 10 and 11 return to design and speed.
• Chapter 12 is about doing all of it safely.

7. What PostgreSQL Is: Client and Server

SQL is a language. A database management system (DBMS) is the program that understands the language, stores the data on disk, and enforces the rules. PostgreSQL (say "post-gress-Q-L", often just "Postgres") is one such system. It is free, open source, more than 30 years old, and trusted by companies of every size. It is also known for following the SQL standard closely and for supporting advanced features such as JSON columns, full-text search, and strong data integrity.

The most important thing to understand is its shape. Postgres is a server. It is a program that runs in the background, listens on a network port (5432 by default), and waits for clients to connect:

 +-----------+      SQL text       +------------------+     +-------+
 |  Client   | ------------------> |  PostgreSQL      | --> | Files |
 | (psql,    |                     |  server          |     | on    |
 |  Node.js, | <------------------ |  (port 5432)     | <-- | disk  |
 |  GUI app) |      result rows    |                  |     +-------+
 +-----------+                     +------------------+

A client is anything that sends SQL and reads the answer:

• psql, the command-line client that ships with Postgres. We use it in this series.
• A graphical tool such as DBeaver or TablePlus.
• Your own application: a Node.js, Python, or Go program using a database library.

This separation explains many things you will see later. It is why you start the server first and connect second. It is why many clients can use one database at once. It is why a web application never opens the database files itself but sends SQL over a connection, and why that connection needs a username and password. And it is why SQL injection (chapter 12) is such a risk: your app builds SQL as text and sends it to the server.

Inside one Postgres server there can be many databases, and inside each database there are tables. For this series we create one database, shopdb, on a server that runs in a Docker container. That keeps your computer clean: if you want to start over, you delete the container.

8. Other Databases You Will Meet

Postgres is not the only relational database. What you learn here transfers, because they all speak SQL with small differences ("dialects").

You may also hear about NoSQL databases such as MongoDB, Redis, or DynamoDB. They store data in other shapes (documents, key-value pairs) and trade some features for flexibility or speed. They are useful tools, but for most applications with connected data like customers, orders, and payments, a relational database is the safest default. If you master SQL first, picking up other databases later is much easier.

Why Postgres here? It is free, runs everywhere, is strict about data correctness (which is a good habit to learn early), and is widely requested in job listings. When a chapter uses a Postgres-only feature, we will say so.

9. The Plan: shopdb

You learn databases by building one. In every chapter we will add to a single project: the database of a small online shop called shopdb.

The final design has five tables:

 customers          categories
 ---------          ----------
 id                 id
 name               name
 email
 city                  |
 created_at            | one category has many products
   |                   v
   |               products
   |               --------
   |               id
   |               name
   |               price
   |               stock
   |               category_id
   |                   ^
   | one customer      | one product appears in many order lines
   | has many orders   |
   v                   |
 orders            order_items
 ------            -----------
 id                order_id
 customer_id       product_id
 status            quantity
 ordered_at        unit_price

A short tour:

• customers holds the people who buy.
• categories groups products (Electronics, Books, Home).
• products are what you sell, each with a price and a stock count.
• orders are purchases, each belonging to one customer.
• order_items is the list of lines in each order: which product, how many, at what price.

Do not worry if the diagram is not clear yet. You will build it table by table, and by chapter 6 every arrow will make sense.

Here is the chapter-by-chapter plan:

From chapter 2 you will keep a folder named shopdb/ with a few .sql files in it: schema.sql (the tables), seed.sql (the sample data), and a queries/ folder with your own questions. These files are the real product of this series. Because everything lives in text files, you can delete the database and rebuild it in seconds with one command. That is how professional teams work too: the database structure is code in a repository, not clicks that nobody remembers.

You will also use a fixed set of sample data. Five customers (Sokha, Dara, Linda, Vuthy, Maly), eight products, and five orders. Every result table in this series is computed from that data, so if you follow along, your output will match what you read.

10. Try It Yourself

These exercises need only paper or a text editor. No installation yet.

1. Spot the repetition. Look at this table of tutorial videos:

   title	channel	channel_email
   Intro to SQL	Mengsreang Channel	hello@example.com
   Joins Explained	Mengsreang Channel	hello@example.com
   Docker Basics	Mengsreang Channel	hello@example.com

   What gets repeated? What goes wrong if the email changes? Sketch how you would split it into two tables and which column would connect them.

2. Draw your own tables. Choose something you know well, such as a library, a school, or a food delivery app. List three tables with four or five columns each. For each table, circle the column that identifies a row, and draw an arrow from any column that refers to another table.

3. Declarative vs imperative. Write the loop in JavaScript that finds all customers in Phnom Penh from a list of objects. Then write what you think the SQL looks like in the form SELECT ... FROM ... WHERE .... Which one is shorter? Which one tells the computer how?

4. Sort the statements. Label each statement as DDL, DML, or DQL: CREATE TABLE, SELECT, DELETE, INSERT, ALTER TABLE, UPDATE, DROP TABLE.

5. Client and server. In your own words, explain why a web application sends SQL to a Postgres server instead of opening the database files directly. Name two benefits that come from the server being a separate program.

6. When is a spreadsheet enough? List two situations where you would still pick a spreadsheet and two where you would pick a database. What signs tell you it is time to move?

Where to go next. You now know why databases exist, what a table and a relation are, what SQL is, and what we will build. In the next chapter, Setting Up PostgreSQL, you will run Postgres in Docker, connect with psql, create the shopdb database, and run your first queries.