Tables and columns

A table is defined by its columns. Each column has a name, a type and optional rules about the values it accepts. When you describe what you want to keep track of, the assistant proposes these for you. This page explains what you are looking at before you approve them.

Column types

The type decides what a column can hold, and how values are compared and sorted. For example, as numbers 9 comes before 10, but as text "10" comes before "9".

Three choices are worth getting right from the start:

• Use decimal for money. A double is approximate, so totals can drift by tiny amounts.
• Use date or datetime for dates, not text. Date columns sort correctly and work with date ranges such as "last month".
• Prefer real columns to json. Values inside a json column have no type checks and can't be indexed directly. Keep json for data whose shape you don't control.

Required columns and empty values

A column is optional unless it is marked required. An optional column can be empty and a required column refuses a row without a value. Mark a column required when a row makes no sense without it, such as an order's customer.

Empty is different from zero and from blank text: an empty discount means "unknown" or "not set", while 0 means "no discount". Queries shows how this affects your results.

Default values

A default is the value a column gets when a new row doesn't provide one. It can be a fixed value, such as open for a status, or one of these generated values:

• The current time, for datetime columns. This is the usual way to fill a created_at column.
• A new random identifier, for uuid and string columns.
• An empty JSON object, for json columns.

Unique columns

A unique column refuses a value that another row already has. Use it for values that must not repeat, such as an email address or an invoice number. text and json columns can't be unique.

• Comparisons ignore upper and lower case and accents, so Anna@example.com and anna@example.com count as the same value.
• Empty values don't count as duplicates, so several rows can leave an optional unique column empty.

To require a unique combination of several columns, such as one row per product in each order, use a unique index.

Primary keys

The primary key identifies each row. No two rows can have the same one, and it is never empty.

Unless you ask for something else, every new table gets an automatic id column. The database fills it in with the next number, so you never set it yourself. An id carries no meaning, and that is what makes it a good key: a customer can change their name or email address and still be the same row.

A table can use its own primary key instead, made of one or more required columns that aren't text or json. A primary key can't be changed or removed afterwards, so the automatic id is the right choice for almost every table.

Names and descriptions

Names are written in lowercase and start with a letter, followed by letters, digits or underscores, up to 63 characters. Words with a special meaning to the database are refused, such as order or group.

Tables and columns can also have descriptions. Agents read them to work out what the data means, so a good description leads to better answers. For example, total tells an agent little, while "Order total in euros, including VAT" removes the guesswork.

Changing a table later

A table can change after it has data in it. In the chat, you can ask to:

• Add, remove or rename a column.
• Change whether a column is required, its default or its description.
• Rename the table or change its description.
• Change a column's type, as long as no data can be lost.

Every schema change asks for your approval, and changes to one table approved as a single step apply together or not at all. Removing a column or a table permanently deletes its values.

These type changes are allowed:

Any other type change is refused because it could lose data. Instead, add a new column, copy the values over and remove the old column.