Lesson 18.818.8 Tables, rows and relationships

Part B has one concept, because the sign-in server keeps its records in a database, and the rest of the chapter reads those records. You will never write a database question in this book. Coding agents write them. You learn to read one.

In everyday life. A school office keeps three lists. One lists every student, one per line, each with a student number. One lists every club, each with a club number. A third, the sign-up sheet, has one line per student per club: "student 1042, chess club, captain". A student in two clubs has two lines on the sign-up sheet, and the student list still has them once. To answer "which clubs is Priya in?", you find her number on the student list, then every line with that number on the sign-up sheet, then each club's name on the club list.

What you will learn. How to read a small database: its tables, its keys, and the three kinds of question an agent writes for it.

Why it matters. These tables decide who belongs to which company, and you read each question before it runs.

One question, start to finish. Priya Shah is an outside bookkeeper who works for both Brightline and Cedar Ridge. (She, like everyone in the lab, is invented.) The builder asks: "Which companies does Priya belong to, and with what role?" The sign-in server keeps its records in a database: an organized store of records that many programs, and many copies of one program, can read and change safely. Its lists work like the school office's. Each list is a table: one line, called a row, per thing, and one column per fact about it, like a spreadsheet tab with strict rules.

user, one row per person

organization, one row per company

member, one row per membership, like the sign-up sheet

Read it by hand: find Priya's id in user (u_priya), find her rows in member (m_3 and m_4), and look up each organizationId in organization. Priya has the role member in both Brightline and Cedar Ridge Supply. (People's real ids are long random strings. Brightline's scripts give each company a short fixed id like these, so the same company has the same id on your computer, in staging and in production.)

One word, two uses: member is the name of this table, and also the name of one role (18.7). The table holds every membership, whatever its role.

Notice what is not in user: passwords. Their hashes sit in a separate table, account, so that a program listing people never touches them.

What makes a database relational?

A database that keeps records in tables that point at each other is a relational database. What makes the pointing work is two kinds of column and one kind of table:

• A primary key is a column whose value no other row in its table shares, here id. It is how anything else points at this row, like a student number.
• A foreign key is a column that holds another table's primary key. member.userId points at a row in user. member.organizationId points at a row in organization. The database refuses a membership that points at a person or company that does not exist.
• A join table links two tables when each side can have many of the other, which is called a many-to-many link. One person can belong to many companies, and one company has many people, so neither user nor organization can hold the link alone. member is the join table, and it also holds what belongs to the link itself: the role. That is how one person belongs to two companies with a different role in each.

How do you read a query?

The language for asking a database questions is SQL (Structured Query Language, said "sequel" or "S-Q-L"), and one question is a query. You need to read three shapes. Each has its meaning in plain words underneath. Lines that start with -- are notes for people, and the database skips them.

Some queries only read rows, starting with SELECT. Others change them, with INSERT, UPDATE or DELETE. When the lab says read-only, it means SELECT only.

-- 1. Choose rows that match a condition: one company by its id
SELECT name FROM organization WHERE id = 'org_cedar';

From organization, give me the name of the row whose id is org_cedar. The answer: Cedar Ridge Supply.

-- 2. Join two tables: which companies Priya belongs to, with names
SELECT user.name AS person, organization.name AS company, member.role
FROM member
JOIN user         ON user.id = member.userId
JOIN organization ON organization.id = member.organizationId
WHERE user.name = 'Priya Shah';

Line by line: SELECT names the three columns you want back. Each is written as table, dot, column, so user.name means the name column of the user table. Two of them are called name, so AS gives each its own label in the answer, person and company. Without it, some tools keep only one of the two. FROM member starts from the membership table. Each JOIN ... ON ... attaches a second table (the word JOIN here is an action in a query, not the join table from above) by matching a foreign key to a primary key: first the person, then the company. WHERE keeps only Priya's rows. The answer has two rows, one per company.

-- 3. Count: how many members each company has
SELECT organization.name, COUNT(*) AS members
FROM member
JOIN organization ON organization.id = member.organizationId
GROUP BY organization.name;

Put the memberships in piles by company, count each pile (COUNT(*) means "count every row"), and call the count members. The answer: Brightline 2, Cedar Ridge Supply 2.

Why a database and not files?

Chapter 17's connector read data files packed with its code and never changed them. The sign-in server must write: a new person, a new membership, a refresh token that has just been used. Many copies of it run at once, and two may write at the same moment. A database is built for exactly that: many copies reading and writing the same rows safely. Its tables are created and changed by numbered steps kept in the repository, called migrations, which field guide F2 explains.

At work. In Lab 18, Task 2, the builder asks Claude Code in plain words: "Show which companies Priya belongs to, with names." Claude Code writes query 2, shows it, runs it read-only on a copy on the builder's computer, and explains each line. The builder checks the result against the three tables by hand.

The operating rule: read a table by its keys. A join table is how many-to-many links are kept, and the link's own facts, such as a role, live in it. Agents write every query. You read each one before it runs.

Figure 18.11. Three tables. Priya is one row in user and two rows in member, one per company.