Guides
Designing a database
Pick tables, field types, keys, links and relationships, with example schemas for common jobs.
One table per kind of thing
A table holds one kind of thing: people, companies, applications, books. If you catch yourself adding fields like contact_1_name, contact_2_name, the contacts are their own table.
Every table gets id, created_at and updated_at automatically. Don't add them.
Field types
| Type | Use for | Write it as |
|---|---|---|
| text | Short text: names, titles | "Acme" |
| long_text | Notes, descriptions | "Met at the conference..." |
| number | Amounts, counts, scores | 42, 9.5 |
| checkbox | Yes/no | true / false |
| date | A day | "2026-11-01" |
| datetime | A moment | "2026-11-01T14:30:00Z" |
| url, email | Checked links and addresses | "https://acme.com" |
| select | One of a fixed set (status, priority) | "Applied" |
| multi_select | Several of a fixed set (tags) | ["remote", "senior"] |
| list | Several free values owned by the row (a person's links) | ["https://a.dev", "https://b.dev"] |
| link | One row in another table | 3 (that row's id) |
| relationship | Many rows in another table, seen from both sides | [3, 7] |
Number, checkbox and link fields can't change type later; text-like types can switch among themselves. Pick carefully for those.
Several values per row
- multi_select when the values come from a short, fixed set of choices.
- list when the values are free (links, aliases, phone numbers) and have nothing else to say about them. At most 100 items.
- A separate table with a link field when each value has its own details (a link with a label), is shared between rows, or can grow large.
Link or relationship?
- A link points a row at one row elsewhere, and only this table has the field: a task's project, a book's author when you only care which author.
- A relationship connects two tables both ways: people and organizations, where a person lists their organizations and an organization lists its people. Adding it once adds a field on both tables.
- When the connection has its own details (a person's role at an organization, since when), make a table for it (
memberships) with two link fields instead.
Keys and uniqueness
Mark a field unique when no two rows should share it: email, url, an external id like a GitHub username or an order number. Unique fields are what insert_rows and import_csv use with match_on to update existing rows instead of adding duplicates. Empty values don't count as duplicates.
Example schemas
Job search
applications: company (link to companies), role (text), status (select: Interested, Applied, Interview, Offer, Rejected), applied_on (date), url (url, unique), notes (long_text)companies: name (text, unique), website (url), size (select)
Simple CRM
people: name, email (email, unique), title, links (list of url)organizations: name (unique), website, industry (select)people.organizations: a relationship, which addsorganizations.peopleinteractions: summary, person (link to people), happened_on (date), kind (select: Call, Email, Meeting)
Reading list
books: title, author (text), status (select: Want to read, Reading, Read), rating (number), finished_on (date), tags (multi_select)
Recurring tracker (prices, rankings, metrics)
items: name, url (unique)snapshots: item (link to items), checked_at (datetime), value (number)
Keep one row per thing in the main table, and put repeated measurements in their own table with a link back. That keeps history instead of overwriting it.