Schema design

Tables, columns, types and constraints are the shape of your data. Good schemas normalise what should not repeat, use foreign keys and NOT NULL to enforce rules, and choose types that cannot hold invalid values.

What a session covers

The tutor works through these with you, in conversation and code. A session ends when you have shown them, not when a video does.

Explain what normalisation removes and what constraints enforce
Design tables for a small domain with foreign keys and NOT NULL
Spot money stored as a float or dates as text

In practice

A few lines that show Schema design doing its job.

SQL

CREATE TABLE customers (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL REFERENCES customers(id),
  status      text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

The common mistake

Watch for

Storing money as a float or dates as text. Floats cannot represent 0.10 exactly and text dates do not sort or compare; use integer cents and timestamptz.

What to know first

Your roadmap puts these before Schema design, unless you already know them.

SQL queries

How it stays learned

Once a session shows you understand Schema design, it comes back for review. Each review takes a minute and tells the memory model how well you still know it.

Day 3First recall, while it is fresh
Day 7Second, a week on
Day 18Last, when it has settled

Where it leads

Topics that build on Schema design.

Migrations

Previous in the module← SQL queries

Next in the moduleIndexes →