# 18.8 Tables, rows and relationships (/building-ai-workers/securing-connectors/tables-rows-and-relationships)

---
type: Document
title: "18.8 Tables, rows and relationships"
description: "Relational databases from zero, at reading depth: tables, keys, a join table and three shapes of query."
status: stable
order: 318.08
ksor:
  owner: team:panaversity
  audience: [ public ]
  approval:
    by: process:panaversity
    at: 2026-11-02T18:20:57Z
chapter: "18"
part: III
expert_status: builder-only
concepts: [ "18.8" ]
last_verified: 2026-11-02
generated:
  at: 2026-11-02T18:20:57Z
  by: process:panaversity
trust_tier: unverified
build_id: sha256:e1fbcb7d9a8f8b9b7cb96875022325c02c9ba4be94cef13b301cbd39a9edad85
dirty: true
ksor_version: 0.0.60
---

*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

| id | name | email |
| --- | --- | --- |
| u_ana | Ana Morales | ana@brightline.example |
| u_luis | Luis Moreno | luis@cedarridge.example |
| u_priya | Priya Shah | priya@books.example |

**`organization`**, one row per company

| id | name |
| --- | --- |
| org_brightline | Brightline |
| org_cedar | Cedar Ridge Supply |

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

| id | userId | organizationId | role |
| --- | --- | --- | --- |
| m_1 | u_ana | org_brightline | owner |
| m_2 | u_luis | org_cedar | owner |
| m_3 | u_priya | org_brightline | member |
| m_4 | u_priya | org_cedar | member |

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](permission-per-tool.md)). 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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](field-guide-securing-connectors.md#f2-relational-databases-and-d1-from-zero) explains.

**At work.** In [Lab 18](lab.md), 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.

![Three tables drawn as cards: user, organization and member. Arrows run from member.userId to user.id and from member.organizationId to organization.id, labeled foreign key to primary key. Priya's row in user and her two rows in member, one for Brightline and one for Cedar Ridge Supply, are highlighted, each with its role.](img/chapter-18-tables.png)

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