Relational database basics

A relational database keeps information in tables that can refer to each other. Your organization's database is one of them. You don't need to know any of this to use it, because the assistant on the Database page designs the tables and writes the queries for you. Knowing the basics does help you ask for the right thing and check what you are approving.

These pages use one running example: a small shop that tracks its customers and their orders.

Tables, rows and columns

A table holds one kind of thing. Here is a customers table:

id	name	email	city
1	Anna Novak	`anna@example.com`	Bratislava
2	Ben Clark	`ben@example.com`	Dublin
3	Chloe Martin	`chloe@example.com`

Each row is one record: one customer.
Each column is one fact about that record, and it holds the same kind of value in every row.
A cell with no value, like Chloe's city, is empty. It is not the same as zero or a blank piece of text.

A database usually has several tables, one for each kind of thing: customers, orders, products.

The schema

The schema is the description of the database: which tables exist, which columns they have, what type of value each column takes and which rules apply. The schema describes the shape of your data, and the rows are the data itself.

You can see your schema on the Database page. Changing it asks for your approval, because a change can affect everything that uses the table. For the details, see Tables and columns.

Keys

A key is a column used to identify rows:

A primary key identifies each row of a table. In the example above it is id, and no two customers can have the same one.
A foreign key is a column that holds the primary key of a row in another table. An order with customer_id set to 1 belongs to Anna.

Foreign keys are what make a database relational: each fact is stored once, and tables point to each other instead of repeating it. Relationships explains how to use them.

Queries

A query is a question put to the database, such as "which customers live in Dublin?" You ask in plain words and the assistant or agent runs the query. Queries covers the kinds of questions a database answers.

Databases and spreadsheets

A table looks like a spreadsheet, but it behaves differently:

	Spreadsheet	Relational database
Values	Any cell can hold anything	Each column has a type, and values that don't fit are refused
Rules	Mostly by convention	Enforced, such as required values and no duplicates
Related data	Often copied between sheets	Stored once and linked by keys
Calculations	Formulas stored in cells	Worked out by a query when you ask
Size	Slows down as it grows	Stays fast on large tables with the right indexes
Shared use	Easy to overwrite someone's work	Many people and agents can read and write at once

A spreadsheet is often better for a one-off calculation or a table you lay out by hand. A database is better when data keeps growing, has to stay consistent, or is used by several people and agents.

When to use the database

Use the database for records you want to store, count, filter and look up later, such as customers, orders, inventory, tickets or logs. Use files for documents you read as a whole, such as contracts, reports and images. The two work well together: a contracts table can list each contract's customer and end date, while the contract itself stays a file.

Don't store passwords, API keys or other secrets in the database. Every member and every agent with access can read its contents.

Search documentation