Schema design: keys, types and normalization

conceptThe concept

Normalisation stores each fact once: customer details in customers, not repeated on every order. Third normal form (every non-key column depends on the key, the whole key and nothing but the key) avoids update anomalies. Denormalise deliberately, for example storing orders.total to avoid summing line items on every read, and keep it consistent with a trigger or in the same transaction.

Keys: GENERATED ALWAYS AS IDENTITY is the modern replacement for serial. Random UUIDs (v4) are good for distributed generation but scatter inserts across the whole index; uuidv7 (built in since 18) is time-ordered, so it inserts at the right edge of the index like a sequence. Natural keys (an ISO country code) are fine when they are truly stable.

Use the type system: domains (a type plus constraints, such as CREATE DOMAIN email AS text CHECK (...)), enums for small fixed sets (adding values is easy, removing them is not; a CHECK or lookup table is more flexible), and generated columns. Since 18 a generated column is virtual by default (computed on read); add STORED to compute on write and allow indexing.

whyWhy it matters

Schema mistakes are the most expensive to fix: they leak into every query and every service. A missing NOT NULL or a float for money will eventually produce bad data that someone has to clean up by hand.

Use caseReal-world scenario

The shop adds a shipments table. It references orders, stores a tracking number with a domain type, uses uuidv7 for ids shared with a partner API, and has a generated column for delivery time in days.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE DOMAIN tracking_no AS text CHECK (VALUE ~ '^[A-Z]{2}[0-9]{9}[A-Z]{2}$');
CREATE TABLE shipments (
  id            uuid PRIMARY KEY DEFAULT uuidv7(),
  order_id      integer NOT NULL REFERENCES orders (id),
  tracking      tracking_no NOT NULL,
  shipped_at    timestamptz NOT NULL,
  delivered_at  timestamptz,
  transit_days  numeric GENERATED ALWAYS AS (round(extract(epoch FROM delivered_at - shipped_at) / 86400, 1)),
  CHECK (delivered_at IS NULL OR delivered_at >= shipped_at)
);
INSERT INTO shipments (order_id, tracking, shipped_at, delivered_at)
VALUES (1, 'RX123456789DE', '2025-03-01 09:00+00', '2025-03-03 15:00+00'),
       (2, 'RX987654321DE', '2025-03-02 09:00+00', NULL)
RETURNING id, tracking, transit_days;
SELECT attname, attgenerated FROM pg_attribute WHERE attrelid = 'shipments'::regclass AND attname = 'transit_days';
ROLLBACK;

BEGIN;
CREATE DOMAIN tracking_no AS text CHECK (VALUE ~ '^[A-Z]{2}[0-9]{9}[A-Z]{2}$');
SELECT 'RX12345DE'::tracking_no;

Is orders.total consistent with its line items? Denormalised values need a check like this, or a trigger that keeps them in sync.

SELECT count(*) AS orders_checked,
       count(*) FILTER (WHERE o.total <> li.sum_lines) AS mismatched
FROM orders o
JOIN (SELECT order_id, sum(qty * unit_price) AS sum_lines FROM order_items GROUP BY order_id) li ON li.order_id = o.id;

SELECT pg_column_size(ROW(true, 1::bigint, true, 1::bigint)) AS bool_bigint_interleaved,
       pg_column_size(ROW(1::bigint, 1::bigint, true, true)) AS bigints_first;

internalsUnder the hood

Column order affects row size because of alignment padding: an 8-byte column after a 1-byte boolean wastes 7 bytes. On very wide, large tables, ordering columns from widest fixed-length to variable-length saves space. Each row also has a 23-byte header plus a null bitmap.

Avoid / preferDo this, not that

CREATE TABLE attributes (entity_id int, name text, value text);  -- entity-attribute-value

-- real columns for known attributes, a jsonb column for the variable remainder

CREATE TABLE invoices (id serial PRIMARY KEY);

CREATE TABLE invoices (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

PitfallsCommon mistakes

• Storing money in float or real. Use numeric (or integer cents).
• Using serial in new schemas. Identity columns are standard SQL and handle permissions and dependencies cleanly.
• Modelling everything as JSONB "for flexibility" and then needing foreign keys, constraints and good statistics on the fields inside.

LimitsWhen not to use it

Do not fully normalize read-heavy analytical tables; star schemas and some deliberate denormalization serve reporting better. Normalize the system of record.

SecuritySecurity notes

Sequential public ids invite enumeration (/invoices/1001, /1002). Expose random or uuidv7 identifiers and always check ownership. Keep personal data in its own table with tighter grants.

ChallengeTry it yourself

SELECT c.relname, a.attname
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
  AND a.attnum > 0 AND NOT a.attisdropped
  AND a.atttypid = 'text'::regtype AND NOT a.attnotnull
ORDER BY 1, 2;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Data definition
• Generated columns