Designing a table

create table

Read it as a list of rules. The id identifies the row. A quote must know its customer and its door. A quantity of zero or minus three is not a quote. A price cannot be negative. Created fills itself in. The same customer cannot be quoted the same door twice.

Every one of those rules would otherwise live in application code — in every application that ever writes to this table, including the migration script somebody runs at midnight.

every word after the name is a promise

create table quotes (
  id          integer primary key,
  customer_id integer not null,
  door_id     integer not null,
  qty         integer not null check (qty > 0),
  price       real    not null check (price >= 0),
  created     text    not null default (date('now')),
  unique (customer_id, door_id)
);

Types, and how much your database cares

Kind	SQLite	PostgreSQL	Note
whole number	integer	integer, bigint	ids, counts
exact decimal	—	numeric(12,2)	money: never float
approximate	real	double precision	measurements
text	text	text, varchar(n)	length limits rarely help
true/false	integer 0/1	boolean	SQLite has no boolean
time	text (ISO)	timestamptz	store UTC, always
structured	text / json	jsonb	see the JSON lesson

Money is not a float

0.1 + 0.2 is not 0.3 in binary floating point, and an accountant will find the missing penny. Use numeric(12,2) where it exists, or store whole minor units — 129900 for £1,299.00 — in an integer. SQLite has only real, which is one reason a real service does not keep its ledger in SQLite.

not null is the most valuable word here

A nullable column is a question every reader of the data has to answer: can this be missing, and what does missing mean? Make the default not null, and allow NULL only where missing is a genuine state with a meaning — as the NULL lesson put it.

default and not null together are how a column gets added to a live table without breaking the rows that are already in it.

check: the rule that has nowhere else to live

A check that names one column can sit on that column; a rule that spans two goes at the end of the table. MySQL ignored check entirely until 8.0.16, which is why so much older code re-implements them in PHP.

cheap, permanent, and impossible to forget

qty     integer not null check (qty > 0),
price   real    not null check (price >= 0),
status  text    not null check (status in ('quoted','paid','made','fitted','cancelled')),
email   text    check (email like '%@%'),
check (ends_at > starts_at)                    -- a rule about two columns

Identifiers and defaults

the id, per dialect

-- SQLite: an integer primary key IS the rowid, and fills itself in
id integer primary key

-- PostgreSQL
id integer generated always as identity primary key
id uuid primary key default gen_random_uuid()

-- MySQL
id int auto_increment primary key

Sequential or random?

A sequential id is small, sorts by age and makes a tidy index. A UUID hides how many customers you have and can be generated by the client before the row exists. Pick deliberately; do not mix.

Changing a table later

Adding a nullable column, or one with a constant default, is metadata-only in PostgreSQL 11+, MySQL 8 and SQLite — it does not rewrite the table. Adding a not null column with no default to a table that already has rows cannot work, and the database will say so.

alter table, and what it costs

alter table doors add column finish text;                     -- instant
alter table doors add column finish text not null default '';  -- instant in modern engines
alter table doors rename column lead_days to build_days;       -- SQLite 3.25+
alter table doors drop column finish;                          -- SQLite 3.35+