Data modeling and constraints
Express business facts with types, keys, and constraints so invalid data cannot land
A good PostgreSQL model does not postpone every rule to application code. It teaches the database which states are valid.
A working order model
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
display_name text NOT NULL CHECK (length(trim(display_name)) > 0),
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT customers_email_unique UNIQUE (email)
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
total_cents bigint NOT NULL CHECK (total_cents >= 0),
placed_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON COLUMN orders.total_cents IS
'Order total in the smallest currency unit; never a floating-point amount.';Type choices
| Need | Prefer | Avoid |
|---|---|---|
| Primary key | bigint GENERATED ... AS IDENTITY or uuid | New designs depending on implicit serial behavior |
| Money | Smallest unit in bigint, or explicit numeric(p,s) | real / double precision |
| Instant | timestamptz | Storing a real-world instant as text |
| Text | text plus business constraints | Arbitrary varchar(255) without meaning |
| State | CHECK for small stable sets; reference table for a lifecycle | Unconstrained free text |
| Document attributes | jsonb | Hiding core relations and foreign keys in JSON |
timestamptz stores an absolute instant and renders it in the session time zone. It does not retain the input zone name. Store a zone identifier separately when rules such as Europe/Paris matter.
What constraints mean
NOT NULL: a value must exist.CHECK: each row must satisfy a predicate.UNIQUE: a candidate key is unique; multipleNULLs are allowed by default.PRIMARY KEY: unique, non-null row identity.FOREIGN KEY: the target must exist; deletion behavior is a design decision.
Delete behavior is not syntax preference
ON DELETE CASCADE says children should disappear with the parent. Use it only for truly dependent lifecycles. Invoices and audit records normally should not cascade.
Schemas and names
Use an explicit schema for application objects and reduce default privilege:
CREATE SCHEMA app;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER ROLE app_runtime SET search_path = app, pg_catalog;Names that help both humans and models are complete, stable, and low on abbreviations: customer_id beats cid; created_at beats ctime. Use COMMENT ON for units, state transitions, and sensitivity—not to repeat the column name.
Verify the model
INSERT INTO customers (email, display_name)
VALUES ('ada@example.com', 'Ada')
RETURNING id, created_at;
-- Expected to fail: totals cannot be negative
INSERT INTO orders (customer_id, total_cents)
VALUES (1, -100);A model is not verified merely because valid rows work. Representative invalid rows must fail for the intended reason.
Last updated on