# 19.2 Tables, rows and keys, from zero (/building-ai-workers/business-data-and-retrieval/tables-rows-and-keys-from-zero)

---
type: Document
title: "19.2 Tables, rows and keys, from zero"
description: "Databases from zero: tables, rows, columns, primary and foreign keys, and how to read the three shapes of SQL query the chapter uses, with Brightline's own data."
status: stable
order: 319.02
ksor:
  owner: team:panaversity
  audience: [ public ]
  approval:
    by: process:panaversity
    at: 2026-11-04T22:13:33Z
chapter: "19"
part: III
expert_status: co-build
concepts: [ "19.2" ]
objectives:
  - { id: CCDV-F.D8.S2, label: supporting }
last_verified: 2026-11-02
generated:
  at: 2026-11-04T22:13:33Z
  by: process:panaversity
trust_tier: unverified
build_id: sha256:e1fbcb7d9a8f8b9b7cb96875022325c02c9ba4be94cef13b301cbd39a9edad85
dirty: true
ksor_version: 0.0.60
---

*This concept assumes nothing. If all of this is new to you, start here. If not, skim it for Brightline's own lists and the three shapes of question the rest of the chapter uses.*

**In everyday life.** An accounts payable clerk keeps two lists. The vendor list has one line per supplier: a vendor number, the name, the payment terms. The invoice list has one line per bill: the invoice number, the vendor number that sent it, the amount. To answer "how much do we owe Midwest Packaging?", she finds Midwest's number on the vendor list, then every line with that number on the invoice list, and adds them up. She never writes Midwest's name on every invoice line. The number is enough, because it points at the one line that holds the name.

**What you will learn.** The few words every later concept uses, from zero, and how to read the three shapes of question this chapter asks.

**Why it matters.** Every later concept, and every question a coding agent writes in the lab, uses these words. Once you know them, you can check an answer against the lists yourself.

**One question, start to finish.** Ana asks the builder: "Which invoices do we have from vendor V001, and who is V001?" Kept by a computer, Brightline's two lists look like this, cut down to a few rows and columns:

**`vendors`**, one line per vendor (terms such as Net 30 mean "pay within 30 days")

| vendor_id | vendor_name | terms |
| --- | --- | --- |
| V001 | Midwest Packaging Co. | Net 30 |
| V002 | Buckeye Janitorial Supply | Net 15 |
| V005 | Hocking Valley Pallets | Net 30 |

**`invoices`**, one line per invoice

| invoice_number | vendor_id | amount_cents | status |
| --- | --- | --- | --- |
| INV-8301 | V001 | 482500 | open |
| INV-8302 | V002 | 61875 | open |
| INV-8309 | V001 | 312000 | paid |

Read it by hand, as the clerk would. Find V001's line in `vendors` (Midwest Packaging Co.), then every line in `invoices` whose `vendor_id` is V001: INV-8301, open, and INV-8309, paid. Amounts are whole cents, so 482500 is $4,825.00.

### What is a relational database?

A **database** is an organized store of records that many programs, and many copies of one program, can read and change at the same time, safely. A **relational database** keeps its records in **tables**, and lets one table point at another. Each table is like one spreadsheet tab with strict rules: one **row** per thing (one vendor, one invoice), and one **column** per fact about it (its name, its amount). Every column has a type: text, a whole number, a date.

What makes the pointing work is two kinds of column:

- A **primary key** is the column whose value no two rows in its table share, so it names exactly one row. In `vendors` it is `vendor_id`. In `invoices` it is `invoice_number`. It works like a student number.
- A **foreign key** is a column that holds another table's primary key. `invoices.vendor_id` holds a `vendor_id`, so each invoice points at one vendor. The database refuses an invoice whose `vendor_id` points at no vendor. One vendor can have many invoices, and each invoice has one vendor: this is called a **one-to-many** link.

Written as table, dot, column, `invoices.vendor_id` means "the `vendor_id` column of the `invoices` table".

### How do you read a query?

The language for asking a relational database questions is **SQL**, said "sequel" or "S-Q-L". One question is a **query**, and SQL stands for Structured Query Language. In this book a coding agent writes every query. You need to read three shapes. Each has its meaning in plain words underneath, and the real answer from the lab's database. Lines that start with `--` are notes for people, and the database skips them.

```sql
-- 1. Choose rows that match a condition: one vendor by its id
SELECT vendor_name, terms FROM vendors WHERE vendor_id = 'V005';
```

From `vendors`, give me the `vendor_name` and `terms` of the row whose `vendor_id` is V005. The quotes mark `'V005'` as a text value, and the `;` ends the statement. The answer is one row: Hocking Valley Pallets, Net 30.

```sql
-- 2. Join two tables: each of V001's invoices, with the vendor's name
SELECT invoices.invoice_number, vendors.vendor_name, invoices.amount_cents
  FROM invoices
  JOIN vendors ON vendors.vendor_id = invoices.vendor_id
 WHERE invoices.vendor_id = 'V001';
```

Line by line: `SELECT` names the columns you want back. `FROM invoices` starts from the invoices table. `JOIN vendors ON ...` attaches the vendors table by matching each invoice's foreign key to a vendor's primary key, so each invoice row gains its vendor's columns. `WHERE` keeps only V001's invoices. The answer has two rows: INV-8301, Midwest Packaging Co., 482500, and INV-8309, Midwest Packaging Co., 312000. The name appears twice in the answer, but it is stored once, in `vendors`.

```sql
-- 3. Count: how many open invoices each vendor has
SELECT vendor_id, COUNT(*) AS open_invoices
  FROM invoices
 WHERE status = 'open'
 GROUP BY vendor_id;
```

Keep the open invoices, put them in piles by vendor (`GROUP BY`), count each pile (`COUNT(*)` means "count every row"), and call the count `open_invoices`. On the lab's full data, which has all eight of Brightline's vendors and fifteen invoices, more than the few rows shown above, the answer has eight rows, one per vendor. V001 has 1, and V002 has 2.

Those three shapes read data. Five other words change it, and only the lab's migrations and data scripts use them: `INSERT` adds rows, `UPDATE` changes them, `DELETE` removes them, `CREATE TABLE` makes a new table with its columns and rules, and `DROP TABLE` deletes a whole table. When this chapter says "read-only", it means `SELECT` only.

### Why does this matter for an AI system?

An AI app never touches these tables. It calls the connector's tools ([Chapter 17](../connectors-and-mcp/overview.md)), and the connector's code runs queries like these three, written and tested in advance. The model never writes SQL ([19.10](where-the-guarantee-stops.md)).

**At work.** In [Lab 19](lab.md), Task 4b, after the companies' data is loaded, the builder asks Claude Code in plain words: "Show me which invoices we have from V001, with the vendor's name." Claude Code writes query 2, shows it, runs it read-only, and explains each line. The builder checks the answer against the two tables by hand, and predicts query 3's answer before it runs.

**The operating rule:** read a table by its keys. The primary key names a row, and a foreign key points at a row in another table. Store each fact in one table and reach it through its key. Agents write every query. You read each one before it runs.

![Two tables drawn as cards, vendors and invoices. In vendors, the vendor_id column is marked primary key. In invoices, the invoice_number column is marked primary key, and the vendor_id column is marked foreign key, with an arrow from each invoice's vendor_id to the matching vendor row. Midwest Packaging's row in vendors and its two invoices, INV-8301 and INV-8309, are highlighted. Below, a small result table from a join: each invoice beside its vendor's name.](img/chapter-19-keys-from-zero.png)

*Figure 19.5. A primary key names one row. A foreign key points at a row in another table. A join follows the pointer.*